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.

 

[SkipVought]

Have you looked at EXCEL HELP to see what #NAME! means?

Have you entered a formula like this in the sheet and does to work sucessfully?

Please post that formula.

Also a FORMULA must be assigned to the Formula property of the range object, rather than the Value property, maybe somethin glike...

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

Also Using ActiveCell is NOT a good practice.

Skip,

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

 

[AnotherHiggins]

It looks like you don't bother reading the advice you're given.

I'll repeat myself from your last question:

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.

[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.

 

[ColdDay]

Hey Skip,

Thanks for the help. It works with what you said. I was looking at the wrong area of my VLookup code. I was using .value and trying to convert b/c it was date info. I was using Variant b/c I believed that CVar would handle any issues. But since the problem was not in what was copied (the date), but was instead with the lookup value (" & activecell.address & "), very obviously, I was wrong.

#NAME? happens when Excel "tries to interpret your entry as a name even though you intended it to be used as text." The "item#'s" that I am using for the lookup value contain both "numbers" (i.e 2296) and "numbers & text" (i.e. 2296A).The columns in both sheets for the item#'s are text.

I know that using ActiveCell is not the best way to do it. I'm using it for two reasons: 1)I can "see" what's happening with the cells better and 2)I'm up against the end of December to get this proc done. I'll be doing some reading on VBA and Excel, but there's a very good chance that I'll be asking more questions.

Once again, Thanks for the help!

 

[ColdDay]

John,

I did do the F2, but did not recognize the value of the word "Formula" in the macro.

Thanks for the help.

 

[ColdDay]

I copied the proc from home and brought it to work. Pasted it into to my proc here and a new problem appeared.

The entries for 0212 stop at row 84 in AllSalesData. Row 85 has item# 0295. It does not work. Nothing past 0212 works. There are currently over 7,000 records in AllSalesData. I will be removing duplicates b/c I need only the first invoice date. The columns are sorted first by item#, then by date.

'The items in Data3BDS are sorted by item#.
'I have manually formatted the Item# columns in both sheets to text.

Sub VLookupColA() 'looking for current 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

'still problem with Postage being given "New Job" when it is not.
Range("A2").Select
With Range("D2") 'on Data3BDS.

'following .Formula was OK at home but not at work.
'these now both give date for first item (0129) only all the way down colD.
'.Formula = "=if(iserror(VLookup(" & ActiveCell.Address & ", AllSalesData!$A$2:$E$" & LastRow & ", 2, False)),""New Job"", VLookup(" & ActiveCell.Address & ", AllSalesData!$A$2:$E$" & LastRow & ", 2, False))"

'.Value = "=if(iserror(VLookup(" & ActiveCell.Address & ", AllSalesData!$A$2:$E$" & LastRow & ", 2, False)),""New Job"", VLookup(" & ActiveCell.Address & ", AllSalesData!$A$2:$E$" & LastRow & ", 2, False))"

'the following give the correct date for items 0129 and 0212 only. _
    the rest are "New Job".  'there are other items that begin _
    with "0". they are not working. only 0129 and 0212 work.

'.Value = "=if(iserror(VLookup(A2, AllSalesData!$A$2:$E$" & LastRow & ", 2, False)),""New Job"", VLookup(A2, AllSalesData!$A$2:$E$" & LastRow & ", 2, False))"

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

.AutoFill Destination:=Range("D2:D" & LastRow)
End With
Application.ScreenUpdating = True
End Sub

Using Excel 2000 at home, 2003 at work.

Thanks.

 

[SkipVought]

...Row 85...

What is your formula in Row 84, that I assume does "work", and row 85,that I assume does NOT "work?"

'I have manually formatted the Item# columns in both sheets to text.

Exactly HOW did you manually accomplish that?

Skip,

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

 

[ColdDay]

Rows 84 and 85 are in the lookup table, AllSalesData. They do not have formulas. Row 84 is the last entry of 0212 and 85 is the first entry of 0295.

I formatted the columns on both sheets by selecting the column(s), selecting format from the tool bar and then selecting text.

This is the lookup code I am using.

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

The " & Activecell.Address & " worked on my home PC, but not at work. I copied and pasted from home to work so I know the code was the same.

Thanks.

 

[SkipVought]

I formatted the columns on both sheets by selecting the column(s), selecting format from the tool bar and then selecting text.

[red]FORMATTING CHANGES NOTHING! The underlying value remain UNCHANGED![/red]

You must actually CONVERT a NUMBER to a STRING.

Here is a method.

1. in an empty column, use the TEXT function. Assuming that your first NUMBER is in A2...
[tt]
=TEXT(A2,"0000")
[/tt]
2. copy that column

3.select column A

4. Edit > paste special -- VALUES

now column A contains TEXT regardless of the format. Remember...

[red]FORMATTING CHANGES NOTHING! The underlying value remain UNCHANGED![/red]


Skip,

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

 

[SkipVought]

BTW, if you need to ADD new rows of data manually, prefix your digits with an APOSTROPHY as...
[tt]
'0123
[/tt]


Skip,

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

 

[ColdDay]

Skip,

I no longer believe that there is any conversion problem, though I thought there was. Please do not give up on me yet.

Besides going crazy, this is what I am doing:
1)I run my lookup prcedure and check the results.

2)For item #'s that are "less than or equal to" 0212 or 0212A (even though it is being exported from Quickbooks as text, not numbers), I get a date retrieved from AllSalesData as I should.

3)But, for those items "greater than" 0212A, I get "New Job". Which is wrong if the item is not really new.

4)I go to cells in Data3BDS (the receiving sheet) and change an item (that I know for a fact s/b giving me a date, but is not), to 0129, 0212 or 0212A and I see the date cell change from "New Job" to the invoice date.

5)I then change a cell or two to 0213 and the date cell stays with "New Job". This should not be.

6)I then go to the very last cell in Data3BDS (receiving sheet). The current value is "Handling". It is saying "New Job" which is wrong.

7)I change "Handling" to 0212 and the see "New Job" change to the date.

8)To test again, I change the 0212 to 0213 and "New Job" reappears. This is wrong.

9)I went to the lookup table "AllSalesData" and deleted any repeating item#'s and ran the lookup again. Anything over 0212A does not work.

10)I then pulled "brand new, spanking clean" data from Quickbooks for Data3BDS (current month's invoicing) and AllSalesData (past months).

11)I ran the lookup again and the same exact problem occurred. Anything over 0212A will not work.

12)I had my supervisor come in and see what I was doing. She's not into VBA, but she does know Excel. She could not come up with any reason.

Skip, by now you must be thinking I'm a really big dimwit, but if the data in both columns is coming out of Quickbooks in the same format and type of data, then I believe it should work. Which it does, except for anything "greater than" 0212A.

I would be very grateful for any help.

Thanks.

 

[SkipVought]

Please post the FORMULA from the SHEET that is returning the Date or "New Job".

BTW, I would certainly NOT code a formula that would put invalid data in a column; ie mixing DATES & TEXT. NOT a good idea, IMHO!

Skip,

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

 

[ColdDay]

Here is the current lookup code I'm using. There are some notes within it.

Sub VLookupColA() 'looking for current 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

'still problem with Postage being given "New Job" when it is not.
Range("A2").Select
With Range("D2") 'on Data3BDS.

'following .Formula was OK at home but not at work.
'these both give date for first item (0129) all the way down colD.

'.Formula = "=if(iserror(VLookup(" & ActiveCell.Address & ", AllSalesData!$A$2:$E$" & LastRow & ", 2, False)),""New Job"", VLookup(" & ActiveCell.Address & ", AllSalesData!$A$2:$E$" & LastRow & ", 2, False))"

'.Value = "=if(iserror(VLookup(" & ActiveCell.Address & ", AllSalesData!$A$2:$E$" & LastRow & ", 2, False)),""New Job"", VLookup(" & ActiveCell.Address & ", AllSalesData!$A$2:$E$" & LastRow & ", 2, False))"

'the following give the correct date for items 0129 and _ 0212 only. the rest are "New Job".  'there are other _
items that begin with "0". they are not working. Only 0129, 0129A, 0212 and 0212A work. _

'.Value = "=if(iserror(VLookup(A2, AllSalesData!$A$2:$E$" & LastRow & ", 2, False)),""New Job"", VLookup(A2, AllSalesData!$A$2:$E$" & LastRow & ", 2, False))"

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

'The items in both AllSalesData and Data3BDS are sorted by item#. Both colA.

.AutoFill Destination:=Range("D2:D" & LastRow)
End With
Application.ScreenUpdating = True
End Sub

Thanks.

 

[SkipVought]

Please post the FORMULA from the SHEET that is returning the Date or "New Job".

I want to see the FORMULA from the CELL in the SHEET, not your VBA!

Skip,

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

 

[ColdDay]

Sorry.

=IF(ISERROR(VLOOKUP(A2, AllSalesData!$A$2:$E$54, 2, FALSE)),"New Job", VLOOKUP(A2, AllSalesData!$A$2:$E$54, 2, FALSE))


Increments to A3, A4, etc. as it moves down the sheet.

 

[SkipVought]

So you CHANGE '0212 to '0213 and get "New Job".

1) WHERE do you change that value?

2) In your lookup table, on what ROW is '0213?

Skip,

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

 

[ColdDay]

So you CHANGE '0212 to '0213 and get "New Job".

1) WHERE do you change that value?
On Data3BDS, A2. I change it to see if the date will appear. The lowest item # for November was 1134, which was in A2. But no date appears for 1134. Either when the proc is run or when I change "1134" to "1134". 1134 is definitely in the lookup table. But when I change 1134 to 0212, the date for 0212 appears.

2) In your lookup table, on what ROW is '0213?
Row 7. The numbers starting in A2 of AllSalesData are 0129, 0129A, 0211, 0212 and 0212A.

I will be home tomorrow and will input the same data into my home PC and see what happens.

Thanks again.

 

[SkipVought]

So EVERY value in your lookup table has been entered as

'1134

AND the data in A2 that your formula references ALSO is entered

'1134
???

Skip,

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

 

[ColdDay]

So EVERY value in your lookup table has been entered as
'1134

No. Here is my lookup table (AllSalesData) out of QB and after I remove any unneeded columns, then sort by item# and date and then remove duplicate item #'s. I KEEP the first different item number and its date b/c it is the date of first invoicing of that number. I.e 0129 may have multiple invoice dates and as such, would have multiple rows of data.

[tt]
A B
0129 3/10/2006
0129A 3/10/2006
0212 5/12/2006
0212A 5/12/2006
0213 7/21/2006
0295 7/21/2006
0308 4/14/2006
0317 8/4/2006
0325 7/21/2006
0336 05/5/2006
0350 5/31/2006
0409 4/21/2006
1109 1/12/2007
1105 6/2/2006
1134 4/14/2006
1238 10/27/2006
etc.
[/tt]
There are many, many items after 1134. The very last one is "Postage".

"AND the data in A2 that your formula references ALSO is entered '1134"

This is my receiving table Data3BDS as it looks after removing unneeded columns and row 2 which comes out of QB blank, and then sorting by item#. NOTE: ColB is for if the # in A replaced a different item#. ColD is where I want the original invoice date from the lookup table to be placed.

[tt]
A B C D
Item# Old# InvDate OrgInvDate
1134 N 11/05/2010
1238 N 11/12/2010
3648 N 11/05/2010
3650 N 11/12/2010
3699 N 11/05/2010
3724 N 11/05/2010
3724 N 11/05/2010
3726 N 11/05/2010
3727 7382 11/12/2010
3739 N 11/05/2010
3740 N 11/05/2010
3741 N 11/05/2010
3741 N 11/05/2010
3742 N 11/05/2010

[/tt]

This is my receiving table after I run my lookup on colA.
[tt]
A B C D
Item# Old# InvDate OrgInvDate
1134 N 11/05/2010 New Job
1238 N 11/12/2010 New Job
3648 N 11/05/2010 New Job
3650 N 11/12/2010 New Job
3699 N 11/05/2010 New Job
3724 N 11/05/2010 New Job
3724 N 11/05/2010 New Job
3726 N 11/05/2010 New Job
3727 7382 11/12/2010 New Job
3739 N 11/05/2010 New Job
3740 N 11/05/2010 New Job
3741 N 11/05/2010 New Job
3741 N 11/05/2010 New Job
3742 N 11/05/2010 New Job

[/tt]

This is my table after I manually enter different item #'s into various rows of my receiving table starting with A2 (was 1134, now 0129)
[tt]
A B C D
Item# Old# InvDate OrgInvDate
0129 N 11/05/2010 3/10/2006
0129A N 11/12/2010 3/10/2006
0212 N 11/05/2010 5/12/2006
0212A N 11/12/2010 5/12/2006
0213 N 11/05/2010 New Job
3724 N 11/05/2010 New Job
3724 N 11/05/2010 New Job
3726 N 11/05/2010 New Job
3727 7382 11/12/2010 New Job
3739 N 11/05/2010 New Job
3740 N 11/05/2010 New Job
3741 N 11/05/2010 New Job
3741 N 11/05/2010 New Job
3742 N 11/05/2010 New Job
[/tt]

0213 above s/h an OrgInvDate of 7/21/2006.

As always, thanks.

 

[SkipVought]

Here's what I get when I "import" your example, using your formula unchanged...
[tt]
Item# Old# InvDate OrgInvDate
1134 N 11/5/2010 4/14/2006
1238 N 11/12/2010 10/27/2006
3648 N 11/5/2010 New Job
3650 N 11/12/2010 New Job
3699 N 11/5/2010 New Job
3724 N 11/5/2010 New Job
3724 N 11/5/2010 New Job
3726 N 11/5/2010 New Job
3727 7382 11/12/2010 New Job
3739 N 11/5/2010 New Job
3740 N 11/5/2010 New Job
3741 N 11/5/2010 New Job
3741 N 11/5/2010 New Job
3742 N 11/5/2010 New Job
[/tt]

Skip,

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