Frustrating #NAME? Problem 1

[ColdDay]

How do I get the date value and not #NAME? in the following?

Thanks.

Sub VlookupColBUsingDoLoopWithErrorTrap ()
Application.ScreenUpdating = False
Sheets("Data3BDS").Select 'sheet to receive the date info
 
'LastRow is for the item# on lookup table DataAllSales
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Offset(1, 0).Row
 
Sheets(“Data3BDS”).Select
Range(“B1”).Select
 
Do
With ActiveCell
If IsEmpty(ActiveCell) Then
Err.Number = 0
On Error Resume Next
ActiveCell.Offset(1,0).Select
 
Else
With Range(ActiveCell.Offset(0, 4).Address)
If Err.Number = 0 Then
.Value = "=VLookup(activecell.address, AllSalesData!$A$2:$E$" & LastRow & ", 2, False)”
End If
 
ActiveCell.Offset(1, 0).Select
End With
End If
End With
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
Application.ScreenUpdating = True
End Sub

These are the different Cvar's that I've tried.

.Value = CVar("=VLookup(activecell.address, AllSalesData!$A$2:$E$" & LastRow & ", 2, False)”)
.Value = "=CVar(VLookup(activecell.address, AllSalesData!$A$2:$E$" & LastRow & ", 2, False))”
.Value = CVar("=VLookup(activecell, AllSalesData!$A$2:$E$" & LastRow & ", 2, False)”
.Value = "=CVar(VLookup(activecell, AllSalesData!$A$2:$E$" & LastRow & ", 2, False)”

At this point in time, all I want to do is get the date, not #NAME?


Thanks.

 

[ColdDay]

I got it!

Here was the problem:

Sheets("Data3BDS").Select 'sheet to receive the date info
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Offset(1, 0).Row

The problem with that is that it is setting LastRow to the number of rows of data on Data3BDS. I was needing the number of rows from AllSalesData! DOH!

Here is my corrected code:

Sub VLookupColA() 'looking for current item #
Application.ScreenUpdating = False
'The items in both AllSalesData and Data3BDS are sorted by item#. Both colA.

'NEED BOTH a LastRowASD AND a LastRowD3BDS VARIABLE

'Sheets("AllSalesData") 'lookup table
Dim LastRowASD As Long
LastRowASD = Sheets("AllSalesData").Range("A65536").End(xlUp).Offset(1, 0).Row

'Sheets("Data3BDS")  'receiving table
Dim LastRowD3BDS As Long
LastRowD3BDS = Sheets("Data3BDS").Range("A65536").End(xlUp).Offset(1, 0).Row

Sheets("Data3BDS").Select 'sheet to receive the date info

With Range("D2") 'on Data3BDS. 'note use of LastRowASD in VLookup

.Formula = "=if(iserror(VLookup(A2, AllSalesData!$A$2:$B$" & LastRowASD & ", 2, False)),""New Job"", VLookup(A2, AllSalesData!$A$2:$B$" & LastRowASD & ", 2, False))"

‘had to adjust this line also
.AutoFill Destination:=Range("D2:D" & LastRowD3BDS)

End With
Application.ScreenUpdating = True
End Sub

I made a few other small tweaks as well.

Skip, thanks for your help. I hope that someone else may be able to learn from my frustration!