Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VLookup errors

Status
Not open for further replies.

ColdDay

Technical User
Nov 30, 2010
92
US
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:
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
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





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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 


numbers or numbers and a letter
THAT is a problem IF your "numbers" are really NUMBERS, rather than STRING of numeric characters, Computers don't handle a mixture of NUMBERS and STRINGS in the same field very well and WILL cause a VLOOKUP error.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Just a follow up Q: 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.

Is it best to control this problem with a counter proc as in:
Code:
Dim x
    
    lastRowColA = Range("A65536").End(xlUp).Row
    counter = 0
    Do While counter < lastRowColA
          counter = counter + 1

          x = Application.VLookup(Range("B1"), Sheets("Sht-2").Range("C10:F1000"), 4, False)
          
          If Not IsError(x) Then
            Cells(counter, "X").Value = x
          End If
    Loop

Skip, I believe you wrote the above in a past post. I know I'll need to adjust it to my needs.

Thanks.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top