After sorting all of the records by Item (col A) and Date (col B) on sheet AllSalesData (the lookup table), the first 2000 or so values in column A will be numbers or numbers and a letter (I.e. 2234A).
The rest will be words (such as Admin, Handling, Postage, Storage, etc.). Both the numbers and words are “Items” in Quickbooks. This is past invoicing info has been exported to Excel.
The Items on sheet Data3BDS (receiving sheet) have also been sorted by Item and Invoice Date. This is current month invoicing data.
AllSalesData table (past invoice data)
A B
0122 01-01-10
0122 03-04-10
0122A 01-01-10
2234 08-02-10
2234A 08-02-10
4567 10-15-09
Handling 06-07-09
Postage 10-10-09
Data3BDS (current month invoice data)
A B C(sales) D(original invoice date)
0122 11-01-10 100.21
0122 11-21-10 34.55
0122A 11-01-10 5.00
0122A 11-21-10 5.00
2234 11-08-10 44.44
I am wanting to pull the original invoice date from AllSalesData coulmn B and place it in column D on sheet Data3BDS.
When I use “Set LastRow = Range(“A65536”).End(xlUP).Row” I get a Type Mismatch error and “.Row” is highlighted.
When I use “Set LastRow = Range(“A65536”).End(xlUP)” I get an “application-defined or Object-defined error” for the line beginning with “.Value”.
My code is:
I believe that the problem is in the default property value for a Range object, but I am bewildered as to the fix. I have tested the procedure by manually going to the end of column A on both sheets and adding a number (I.e. 8888) and then the proc works fine, but I also know that that is really, really bad programming.
Plus, how do I keep the procedure from filling down column D of the receiving sheet (Data3BDS) with N/A after the original invoice dates have been inserted for the current month records.
Thanks.
CD
The rest will be words (such as Admin, Handling, Postage, Storage, etc.). Both the numbers and words are “Items” in Quickbooks. This is past invoicing info has been exported to Excel.
The Items on sheet Data3BDS (receiving sheet) have also been sorted by Item and Invoice Date. This is current month invoicing data.
AllSalesData table (past invoice data)
A B
0122 01-01-10
0122 03-04-10
0122A 01-01-10
2234 08-02-10
2234A 08-02-10
4567 10-15-09
Handling 06-07-09
Postage 10-10-09
Data3BDS (current month invoice data)
A B C(sales) D(original invoice date)
0122 11-01-10 100.21
0122 11-21-10 34.55
0122A 11-01-10 5.00
0122A 11-21-10 5.00
2234 11-08-10 44.44
I am wanting to pull the original invoice date from AllSalesData coulmn B and place it in column D on sheet Data3BDS.
When I use “Set LastRow = Range(“A65536”).End(xlUP).Row” I get a Type Mismatch error and “.Row” is highlighted.
When I use “Set LastRow = Range(“A65536”).End(xlUP)” I get an “application-defined or Object-defined error” for the line beginning with “.Value”.
My code is:
Code:
Option Explicit
Sub VLookupCopyOriginalInvoiceDate()
Sheets(“Data3BDS”).Select 'sheet to receive info
Dim LastRow As Range
Set LastRow = Range(“A65536”).End(xlUp)
'Dim LastRow As Variant 'not working
'Set LastRow = Range(“A65536”).End(xlUp).Row 'not working
With Range (“D2”)
.Value = “=VLookup(A2, AllSalesData!$A$2:$E$” & LastRow & “, 2, False)”
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Cells(2, 4) _
.End(xlDown).Row, .Column)), Type:=xlFillDefault
End With
End Sub
Plus, how do I keep the procedure from filling down column D of the receiving sheet (Data3BDS) with N/A after the original invoice dates have been inserted for the current month records.
Thanks.
CD