Help! IF/Then Vlookup with offset?

jmthompson

Well-known Member
Joined
Mar 31, 2008
Messages
966
To begin, I did have another thread looking for a solution, but I don't think the subject was descriptive enough- see original thread here- http://www.mrexcel.com/forum/showthread.php?t=325282


I have tried this code a number of ways, and no matter what I try, I get run-time 1004 application-defined or object-defined error.
Here is my code:

Code:
Sub DropShip()
MyDate = DateSerial(Year(Date), Month(Date) - 1, 1)
MyMonth = Format(MyDate, "mmmm")
MyYear = Year(MyDate)
MyPath = "C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Drop Ship\"
MyFile = MyMonth & " " & MyYear & ".xls"
MyPDFile = "TPD " & MyMonth & " " & MyYear & ".xls"
Sheets(MyMonth & " " & MyYear).Select
    Dim c As Range
 
        For Each c In Range([B50], Cells(Rows.Count, "B").End(xlUp))
            If c = "12100 - The Grand Rapids Press" Then c.Offset(0, 4).FormulaR1C1 = "=VLookup(" & c.Offset(0, 1).Address & ", '[C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\2008\May\Drop Ship\May 2008.xls]Grand Rapids'!(B5:D23), 3, False)"

Next c
End Sub

error here:
Code:
Then c.Offset(0, 4).FormulaR1C1 = "=VLookup(" & c.Offset(0, 1).Address & ", '[C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\2008\May\Drop Ship\May 2008.xls]Grand Rapids'!(B5:D23), 3, False)"

In this iteration, I have entered the actual file name for the table array. I am at a loss. I don't know what is causing the error, is it the use of c.Offset? The syntax of the table array? Or something else entirely?

I would appreciate any suggestions!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
I think in this case it's a syntax thing...
Only the FILENAME get's put in []
And you have praenthesis around the range
And take of the R1C1, since c.Offset(0, 1).Address is going to return an A1 style address, not an R1C1 style..

It should be like 'C:\Path\[filename.xls]Sheetname'!Range...

Try like this.
Code:
Then c.Offset(0, 4).Formula = "=VLookup(" & c.Offset(0, 1).Address & ", 'C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\2008\May\Drop Ship\[May 2008.xls]Grand Rapids'!B5:D23, 3, False)"
 
Upvote 0
jommo1- You rock, as always! I think I have tried each of those things, but not all of them at the same time.

pheew!

I do have a few more questions- if anyone can help with any one of them, I would appreciate it!

Code:
Sub DropShip()
MyDate = DateSerial(Year(Date), Month(Date) - 1, 1)
MyMonth = Format(MyDate, "mmmm")
MyYear = Year(MyDate)
MyPath = "C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Drop Ship\"
MyFile = MyMonth & " " & MyYear & ".xls"
MyPDFile = "TPD " & MyMonth & " " & MyYear & ".xls"
Sheets(MyMonth & " " & MyYear).Select
    Dim c As Range
 
        For Each c In Range([B50], Cells(Rows.Count, "B").End(xlUp))
            If c = "12100 - The Grand Rapids Press" Then c.Offset(0, 4).Formula = "=VLookup(" & c.Offset(0, 1).Address & ", '" & MyPath & "[" & MyFile & "]Grand Rapids'!B5:D23, 3, False)"
                                                                       
Next c
End Sub

1. The number of rows in my table array will change with every use. How would I change this formula to include a lastrow statement for the table array or how cn I change this formula to include the entire columns from B:D?


2. The final macro will include 26 versions of this code to 26 possible values of c, it is possible that the sheet name I reference in the vlookup will not exist for every value of c, what can I include in the code for the macro to skip that step and move to the next?

3. Can I include something in the then statement to return nothing in place of #NA? If not, I will add a find/replace step at the end of the macro (after replacing the formulas with their values.

Thanks, again!
 
Upvote 0
1. You can set a variable to the last used row of the range, and use that variable in the vlookup...

Code:
LR = Sheets("Grand Rapids").Cells(Rows.Count,"B").End(xlup).Row
If c = "12100 - The Grand Rapids Press" Then c.Offset(0, 4).Formula = "=VLookup(" & c.Offset(0, 1).Address & ", '" & MyPath & "[" & MyFile & "]Grand Rapids'!B5:D" & LR & ", 3, False)"

2. Check if the sheet exists first...
Code:
On Error Resume Next
X = Sheets("Grand Rapids").Name
On Error Goto 0
If X = "Grand Rapids" Then...

3. First check if the value exists...
Code:
X = Application.Match(c.Offset(0, 1).Value,Workbooks(MyPath & MyFile).Sheets("Grand Rapids").Range("B5:B" & LR),0)
If IsError(X) Then
    c.Offset(0, 4).ClearContents
Else
    vlookupcodehere
End If

Hope this helps...
 
Last edited:
Upvote 0
jonmo1-

Wow, thanks!

I do have a question about the second response- where in my code would I put this code?

Code:
 On Error Resume Next
X = Sheets("Grand Rapids").Name
On Error Goto 0
If X = "Grand Rapids" Then...

Code:
Sub DropShip()
MyDate = DateSerial(Year(Date), Month(Date) - 1, 1)
MyMonth = Format(MyDate, "mmmm")
MyYear = Year(MyDate)
MyPath = "C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Drop Ship\"
MyFile = MyMonth & " " & MyYear & ".xls"
MyPDFile = "TPD " & MyMonth & " " & MyYear & ".xls"
Sheets(MyMonth & " " & MyYear).Select
    Dim c As Range
 
        For Each c In Range([B50], Cells(Rows.Count, "B").End(xlUp))
            If c = "12100 - The Grand Rapids Press" Then c.Offset(0, 4).Formula = "=VLookup(" & c.Offset(0, 1).Address & ", '" & MyPath & "[" & MyFile & "]Grand Rapids'!B5:D23, 3, False)"
                                                                       
Next c
End Sub
 
Upvote 0
Haven't tested, but it should work..

Code:
Sub DropShip()
MyDate = DateSerial(Year(Date), Month(Date) - 1, 1)
MyMonth = Format(MyDate, "mmmm")
MyYear = Year(MyDate)
MyPath = "C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\" & MyYear & "\" & MyMonth & "\Drop Ship\"
MyFile = MyMonth & " " & MyYear & ".xls"
MyPDFile = "TPD " & MyMonth & " " & MyYear & ".xls"
Sheets(MyMonth & " " & MyYear).Select
Dim c As Range
Dim X As Variant
For Each c In Range([B50], Cells(Rows.Count, "B").End(xlUp))
    If c = "12100 - The Grand Rapids Press" Then
        On Error Resume Next
        X = ""
        X = Sheets("Grand Rapids").Name
        On Error GoTo 0
        If X = "Grand Rapids" Then
            LR = Sheets("Grand Rapids").Cells(Rows.Count, "B").End(xlUp).Row
            X = Application.Match(c.Offset(0, 1).Value, Sheets("Grand Rapids").Range("B2:B" & LR), 0)
            If IsError(X) Then
                c.Offset(0, 4).ClearContents
            Else
                c.Offset(0, 4).Formula = "=VLookup(" & c.Offset(0, 1).Address & ", '" & MyPath & "[" & MyFile & "]Grand Rapids'!B5:D" & LR & ", 3, False)"
            End If
        Else
            c.Offset(0, 4).ClearContents
        End If
    End If
Next c
End Sub
 
Upvote 0
jonmo1-
Does this code check for the existance of the specified sheet in the correct file? I don't see that direction (not that I would know, really) and when I tested this code with a sheet that does exist, it did not find that sheet.

It should look for the sheet in "MyPath & MyFile" or whever the correct syntax would be in this context
 
Upvote 0
Oh yes, sorry...

Anywhere you see
Sheets("GrandRapids")
There are 3 occurances of it...

Change it to
Workbooks(MyPath & MyFile).Sheets("GrandRapids")
 
Upvote 0
Something must be wrong here:
Code:
        X = ""
        X = Workbooks(MyPath & MyFile).Sheets("Grand Rapids").Name
        On Error GoTo 0
        If X = "Grand Rapids" Then

because it jumps from there to
Code:
 Else
            c.Offset(0, 4).ClearContents

even though I know that sheet exists. I tried replacing MyPath and MyFile with the actual path and file, that didn't change my results.

I just removed the "On Error Resume Next" code and got an Subscript out of range error, so it must be a syntax thing, right?

Here is the code with the path and file manually entered:
Code:
 X = Workbooks("C:\Documents and Settings\JThomps2\Desktop\WorkflowOne\Billing\2008\May\Drop Ship\May 2008.xls").Sheets("Grand Rapids").Name
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top