VLookup column Q

[ColdDay]

'Problem with the proc not working properly. No _
syntax errors, just wrong results. puts "New Job" _
into all cells of colE. Chances are that some will be new, _
but most cells should be receiving an original _
invoice date.

'How this proc is supposed to work:
'looks at value in colA of Data3BDS sheet, sees if _
that value is in colA of AllSalesData sheet. If yes, it copies _
the original invoice date from AllSalesData and places it _
in the appropriate item row of colE of Data3BDS. If there is no matching value in colA of AllSalesData, then colE of Data3BDS is filled with "New Job".

Sub VLookupUseColA() 'looking for new item# or item# that _ is not replacing a different item#. pulling invoice date

Application.ScreenUpdating = False
Sheets("Data3BDS").Select 'sheet to receive the date info
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Offset(1, 0).Row
 
With Range(activecell.Offset(0, 4).Address)
'The above line gives me address $E$2 as the activecell _
'"E" is the column to receive the original invoice date.

.Value = "=if(iserror(VLookup(activecell.offset(0,-4), AllSalesData!$A$2:$E$" & LastRow & ", 2, False)),""New Job"", VLookup(activecell.offset(0,-4), AllSalesData!$A$2:$E$" & LastRow & ", 2, False))"

'The above line puts "New Job" into all cells of colE _
instead of the original invoice date. But that is _ not 'correct. The item# being looked up is definitely _
in the "AllSalesData" table.

End With
Application.ScreenUpdating = True
End Sub

Thanks.

 

[AnotherHiggins]

Here's a hint:

While recording a macro, double click (or press [F2]) into a cell containing the VLookup formula you want to use then immediately press enter.

That will generate code showing you what the formula should look like in VBA.

Also, based on what you wrote it seems to me that you could just fill that formula in to column E for all populated rows.... Why not just do that instead of only doing it for the active row?

Also, there's no reason to capture the last row.... Just base your VLookup on the entire columns on AllSalesData.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Oh, and what does column Q have to do with anything??

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

hi,

WHY are you using VBA? This can simply be done using VLOOKUP on the sheet!

If your answer is that the rows vary, use the Data > Create > Create List feature in 2003 or earlier of the Data > Insert > Table feature in 2007 and following.

The resulting table will propogate formulas as new rows of data are added.

Also FYI, "If yes, it copies..." ?

There is no COPY in your process. The VLOOKUP() function or any spreadsheet formula RETURNS a value. ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

I think he meant Q as in "Question"?

 

[SkipVought]

Not as in Queue?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Perhaps he was watching a James Bond flick.

Surely this is the case; which is now closed.

 

[ColdDay]

Please let me give the complete scenario.

'I am needing to look up a "first time invoiced" date from _
sheet "AllSalesData", (my lookup table). AllSalesData _
will have an Item# in ColA with the "first time invoiced" _
date in ColB.

'I have another sheet, "Data3BDS", that will receive this _
"first time invoiced" date into ColE. ColA of Data3BDS _
will have the item# that was used for invoicing for the _
"current" month. But, if the item# in ColA has replaced _
an old item#, then ColB will have the old item#. _

'Please note that not all item#'s will have an old #, _
but if they do, I have to use the old # when looking _
for the "first time invoiced" date. _

'As an example, on sheet Data3BDS, if item# 6789 _
(in ColA) replaced old item# 1234 (which will be in ColB). _
I need to use item# 1234 to find the original invoice _
date from the "AllSalesData" sheet.

'The sheet to receive this date, Data3BDS, will also have an invoice date for the month being closed, ColC. I will then have to calculate the number of days b/w the current _
invoice date (ColC) and the original invoice date (ColD) _
that I got from AllSalesData. (I know how to do this part.) _

'I'm thinking that the logic for this would be to: _
1) Look in ColB of Data3BDS for an item# _
2) If ColB has an item#, use this item# in my VLookup to _
find the original invoice date on sheet AllSalesData, _
3) ELSE, If ColB IsEmpty, then use the item# in ColA _
for my VLookup. _

'If this is the first month that the item# has been invoiced it will not be in AllSalesData. If this is the case then my VLookup will place "New Job" in ColE for that _
item's original invoice date.

'I am using the following Do Loop code to call the proc _
that will get me the data depending if the item# is in _
ColA or ColB. It is looping properly, but do I need it?

If there is NO item in colB, use the value in colA as the lookup value. Otherwise, use the value in colB. colC is this month’s invoice date. colD is where I want the past invoice date placed. I was going to use a seperate proc with IF to call a VLookup for colB if needed, Else call the proc for colA.

Sub VLookupLoopThru()
Sheets("Data3BDS").Select
Range("A2").Select 'start in ColA b/c will have data in last cell _
to use IfEmpty
Do
With activecell
If IsEmpty(activecell) Then 'loop until A is empty on Data3BDS
Exit Sub 'cell in ColA is empty
Else

'look at B first for an item#
If IsEmpty(activecell.Offset(0, 1)) Then 
VLookupUseColA  'if B is empty, use A

Else 'if B has item (not empty), use item # in B
VLookupUseColB

End If
End If
End With
activecell.Offset(1, 0).Select
Loop Until IsEmpty(activecell)

End Sub

Data3BDS table (before running VLookup)
A B C D
1234 11-01-10
3456 11-01-10
7890 2222 11-15-10
6666 11-30-10

AllSalesData (lookup table. Past invoicing data)
A B
0123 09-09-2009
1234 09-10-2009
2222 10-01-2010
5555 05-05-2010
6666 06-06-2010

Data3BDS table (after running VLookup)
A B C D
1234 11-01-10 09-10-2009
3456 11-01-10 New Job
7890 2222 11-15-10 10-01-2010
6666 11-30-10 06-06-2010

'Problem with the next two proc's not working properly. No _
syntax errors, just wrong results. Both put "New Job" _
into all cells of colD. Chances are that some will be new, _
but most cells should be receiving an original _
invoice date.

Sub VLookupUseColA() 'looking for current item #
'looking for new item# or item# that is not replacing _
a different item#
Application.ScreenUpdating = False
Sheets("Data3BDS").Select 'sheet to receive the date info
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Offset(1, 0).Row

 
'How this proc is supposed to work:
'looks at value in colA of Data3BDS sheet, sees if _
that value is in colA of AllSalesData sheet. If yes, it copies the original invoice date from AllSalesData and places it in colD of Data3BDS. If there is no matching value in colA of AllSalesData, then colD of Data3BDS is filled with "New Job".

 
'With Range("D2") 'on Data3BDS. Use this line only if using 
AutoFill. But can't use AutoFill if using item #'s from _
both ColA and ColB. Correct?

With Range(activecell.Offset(0, 3).Address)
'The above line gives me address $D$2 as the activecell _
the first time thru "VLookupLoopThru". This is correct. _
 
The second time thru I should get $D$3, etc. _
"D" is the column to receive the original invoice date.

.Value = "=if(iserror(VLookup(activecell.offset(0,-3), AllSalesData!$A$2:$D$" & LastRow & ", 2, False)),""New Job"", VLookup(activecell.offset(0,-3), AllSalesData!$A$2:$D$" & LastRow & ", 2, False))"
'The above line puts "New Job" into all cells of colD _
instead of the original invoice date. But that is not correct. _
The item# being looked up is definitely in the "AllSalesData" table.

End With
Application.ScreenUpdating = True
End Sub

'the following proc should work the same as the one above, _
except it should read the value in the cell in colB _
of the Data3BDS sheet(if there is a value in colB) _
and pull the original invoice date _
from AllSalesData and place it in the appropriate row _
of ColD of the Data3BDS sheet

Sub VLookupUseColB()
'looking for old item # invoice date
Application.ScreenUpdating = False
Sheets("Data3BDS").Select 'sheet to receive the date info
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Offset(1, 0).Row

With Range(activecell.Offset(0, 3).Address) 'on Data3BDS
.Value = "=if(iserror(VLookup(activecell.offset(0,-3), AllSalesData!$A$2:$D$" & LastRow & ", 2, False)),""New Job"", VLookup(activecell.offset(0,-3), AllSalesData!$A$2:$D$" & LastRow & ", 2, False))"
'same problem as VLookupUseColA
End With
Application.ScreenUpdating = True
End Sub

I’ve done so many different flavors of code that I’ve got myself really confused. Any help would be greatly appreciated.

 

[ColdDay]

Yes, "Q" as in question.

 

[SkipVought]

COLD,

Please look at your post of 6 Dec 10 14:41.

Please note that your examples of table data is AMBIGUOUS, due to your not using TGML [ignore][TT]...[/TT][/ignore] tags

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ColdDay]

Sorry. Did not know the table tags.

[TT]
Data3BDS table (before running VLookup)
A B C D
1234 11-01-10
3456 11-01-10
7890 2222 11-15-10
6666 11-30-10

AllSalesData (lookup table. Past invoicing data)
A B
0123 09-09-2009
1234 09-10-2009
2222 10-01-2010
5555 05-05-2010
6666 06-06-2010

Data3BDS table (after running VLookup)
A B C D
1234 11-01-10 09-10-2009
3456 11-01-10 New Job
7890 2222 11-15-10 10-01-2010
6666 11-30-10 06-06-2010

[/TT]

 

[SkipVought]

Using Named Ranges for...
[tt]
InvoiceID InvoiceDte
0123 9/9/2009
1234 9/10/2009
2222 10/1/2010
5555 5/5/2010
6666 6/6/2010
[/tt]
my formula, NO VBA
[tt]
=IF(ISNA(MATCH(INDEX(A2:B2,1,COUNTA(A2:B2)),InvoiceID,0)),"NEW JOB",INDEX(InvoiceDte,MATCH(INDEX(A2:B2,1,COUNTA(A2:B2)),InvoiceID,0),1))
[/tt]
assuming that your DATA on sheet Data3BDS, begins in A2.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}