#NAME? error

[ColdDay]

Hello,

I know that I've spent too much time on this but just cant seem to get it to work. I am wanting to copy a date from one sheet (AllSalesData) to another (Data3BDS). I keep getting #NAME? in the cells. I've read a FAQ on dates, tried various code and still get #NAME?

Sub VLookupCopyOriginalInvoiceDate()
Sheets("Data3BDS").Select  'sheet to receive info
Dim LastRow As Variant
'Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row

With Range("D2")
.Value = "=worksheetfunction.VLookup(A2, AllSalesData!$A$2:$B$" & LastRow & ", 2, False)"

'.Value = "=application.VLookup(A2, AllSalesData!$A$2:$B$" & LastRow & ", 2, False)"

'.Value = "=VLookup(A2, AllSalesData!$A$2:$B$" & LastRow & ", 2, False)"
            
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Cells(2, 4) _
    .End(xlDown).Row, .Column)), Type:=xlFillDefault
End With
End Sub

Thanks.

 

[ColdDay]

Follow up: I've tried using the following and keep getting a Type Mismatch error when I step thru and hit

Cells(Counter, "OrgInvDate").Value = OrgInvDate

From the FAQ and other posts, I thought that using Variant would deal with the dates.

Sub AnotherVLookup()
Dim OrgInvDate As Variant
Dim Counter As Integer
Dim LastRowColA As Long

Sheets("Data3BDS").Select  'sheet to receive info
   
LastRowColA = Range("A65536").End(xlUp).Row
Counter = 0
Do While Counter < LastRowColA
Counter = Counter + 1

OrgInvDate = "=Application.VLookup(A2, AllSalesData! & LastRowColA &, 4, False)"
          
'OrgInvDate = "=Application.VLookup(A2, Sheets(AllSalesData).LastRowColA, 4, False)"

If Not IsError(OrgInvDate) Then
      Cells(Counter, "OrgInvDate").Value = OrgInvDate
End If
Loop
End Sub

Any help would be appreciated. Thanks.

 

[electricpete]

With Range("D2")
.Value = "=worksheetfunction.VLookup(A2, AllSalesData!$A$2:$B$" & LastRow & ", 2, False)..."

For starters
1 - try using .formula = instead of .value
2 - get rid of worksheetfunction since you don't want that to show up in your excel formula
3 - LastRowColA means nothing to your excel workbook...it's a vba variable. Maybe you want to build the string in vba before you plug it into the formula

 

[Gavona]

Use the Watch window, step through your code and observe the values.

Looks to me as if you could simplify to a single line of code. Does this solution do it?

Sub VLookupCopyOriginalInvoiceDate()
Sheets("AllSalesData").Range("A65536").End(xlUp).Offset(0, 3).Copy _
    Destination:=Range(Sheets("Data3BDS").Range("D2"), _
            Sheets("Data3BDS").Range("D2").End(xlDown))
End Sub

Also the macro recorder is your friend. This is what it recorded for me - which wouldn't take much to generalise.

Sub Macro2()
    Sheets("AllSalesData").Select
    Range("B2").Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlUp).Select
    Range("D13").Select
    Selection.Copy
    Sheets("Data3BDS").Select
    Range("D1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
End Sub

Gavin