RUn-time error 1004

[BRISBANEBOB]

A newbie to VBA and hesitantly trying to code a =vlookup to find if a record already exists. If it does, display an error message. If it doesn't, append the data to the end of the table.

My code is falling over on the V = Application line with a Run-time error 1004 Application-defined or object-defined error.

I am happy that the both the Lookup Name exists and the Range Name exits. This is what I have so far:

Thanks for any help

[shg]

Are you looking for the literal string "UniqueAdmissionRef"?

[bakerman2]

Please Login or Register  to view this content.

This way you are looking for a string called UniqueAdmissionRef, not for the value it stands for. Is it a range, or how do you assign a value to it ?

[BRISBANEBOB]

I think I understand the principle now, but not sure of how to declare it. I now get a runtime 9 error on the r= line

[bakerman2]

How about ?

Please Login or Register  to view this content.

[BRISBANEBOB]

Thanks for the response.

I tried .Value, .String and .Text but "Subscript out of range"

[Trebor76]

I tried .Value, .String and .Text but "Subscript out of range"

Did you change the sheet name from Sheet1 to the sheet name where your lookup value resides?

[Doc.AElstein]

Hi BRISBANEBOB

"Subscript out of range" is often in such a case telling You that "Sheet1" is not to be found in where VBA is looking as a result of your instruction in this code line:
r = Worksheets("Sheet1").Range("V6").Value ' Using Worksheets collection Object of ____???_____ Workbook to refer to the "Sheet1" that is there ( Hopefully ... Lol.... )
'__

'In that code line you are letting VBA Guess what Workbook "Sheet1" is in. It may be guessing right so the following is not your problem.
'
'But Even if the following is not your problem , it almost certainly will be one day when you least want or expect it, so it is worth noting so as to cut down the Error possibilities....
'
'So: ALWAYS Qualify Fully (Explicitly ) where your "Sheet1" is thus:

Dim WB As Workbook
Set WB = ThisWorkbook ' Workbook where the code is____....
'-____...... or
Set WB = Workbooks("MyWorkbook.xlsm") ' Using Workbooks collection Object of all Open Workbooks ( ALWAYS include the .xlsx or .xlsm, etc or VBA may geusss that wrong as well .. Lol.. )

Let r = WB.Worksheets("Sheet1").Range("V6").Value ' Using the Worksheets Collection Object of the Workbook WB to refer to the "Sheet1" that it hopefully has... !!
'
'Basically all the above is just saying again this:
'

Did you change the sheet name from Sheet1 .. to the sheet name where your lookup value resides?

That may not be your current problem. But it most likely will be sometime in the future if you do not always Reference Explicitly your Range.
So one way or another hope that is a bit of help.

Alan

[BRISBANEBOB]

I both marvel at, and really appreciate, your patience! Tricky stuff, this VBA!

It now falls over at the line V = Application etc... with Run=time error '1004': Application - defined or object-defined error

The range exists (I hope this shows the jpeg of the image capture of the range in Name Manager):
Range.png

Does it make any difference in that I'm running it from the View Code in the tab? I can see it in the Sheet in the VBA editor so presumably not. I assume the 'view code' just takes you to the code from this sheet in the Editor

[bakerman2]

Try this for a change.

Please Login or Register  to view this content.

[Doc.AElstein]

Hi BRISBANEBOB

I both marvel at, and really appreciate, your patience! Tricky stuff, this VBA!...

....no worries! – it is often the best way to learn, and to wake the brain up on a Sunday.. Lol.
I will have another look in a bit after a coffee or three

In the meantime a couple of things, not too important..

Usually Code is put in BB Code Tags , not HTML Code tags, but HTML works just as well ( and even sometimes works better.. Lol ).

Images Are good for things like you showed, but some test data ( reduced and desensitised ) that we can copy to a spreadsheet is useful. I Prefer using the Screenshots that everyone can see, but a File upload is also an option. ( Show also if appropriate some hand filled in results showing what should happen based on your supplied input data )

Check out first bit of this post for some jem on that
http://www.excelforum.com/showthread...t=#post4419333






Catch you later

Alan

P.s. You have not quite done the referencing as i explained, ( but that appears not to be the problem this time ) .... you missed out the WB.
Dim WB As Workbook
Set WB = ThisWorkbook
Dim V As Variant
Let r = WB.Worksheets("Admission Data Entry").Range("V6").Value



Edit I see bakerman2 has come in, more the merrier, I will take a look anyway..

[BRISBANEBOB]

Thank you for your response.

Based on the code below I am now getting Run-time error '9': Subscript out of range when it hits the V = line, and I appreciate it probably shouldn't have the second lot of r = Worksheets after the If Not line but it falls over before then.

[BRISBANEBOB]

I have checked the sheet DatabaseAdmissions name on the tab and there are no blanks before or after the name.

[bakerman2]

Out of range usually means a sheet doesn't exist, or a sheetname is misspelled like I did.

Please Login or Register  to view this content.

[Doc.AElstein]

Hi BRISBANEBOB

( BTW: Did you catch my post #11 ? )
It is still not clear to me exactly what you are trying to do , so is difficult to help. – Remember you know exactly what you want, but for someone trying to help you need to spell out a bit more clearly what you are trying to do.
_...............

Anyways...1)

............... assume the 'view code' just takes you to the code from this sheet in the Editor

If you right click on the Tab and select view code you will get to the VB Editor and the Main Big Code Pane Window showing is that of the Worksheet Code Module. It is OK to use that Module rather than a Normal Code Module , but it makes it even more important to reference explicitly all things, as many things will default to “going” then to the Worksheet in question.

Anyways...2)

...........
Does it make any difference in that I'm running it from the View Code in the tab? I can see it in the Sheet in the VBA editor so presumably not...

Should not make any difference. I try to see both the spreadsheet and the VB Code Window ( VBA Development Window ). ( I do that by using an extended ( Old Telly ) Monitor option on my computer and have then spreadsheet on one monitor and the VB Development Window on my notebook main screen ). But on a single screen you can probably juggle things around so that you can see both. Then run the code from the code window in Debug ( F8 ) mode rather than hitting the play ( or F5 ) so that you can see how and where errors occur etc.
But i guess you know all that.

_.........................

Anyways..3)
Named ranges add an extra level of complications, especially when you have possibly some referencing problems..
I would start maybe like this.....

I made an Excel File with two Worksheets in it, and put this data in those two Worksheets


Using Excel 2007 32 bit

Row\Col	B	C	D
5
6		JackSh_t
7		Smith
8		George
9		Pf-Tang
10		P-Tang
11		OLE
12		WiggyWam
13		BiscuitBarrel
14		DuWollyMoley
15		Wog
16		Makro
17		Mod
18		Pod
19

DatabaseAdmissions

_..

Row\Col	T	U	V	W
5
6			BiscuitBarrel
7

Admission Data Entry

_..................................

Then this code tells you that Patient “BiscuitBarrel” is found...

Please Login or Register  to view this content.

But it is difficult to help more without having some data or a Workbook to look at, especially when playing with named ranges.
A Workbook is useful as there can be some tricky “scope” things etc making referencing go astray, and it is difficult to guess problems there “working blind” as it were..
Named Ranges are good things, but it makes careful Range referencing especially important.

Alan

[Doc.AElstein]

So I thought I would practice my Named Range stuff anyway...

The following code works on the same File I created for the Last post.

It creates your wanted Named Range "rgUniquePatientAdmissions", and then the VLookup has the form similar to what you were using.

It gives the same results as the last code

Please Login or Register  to view this content.

_...........................

Also an extended code version here:
http://www.excelforum.com/showthread...19#post4420519
Takes the thing one stage further using a Workbooks scoped Range also.

Note the extra stuff there is a more full version of what you might do by default by a quick “Shorthand” code such as this, which basically does the same:

Please Login or Register  to view this content.

But I will not confuse you here any further.. Lol


Alan

[BRISBANEBOB]

That did it, Alan!

Thanks very much - now I move onto the next part.

Much appreciated

[Doc.AElstein]

Hi BRISBANEBOB
yous welcome,
Thanks for the Feedback
Alan

[Doc.AElstein]

P.s.
Not too important:
It might be worth considering renaming the Thread Title to something Like
Named Ranges Referencing. Run-time error 1004. Subscript out of range
Etc.
That Just helps someone with the same problem on a Google type search

Some jem on doing that:

Basically to do that, you
__ Log-In
__ Go to your first post
__ Hit Edit Post Button ( That should be left from Reply Button, if not see ** )
__ Hit Go Advanced Button
_..........IF AT THIS POINT YOU SEE an EMPTY Window then STOP. – A Bug has lost your first post so IMPORTANT: ABORT. ( To Abort Close Tab or hit Back on browser etc.....
http://www.excelforum.com/suggestion...ml#post4377194
_.... ). See Notes below for getting over that Bug_.......
Assuming the Bug has NOT caused the original Post to vanish and you STILL SEE ALL Your original post, then you will see that you can
__ Edit the title in The Title box above.
__ Then Hit Save Changes.
**Note: After a certain period of Time you can no longer change the Title. However, you can send a Note to a moderator or Administrator to ask for the Title to be changed for you. To do that: Hit the Report Post Button, then you can send a message in the Window that comes up to ask for the Title to be changed for you ( Remember to give the new Title that you want ! )
Thanks
Alan




_......

Some notes on getting over Vanishing Post on Editing
_1) For Simple plain text, After Abort, just copy text to clipboard ( Highlight Text , Ctrl+C ) before trying again .
http://www.excelforum.com/showthread.php?t=1125967
_2) If you have Code, Tables or Formatted text, then After Abort you can get your original text with all formatting ( other than Quotes ) by hitting Reply With Quote and coping all from between the Code Tags.
[QUOTE=Doc.AElstein;4178864]Only copy all here in Red
...Do not copy start and stop BB Quote Code Tags (Here in Black)[/QUOTE]
http://www.excelforum.com/suggestion...ml#post4376444
_...