VLookup problem caused by sheet issue? 1

[ColdDay]

I am having trouble with VLookUp and it is driving me crazy. I have tried the two macros below with different results. I have verified the source sheet name as JobsAll. I have verified the case of the letters and have even tried JOBSALL in both code and the sheet name. One macro shows G2 and H2 as the receiving cells and the other shows D2 and E2. I did this so I could test both and see the results. I am trying to copy # values over to the receiving sheet. I have verified that both the source and receiving sheets are in numerical order with the source and requesting columns of info of both sheets in col A. They are numbers in both. I have verified headers in row 1 of both sheets. I have verified that the last value in column F of the second macro is a number. There are 2479 rows of data in the JobsAll sheet and the current receiving sheet has only 35 rows of data. I have tried .Value and .Formula. I have also tried with no luck the following in both macros (with adjustments for the column #).

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

.Formula = "=VLOOKUP(A1, JobsAll!$A$1:$F$" & LastRow & ", 3, FALSE)"

'this one gives #NAME? in the cells

Sub VLookUpCopiedPieceRateAndStandard()
Application.StatusBar = "Accessing Piece Rates and Standards"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row 

'G2 is the piece rate on timesheet receiving the info
With Range("G2")  'must be a comma before and after "2" in following line
.Formula = "=VLOOKUP(A2, JobsAll, 2, FALSE)"
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Cells(2, 1).End(xlDown).Row, .Column)), Type:=xlFillDefault
End With

'H2 is the standard on the sheet receiving the info
With Range("H2")
.Formula = "=VLOOKUP(A2, JobsAll, 3, FALSE)" 'sheet is actually JobsAll. But this works.
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Cells(2, 1).End(xlDown).Row, .Column)), Type:=xlFillDefault
End With
End Sub

---------------------------------
'this one gives #N/A in cells.

Sub VLookUpCopiedPieceRateAndStandard999()
Application.StatusBar = "Accessing Piece Rates and Standards"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row ' or
'tried .Value also.  Same result
With Range("D1")  'must be a comma before and after "2" in following line
.Formula = "=VLOOKUP(A1, JobsAll!$A$1:$F$" & LastRow & ", 2, FALSE)"
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Cells(2, 1).End(xlDown).Row, .Column)), Type:=xlFillDefault
End With

'tried .Value also.  Same result
'Range("E1").Select   ''E is the standard
With Range("E1")
.Formula = "=VLOOKUP(A1, JobsAll!$A$1:$F$" & LastRow & ", 3, FALSE)" 'sheet is actually JobsAll. But this works.
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Cells(2, 1).End(xlDown).Row, .Column)), Type:=xlFillDefault
End With
End Sub

The reason I feel that there is a problem with the sheet and not the code is that the following macro also does not work when trying to pull over a non-numeric description of the info being asked for BUT this macro DOES work in a different WB. The macro has been copied and pasted exactly as written from the different workbook where it works as it is written, but it will not work in the WB that I am trying to get it to work in now.

Sub VLookUpCopiedStepDescription()
Application.StatusBar = "Accessing Step Description "
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row ' or

'Range("B1").Select   'D is the piece rate on timesheet
With Range("B2")  'must be a comma before and after "2" in following line
.Value = "=VLOOKUP(A1, JobsAll, 5, 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 read many posts and checked my notes on VLookUp but this one really has me stumped. No doubt it will be a "duh" moment when the answer is revealed, but right now I am lost. Thanks for any help.

 

[SkipVought]

hi,

Is there a reason that you are using VBA to enter a spreadsheet formula on your sheet, rather than just entering on your sheet manually?

Have you done the VLOOKUP() function ON THE SHEET MANUALLY?

If no, why not?

If you have, then please COPY your VLOOKUP formula from your Formual Bar and PASTE/post back. Please include the CELL ADDRESS from which you copied your formula and please summarize your results.


Please answer each and every question fully.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Dim LastRow As [highlight]LONG[/highlight]
LastRow = [highlight]Cells(cells.rows.count, 1)[/highlight].End(xlUp)[highlight].Row[/highlight]

.Formula = "=VLOOKUP(A1, JobsAll!$A$1:$F$" & LastRow & ", 3, FALSE)"

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

also #NA! means lookup value not found!

So exactly what value are you using to lookup? (I'd wager it is a NUMBER in one place stored as a number and in another place as a STRING)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ColdDay]

Skip,
I'm doing it with VBA to automate a very big process. To do it manually just is not practical. I hope I understood what you were requesting with the following. Also, I'm using XL 2010 and every time I want to paste, the screen flickers so I do not know if I'm doing it correctly or not. Sorry.

I copied the following from the formula bar which was reading cell G2 which is the first cell on the receiving sheet to get the info. I got the Name error. I also pasted it into G5 to test. The A2 stayed as A2, as opposed to changing to G5 when run by VBA.

=VLOOKUP(A2, JobsAll, 2, FALSE)

I copied and pasted the following from D2 and pasted it into D5. It stayed at A2. I got NA when I pressed enter.

=VLOOKUP(A1, JobsAll!$A$1:$F$" & LastRow & ", 2, FALSE)

Skip, I am so frustrated right now that I cannot concentrate. If you can see something that will help, thanks. Otherwise, I'll be back tomorrow.

 

[SkipVought]

What is the value of LASTROW???

If yer gonna PASTE something into a cell, you gotta have a row number like 9999!
[tt]
=VLOOKUP(A1, JobsAll!$A$1:$F$9999, 2, FALSE)
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ColdDay]

The value of LastRow is 417263330. It is cell A2445.

I've got a few other VLookUp's very similar to JobsAll!$A$1:$F$ that work. Such as:

=VLOOKUP(A1, CLNTINFO!$A$1:$Z$" & LastRow & ", 2, FALSE)

Isn't the value of LastRow appeneded to $F$. Resulting in the cell range of $A$1:$F$2445?

Thanks.

 

[ColdDay]

Not the value of LastRow (417263330) but instead the row number 2445 resulting in $F$2445.

 

[SkipVought]

You never answered my question of 29 Jan 14 13:13!

What is a typical lookup value in an NA?


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ColdDay]

The value returned for both columns showing NA are numbers. Col D would be something like .003, .54, .01, etc. Col E will be a whole number such 123, 29, 48, etc.

The same result is asked for with the columns showing NAME.

Also, the info on the source sheet that has the numbers listed above was first copied and inserted into the current WB from a WB where the code works. When the VLookUp failed, I then copied and pasted the info from the good WB to the current one where it still isn't working. This is one of the reasons I feel that there is a sheet issue b/c the exact same code works in one WB but not another.

Thanks.

 

[ColdDay]

Skip, I did two things that appear to have worked. Or maybe only one of them did. First, I changed the code to:

Dim LastRow As Long
'added Sheets("JobsAll")  to the following line
LastRow = Sheets("JobsAll").Range("A65536").End(xlUp).Row ' or
With Range("D1")  
.Value = "=VLOOKUP(A1, JobsAll!$A$1:$F$" & LastRow & ", 2, FALSE)"
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Cells(2, 1).End(xlDown).Row, .Column)), Type:=xlFillDefault
End With

'Range("E1").Select   ''E is the standard
With Range("E1")
.Value = "=VLOOKUP(A1, JobsAll!$A$1:$F$" & LastRow & ", 3, FALSE)" 'sheet is actually JobsAll. But this works.
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Cells(2, 1).End(xlDown).Row, .Column)), Type:=xlFillDefault
End With

I also ran the following code of which may not have made any difference at all:

Sub ConvertTextNumbersToNumberNumbers()
Application.StatusBar = ""
Application.ScreenUpdating = False
Dim c As Range
Columns("A:A").NumberFormat = "@"
Range("A2").Select
Do
For Each c In Selection
If IsNumeric(c.Value) Then
c.Value = Val(c.Value)
 ActiveCell.Offset(1, 0).Select
End If
Next c
Loop Until ActiveCell.Value = ""
Range("a1").Select
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

Thanks for your input and getting me to think.

 

[SkipVought]

So all is well, eh?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ColdDay]

Hey Skip,

At the moment, all is looking well. The report ran as desired. I plan to go back and update my other VLookUps for consistency and hopefully avoid issues in the future.

Thanks again.

 

[SkipVought]

Great! Continued success!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]