VLookUp problem 3

[stillwillyboy]

When I try to execute (step thru with F8) the following code, I get an error message “Run time error 1004, Application defined or Object defined error.” at the .Value line.

Sub VLookUpCopiedPieceRate()
Application.ScreenUpdating = False

Dim LastRow As Range
Set LastRow = Range("A65536").End(xlUp)

With Range("B1") 'B1 is for PieceRate on the active _sheet. It receives the rate from the Jobs sheet
.Value = "=VLOOKUP(A1, Jobs!$A$1:$F$" & LastRow & ", 2, FALSE)"
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Cells(2, 1).End(xlDown).Row, .Column)), Type:=xlFillDefault

End With

End Sub

“Jobs” is the worksheet with the info I want to pull. I have verified the sheet name. Col A of Jobs has the job numbers, sorted. Col B has the piece rate that I want to move to the active sheet.

Col A of the active sheet has the job numbers, sorted. Col B of the active sheet is blank.

Whenever I hover the mouse over the LastRow variable in break mode, the row count is 85340412.

This should be working.

TIA

Bill

 

[Gavona]

change .value to .formula


Gavin

 

[stillwillyboy]

Gavin,

Same error message.

Bill

 

[Gavona]

lastrow is numeric but you are concatenating it. Convert to a string maybe using the text function.


Gavin

 

[stillwillyboy]

New code below. Same problem. I have a few other VLookups that are working without issue.

Sub VLookUpCopiedPieceRate()
Application.ScreenUpdating = False
Dim LastRow As Range
Set LastRow = Range("A65536").End(xlUp)
Range("B1").Select
With Range("B1") 'B1 is for PieceRate on the TimeSheetEntry sheet. _
It receives the rate from the Jobs sheet
.Value = CStr("=VLOOKUP(A1, Jobs!$A$1:$G$" & LastRow & ", 2, FALSE)")
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Cells(2, 1).End(xlDown).Row, .Column)), Type:=xlFillDefault
End With
Application.ScreenUpdating = True
End Sub

 

[rmikesmith]

Bill,

Your variable LastRow should be a row number, not a Range. Modified procedure:

Sub VLookUpCopiedPieceRate()
Application.ScreenUpdating = False
[COLOR=red]Dim LastRow As Long[/color]
[COLOR=red][s]Set[/s] LastRow = Range("A65536").End(xlUp)[b].Row[/b][/color]
Range("B1").Select
With Range("B1")  'B1 is for PieceRate on the TimeSheetEntry sheet. _
    It receives the rate from the Jobs sheet
.Value = CStr("=VLOOKUP(A1, Jobs!$A$1:$G$" & LastRow & ", 2, FALSE)")
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Cells(2, 1).End(xlDown).Row, .Column)), Type:=xlFillDefault
End With
Application.ScreenUpdating = True
End Sub

Regards,
Mike

 

[stillwillyboy]

Mike,

Thanks for the help, but I don't think .Row is the issue, though I must admit I don't know what is.

When I use the line:
Set LastRow = Range("A65536").End(xlUp).Row I get a Type mismatch error.

If I remove the Set and have the line:
LastRow = Range("A65536").End(xlUp).Row I then get the Object Variable not set error.

I am using VLookup in the same module looking up different info from different sheets and the others are working w/o the .Row. I have checked my spelling of the sheet name with the desired info.

There's either a problem on the worksheet with the desired info or something is wrong with this line:
.Value = "=VLOOKUP(A1, JobsAll!$A$1:$F$" & LastRow & ", 4, FALSE)"

Thanks.

Bill

 

[rmikesmith]

Bill,

I copied/pasted your original code and this generated the Run time error 1004, Application defined or Object defined error error as you reported. After making the changes outlined in my reply, no errors were generated.

There's either a problem on the worksheet with the desired info or something is wrong with this line:
.Value = "=VLOOKUP(A1, JobsAll!$A$1:$F$" & LastRow & ", 4, FALSE)"

Why is the sheet name different?


Mike

 

[stillwillyboy]

Mike, I set up two new sheets with data to pull. VTestJobs and ClientInfo. In Col's A & B VTestJobs looks like:
180 .55
181 .55
182 .55
361072 .65
361726 .75

ClientInfo looks like:
1002 Bob
1004 Steve
1007 Joe
1010 Helen

I set up two new VLookUps. VTesting and VLookUpCopiedName. They are below. VTesting does NOT work, VLookUpCopiedName does work. I can't see any difference except for the sheet names for the data sheets.


Sub VTesting()
Application.ScreenUpdating = False
Dim LastRow As Range
Set LastRow = Range("A65536").End(xlUp)
With Range("B1")
.Value = "=VLOOKUP(A1, VTestJobs!$A$1:$B$" & LastRow & ", 2, FALSE)"
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Cells(2, 1).End(xlDown).Row, .Column)), Type:=xlFillDefault
End With
Application.ScreenUpdating = True
End Sub

Sub VLookUpCopiedName()
Application.ScreenUpdating = False
Dim LastRow As Range
Set LastRow = Range("A65536").End(xlUp)
With Range("B1")
.Value = "=VLOOKUP(A1, ClientInfo!$A$1:$F$" & LastRow & ", 2, FALSE)"
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Cells(2, 1).End(xlDown).Row, .Column)), Type:=xlFillDefault
End With
Application.ScreenUpdating = True
End Sub

Thanks for the help.

Bill

 

[GlennUK]

LastRow is a range ... I'd have thought it should be Long, and refer to Range("A65536").End(xlUp).Row instead of being used in a Set. The second macro will put the value of the last row cell into the formula, and not the row number.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

Gotta agree with Mike & Glenn - you need to use the row number - NOT the range / value etc.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[rmikesmith]

Bill,

Here is an explanation of the issue of how LastRow should be declared and used: take a look at the VLOOKUP string you are constructing. The second argument is a Range and you are creating this using a sheet name qualifier coupled to an A1-style range address where the second row value is provided by the LastRow variable. You want to end up with a formula something like:

=VLOOKUP(A1, ClientInfo!$A$1:$B$200, 2, FALSE)

But, you have declared LastRow as a Range and set it to a particular cell; i.e. the last cell containing data in column A. Every object has a default property and for a Range object it is Value. So when you reference LastRow in the VLOOKUP string, the Value in the cell LastRow points to is added to your A1 range address. If that value is numeric, the address is valid as far as Excel is concerned, so no error is generated, although the formula will almost certainly be wrong. If the value is non-numeric, the code bombs.

That also brings up another, related issue. Your routines are using unqualified range references in several places, which may or may not work, depending on which sheet is active when the procedure runs. It also makes it difficult to accurately duplicate your setup. Could you describe exactly what you want these routines to do and where the output are to be placed?


Regards,
Mike

 

[stillwillyboy]

Great job. It seems to be working, but I do not know the number of lines of data to pull from, it could change, which is why LastRow = Range("A65536").End(xlUp) is used. I am assuming that if I use, let's say, $B$65536 instead of $B$200, it will work. i'll be testing with real data when I get off here. Please note that the Range("A65536").End(xlUp) works with the other proc.

As to unqualified range references, well, what I am writing now is an emergency payroll program for work. Our usual (very old) system has crashed and I am the payroll person. All i am trying to do at this time is get payroll calc'd.

Here is the current code (with the 200 being used).

Sub VTesting()
Application.ScreenUpdating = False
Dim LastRow As Range
Set LastRow = Range("A65536").End(xlUp)
With Range("B1")
.Value = "=VLOOKUP(A1, VTestJobsInfo!$A$1:$B$200, 2, FALSE)"
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Cells(2, 1).End(xlDown).Row, .Column)), Type:=xlFillDefault
End With
Application.ScreenUpdating = True
End Sub


Everyone,
Thanks for all the help.

Bill

 

[xlbo]

<sigh> please read Mike's post again.

You need to use:

LastRow = Range("A65536").End(xlUp).ROW

rather than

LastRow = Range("A65536").End(xlUp)

The reason for this is that the default property of a range object is its value.

What Mike was trying to show you was that if you concatenate your 'LastRow' variable to the end of the range, you need it to be a number.

If the value of LastCell is 50123 (or any number), your formula will concatenate it ("=VLOOKUP(A1, VTestJobs!$A$1:$B$" & LastRow & ", 2, FALSE)") to the string to make the formula:

=VLOOKUP(A1, VTestJobs!$A$1:$B$50123, 2, FALSE)

which will work as there is a nice high row number. If, however, the LastCell's value is a rather more modest '52' then your formula will be:

=VLOOKUP(A1, VTestJobs!$A$1:$B$52, 2, FALSE)

which may end up giving you errors. This is almost certainly what is happening on your 'other' function which appears to work - it is working through lucky coincidence and nothing else !!!! If the value in the Last Cell happens to be a letter (a string data type), then the concatenated formula will read:

=VLOOKUP(A1, VTestJobs!$A$1:$B$SomeText, 2, FALSE)

which obviously won't work...

Your code therefore needs to be:

Sub VTesting()
Application.ScreenUpdating = False
Dim LastRow As Range
Set LastRow = Range("A65536").End(xlUp)[b][COLOR=red].ROW[/color][/b]
With Range("B1")
.Value = "=VLOOKUP(A1, VTestJobs!$A$1:$B$" & LastRow & ", 2, FALSE)"
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Cells(2, 1).End(xlDown).Row, .Column)), Type:=xlFillDefault
End With
Application.ScreenUpdating = True
End Sub

You should amend your other function in the same way as the only way it will be 'working' at the mo is if the value in the last cell on the sheet is an actual number and if it is at least as high as the no. of rows on the sheet. If either of these 2 factors change, your function will fail

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[rmikesmith]

Geoff,

Thanks. A more detailed explanation of what I was trying to get accross and one that, hopefully, Bill understands.


Regards,
Mike

 

[stillwillyboy]

Guys,

Thanks for the help. With Geoffs new code, I now get a type mismatch error.

I changed .Value = "=VLOOKUP(A1, VTestJobs!$A$1:$B$" & LastRow & ", 2, FALSE)" TO:

.Value = "=VLOOKUP(A1, AllJobs!$A$1:$B$" & LastRow & ", 4, FALSE)"

to work with real data and move the info to Col D on the rec'ng sheet.

Thanks again.

Bill

 

[xlbo]

That's because your VLOOKUP formula now resolves to an error:

If LastRow = 200 then:

=VLOOKUP(A1, AllJobs!$A$1:$B$200, 4, FALSE)

would be your formula - you cannot move 4 columns over if your dataset is only 2 columns wide !!!!!!

Use this instead:

.Value = "=VLOOKUP(A1, AllJobs!$A$1:$D$" & LastRow & ", 4, FALSE)"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[stillwillyboy]

Geoff,

For my clarification:

AllJobs!$A$1:$B$ refers to my source sheet. AllJobs is the sheet name; $A$1 is the reference number (i.e job #) to choose (match) from; and $B$ is the info (ie piece rate) I want to move to the receiving sheet. Correct?

On my Receiving sheet, which is the active sheet, Col A has the reference number (i.e job#) to match and Col D is the column I want the piece rate to appear.

With the your new code I get a type mismatch error.

Thanks a million times! (So far)

Bill

 

[xlbo]

...so what was the 4 for in your last post ???? If you want the RESULT of the formula to be in column D but obtaining the data from column B then you need to amend your code to:

With Range("[b]D1[/b]")
.Value = "=VLOOKUP(A1, VTestJobsInfo!$A$1:$B$200, 2, FALSE)"
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Cells(2, 1).End(xlDown).Row, .Column)), Type:=xlFillDefault
End With

Please feel free to try some of this yourself rather than just telling us you are getting errors....



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[stillwillyboy]

Guys,

I am totally baffled here. I have copied and pasted the code below into my procedure. I have checked to make sure that the Range(“B1”) is on my receiving sheet and that the “2” in the .Value line is the 2nd column of information in the data sheet from which I want to pull information. I have verified that the data sheet is named VTestJobs and that there is information in column B. I cannot limit myself to 200 rows or any other set number because there are probably 5000-6000 of actual information and the amount most likely will grow. By using the .Row I get a type mismatch (.Row is highlighted), by not using it, I get an Application error.

Sub VTesting()
Application.ScreenUpdating = False
Dim LastRow As Range
Set LastRow = Range("A65536").End(xlUp).ROW
With Range("B1")
.Value = "=VLOOKUP(A1, VTestJobs!$A$1:$B$" & LastRow & ", 2, FALSE)"
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Cells(2, 1).End(xlDown).Row, .Column)), Type:=xlFillDefault
End With
Application.ScreenUpdating = True
End Sub

I pulled out some old notes re: VLookup, checked my columns, copied and pasted exactly as instructed, but something else is just not right. I don’t have a clue.

Thanks for ALL the help and ideas.

Bill