VLookup working, but not for all records 1

[stillwillyboy]

The following is the correct code for pulling the needed info and putting it in the correct column on my timesheet. That is, up to a point.

Sub VLookUpCopiedPieceRate()
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
Range("D1").Select 'D is the piece rate on timesheet
With Range("D1")
.Value = "=VLOOKUP(A1, JobsAll!$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 have verified that JobsAll has data in Col B to pull.

If I use the following, it works up to row 36, starting in row 37, I get #N/A in my cells.
.Value = "=VLOOKUP(A1, JobsAll!$A$1:$B$" & LastRow & ", 2, FALSE)"

By trial and error, I discovered that if I use the following, it works till row 99. Adding a record in row 100 causes and Application error.
.Value = "=VLOOKUP(A1, JobsAll!$A$1:$B$654" & LastRow & ", 2, FALSE)"

Also by trial and error, I discovered that if I leave the rows of records at 99, but change my code to the following, then I get the Application error message.
.Value = "=VLOOKUP(A1, JobsAll!$A$1:$f$655" & LastRow & ", 2, FALSE)"

What is causing this? As with most application needs, I do not know ahead of time how many source records I will need to pull from plus I will have no way of knowing how many records I will need to put info in for processing.

I’m dazed and confused.

Bill

 

[PHV]

Why $654 or $655 ???

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[stillwillyboy]

I don't know why these numbers: $654 or $655, that's what I'm trying to figure out. Like I said, it was trial and error tracking down these numbers. At low numbers it worked, at high numbers it doesn't.

Bill

 

[Gavona]

$B$654" & LastRow when last row = 100 is equivalent to $B$654100. A workbook cannot contain that many rows.


Gavin

 

[Gavona]

If I use the following, it works up to row 36, starting in row 37, I get #N/A in my cells.
.Value = "=VLOOKUP(A1, JobsAll!$A$1:$B$" & LastRow & ", 2, FALSE)"

1. Look in the worksheet to see what formula has been created. Is it as expected? If not post here what has been created and what you expected/wanted.
2. Most likely the formula cannot find a match in Jobsall to A37. Formula editor may help you to understand why. Copy the value in A37 to the clipboard.


Gavin

 

[stillwillyboy]

Gavin,

I adjusted my receiving sheet (TimeSheetEntry) by adding some more records to see what would work and what would not. The line below is the new "limit" to the code. I copied it from the formula bar as requested. A76 works, A77 does not. Note the $B$100. I did not put this in the line. It appears in the formula bar for every cell (in ColD receiving the info) in the receiving sheet.

=VLOOKUP(A77, JobsAll!$A$1:$B$100, 2, FALSE)

Starting at A77, I receive #N/A all the way to the end.

My line of code in VBA is:
.Value = "=VLOOKUP(A1, JobsAll!$A$1:$B$" & LastRow & ", 2, FALSE)"

In A100 of my source sheet is 12012910. Works.
In A101 of my source sheet is 12012915. Doesn't work.

12012915 is in A77 of my receiving sheet. This is where it bombs. To verify the accuracy of the string, I copied and pasted the 12012915 from the source to the receiving sheet. It did not help.

It seems to me that there is a problem with the source information starting at A100.

Thanks.

Bill

 

[Gavona]

1. I assume that all the values that you are trying to lookup are in the range JobsAll!$A$1:$B$100

2. I would have expected the copy and paste to work for that item. I will make the following suggestion anyway:

A common problem is with things that appear to be numbers but are actually recognised by Excel as text. To convert all these references to numbers:
type 1 in a spare cell.
copy it
select column A
Edit, PasteSpecial, Multiply
Repeat for the other sheet.

3. Assuming that doesn't work then I am perplexed! What happens if you cut row77 and insert it at row 10. Does the lookup resolve then?

Running out of ideas.....


Gavin

 

[stillwillyboy]

Gavin, thanks for the ideas and patience. I think that I might have the answer, though I'm not sure why it has to be this way tow ork.

Instead of using:
.Value = "=VLOOKUP(A1, JobsAll!$A$1:$B$" & LastRow & ", 2, FALSE)"

I used a named range for my source data:
.Value = "=VLOOKUP(A1, JobsAllData, 2, FALSE)"

With what I have tested with limited data to this point, it seems to make it work. I'm gonna keep testing and working the proc. Thanks for all the help and ideas.

Bill

 

[Gavona]

Yes, I always use named ranges but that shouldn't cause the problem. Was JobsAll!$A$1:$B$100 the correct range.

To programatically update the named range (provided that data is set up according to best practice - no totally blank rows or columns and bounded by blank rows/columns or the edge of the worksheet):

Range("Alldata").CurrentRegion.Name = "Alldata"

Gavin