VLookup and .Offset 2

[stillwillyboy]

I have the following table created using the Subtotal function:
Cust# Invoice# Amount
12 900 1000
12 1000

36 912 200
36 913 300
36 500

417 925 100
417 100

I am wanting to pull the customer name from the CustNumb worksheet that is in the same workbook as the above.
My new table would look like:
Cust# Invoice# Amount
12 900 1000
12 Eagle 1000

36 912 200
36 913 300
36 Pete’s 500

417 925 100
417 Freddy 100

The table where I am pulling the info from (custnumb) has the customer number in column A and the customer name in column B.

Below is my code. I keep getting the message: “Application-defined or Object-defined error” when running the procedure.

Sub VLookUpCopied()
Application.ScreenUpdating = False
Dim LastRow As Range
Set LastRow = Range("A65536").End(xlUp)

With ActiveCell 'Range("B1") 'B2 is Name
.Offset(0, 1).Select
‘the line below is causing the error message
.Value = "=VLOOKUP(A1, custnumb!$A$1:$c$" & LastRow & ", 2, FALSE)"
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Cells(2, 1).End(xlDown).Row, .Column)), Type:=xlFillDefault

.Offset(0, -1).Select ‘move activecell back to column A to select next customer number and in turn _
the next customer name
End With
Application.ScreenUpdating = True
End Sub

Thoughts?

TIA

 

[Zathras]

You have confused the use of a Range object with the need to reference a row number.

Your immediate problem can be addressed by changing the offending line to read as follows:

.Value = "=VLOOKUP(A1, custnumb!$A$1:$c$" & LastRow.Row & ", 2, FALSE)"

But "LastRow" is a poor choice of names for a range object that contains a single cell.

An additional problem appears to be that you are trying to find the last row of the name table by using the last row from the current sheet, which does not appear to be the sheet that contains the name table.

 

[stillwillyboy]

Zathras,

I have not been successful in fixing the problem. The problem still lies in the .Value and .Autofill lines. I am getting #N/A in the cell where I should be getting the customer name. My code is below and I feel that it has gotten very sloppy. TIA, Bill.

Sub InsertCustomerNames()
Dim LastRow As Range
Set LastRow = Range("A65536").End(xlUp)
Range("A2").Select
Do
With ActiveCell
If ActiveCell.Font.Bold = True Then
.Offset(0, 1).Select

With ActiveCell
.Value = "=VLOOKUP(a2, custnumb!$A$1:$b$" & LastRow.Row & ", 2, FALSE)"
Stop
.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Cells(2, 1).End(xlDown).Row, .Column)), Type:=xlFillDefault
' .AutoFill Destination:=Range(ActiveCell)
Stop
.Offset(0, -1).Select
End With
End If
End With
With ActiveCell
.Offset(1, 0).Select 'gotta have this or will get caught in loop
End With
'Stop
Loop Until ActiveCell = "Grand Total"
End Sub
Sub InsertRowsBetweenCustomers()
Range("A2").Select
'Stop
Do
With ActiveCell
If ActiveCell.Font.Bold = True Then
' Stop
.Offset(1, 0).EntireRow.Insert
' Stop
.Offset(1, 0).Select
' Stop
End If
End With
With ActiveCell
.Offset(1, 0).Select 'gotta have this or will get caught in loop
End With
'Stop
Loop Until ActiveCell = "Grand Total"
End Sub

 

[stillwillyboy]

After doing some reading, I made some changes. I named the range on the CustNumb sheet to CustNumbRange. The range is A1:B250 right now. I think there is a problem with my declaration of CustNumbRange.

All ws are in the same wb. I came up with the following code. When the code is run, I get #NAME? in the desired cell. TIA. Bill

Sub InsertCustomerNames()
Dim CustNumb As String 'CustNumb sheet is in same wb
Dim CustNumbRange As Range 'the word Range is not blued _
in my code, but I think it should be
Range("A2").Select
Do
With ActiveCell
If ActiveCell.Font.Bold = True Then
.Offset(0, 1).Select
'MsgBox TypeName(ActiveCell)
'Stop
'Below, CustNumb is the column name for col A. I tried _
A1 with no luck.
With ActiveCell
.Value = "= VLookup(CustNumb, CustNumbRange, 2, False)"
'Stop
.Offset(0, -1).Select
End With
End If
End With
With ActiveCell
.Offset(1, 0).Select 'gotta have this or will get caught in loop
End With
'Stop
Loop Until ActiveCell = "Grand Total"
End Sub

 

[Zathras]

Rather than work with your code, let me offer a programmer's way of doing it. I think you will agree that there is a lot less code this way:

Sub InsertCustomerNames()
Dim c As Range
  For Each c In Intersect(Range("A:A"), ActiveSheet.UsedRange)
    If c.Font.Bold And c.Value <> "Grand Total" Then
      c.Offset(0, 1).Value = GetName(c.Offset(-1, 0).Value)
    End If
  Next c
End Sub

Private Function GetName(ANumber As Integer) As String
  With Sheets("custnumb")
    GetName = Application.WorksheetFunction.VLookup(ANumber, _
                    .Range("A1:B250"), 2, 0)
  End With
End Function

Working with multiple sheets can be tricky. Generally it is best not to work with .Select and ActiveCell. Also, I find it easier to cope when I separate the working code into one or more functions and remove it from the loop control structure. In other words, I try to separate the "What" of the macro from the "How" in an effort to keep things clear.

Study this code and learn how it works. (Step through with the F8 key to see the processing.) Post back if you have any questions that aren't answered in the help files.

 

[stillwillyboy]

Zathras, I'm striking out here. I was getting the App. Defined Error message at first, then I changed the .Offset(-1, 0) to (0, 1). This stopped the error message.

Sub InsertCustomerNames()
Dim c As Range
For Each c In Intersect(Range("A:A"), ActiveSheet.UsedRange)
If c.Font.Bold And c.Value <> "Grand Total" Then
c.Offset(0, 1).Value = GetName(c.Offset(0, 1).Value) 'change made here. was (-1, 0). stopped msg.
End If
Next c
End Sub

Now when the function is called, I get the error message "Unable to get the VLookup property of the WorksheetFunction class". I put my worksheet name in the call line and I changed 0 to False, but I was getting the error msg before I made the changes.

Private Function GetName(ANumber As Integer) As String
' With Sheets("custnumb") 'added Workseets("custnumb") below
GetName = Application.WorksheetFunction.VLookup(ANumber, _
Worksheets("custnumb").Range("A1:B250"), 2, False)
' End With
End Function

I found the following in HELP and played around with it to learn and see if my ranges were set up correctly. I know that it doesn't do the VLookup aspect, but it does give me the proper minumum value from my worksheet CustNumb.

Sub MinFunction()
Dim MyRange As Range
Dim CustNumbRange As Range
Dim answer As String
Set MyRange = Worksheets("custnumb").Range("custnumbrange")
answer = Application.WorksheetFunction.Min(MyRange)
MsgBox answer
End Sub

Am I not seeing something that is in front of me? Thanks.

 

[Zathras]

It works just fine for me as I posted.

I don't understand the change to
[tt]
c.Offset(0, 1).Value = GetName(c.Offset(0, 1).Value)
[/tt]
Since the cell at offset 0,1 is empty after the subtotals operation, how can you use it to do a lookup? I used the customer number from the row above the subtotal row, hence the offset -1,0.

When the lookup fails (as it will with a blank customer number, you get the message you see.

Perhaps you have row 1 bolded. in which case you should modify the loop control to ignore that row (as well as the "Grand Total" row). I.e.,
[tt]
If c.Font.Bold And c.Value <> "Grand Total" _
And c.Value <> "(Your value from row one)" Then
etc.
[/tt]

 

[stillwillyboy]

Zathra's,

You're awesome!! First, I did have the first row of info bolded. It was the shop name. I can add it later.

Second, I see now the .offset (-1, 0) now.

Thanks!!!