Help! IF/Then Vlookup with offset?

[jmthompson]

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:

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:

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!

 

[Jonmo1]

Ahh, can't refer to a closed workbook that way.

Is the book May 2008.xls Open at the time the macro is running? If so you could just remove the path...

 

[jmthompson]

It is not open, as my original macro didn't require it. However, I cant make it SOP for the user to open both workbooks before beginning...

 

[Jonmo1]

Yes, that would be easiest.
First open the book..
Workbooks.Open(MyPath & MyFile)

then the previous code shold work..using

Workbooks(MyFile).Sheets(.....

only the MyFile variable in there, not the path..


then when all is done, you can close the book
Workbooks(MyFile).Close

 

[jmthompson]

One more question for this macro-
The final macro will have steps for each of the 26 possible values of c and their corresponding sheets. All of the table-arrays for the vlookup will be in the same file. The macro will just cycle through all of the sheets.
Where is it most efficient to put the workbooks.Open and .close commands? As I have the code written now, it appears to open and close the file for every row in my original workbook. I will add the code to eliminate the screen flicker, but it would probably run a little faster if I could open the file once and close it once.

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))
    Workbooks.Open (MyPath & MyFile)
    If c = "12100 - The Grand Rapids Press" Then
        On Error Resume Next
        X = ""
        X = Workbooks(MyFile).Sheets("Grand Rapids").Name
        On Error GoTo 0
        If X = "Grand Rapids" Then
            LR = Workbooks(MyFile).Sheets("Grand Rapids").Cells(Rows.Count, "B").End(xlUp).Row
            X = Application.Match(c.Offset(0, 1).Value, Workbooks(MyFile).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
    Workbooks(MyFile).Close
Next c
End Sub

 

[Jonmo1]

Definately want that to be OUTSIDE the For Each C Loop...

Workbooks.open(MyPath & MyFile)
For Each c in...
    Do stuff
Next c
Workbooks(MyFile).Close

 

[jmthompson]

I tried that, but then it appeared that the macro was using the second workbook for the all parts of the macro. Do I need to modify my code to include the first workbook where applicable?

For Each c In Range([B50], Cells(Rows.Count, "B").End(xlUp))

I've added a line at the beginning of the code with the first workbook name

SourceFile = "WFO Daily Postage Log" & MyYear & ".update.xls"

Given my challenges with syntax, I don't know where to put it or even it it will fix the problem.

 

[Jonmo1]

Set Object Variables to
1 - Current Book and Sheet
2 - Book to Open

Try like this

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
Dim CurBook As Worksheet
Dim SrceBook As Workbook
Set CurBook = Workbooks("WFO Daily Postage Log" & MyYear & ".update.xls").Sheets(ActiveSheet.Name)
Set SrceBook = Workbooks.Open(MyPath & MyFile)
For Each c In CurBook.Range([B50], CurBook.Cells(Rows.Count, "B").End(xlUp))
    If c = "12100 - The Grand Rapids Press" Then
        On Error Resume Next
        X = ""
        X = SrceBook.Sheets("Grand Rapids").Name
        On Error GoTo 0
        If X = "Grand Rapids" Then
            LR = SrceBook.Sheets("Grand Rapids").Cells(Rows.Count, "B").End(xlUp).Row
            X = Application.Match(c.Offset(0, 1).Value, SrceBook.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
SrceBook.Close
End Sub

 

[jmthompson]

Boy, are you nice!

I ran the code and got error 1004 Method range of object_worksheet failed

For Each c In CurBook.Range([B50], CurBook.Cells(Rows.Count, "B").End(xlUp))

 

[Jonmo1]

Man, so close...I missed one spot..

Change
For Each c In CurBook.Range([B50], CurBook.Cells(Rows.Count, "B").End(xlUp))
to
For Each c In CurBook.Range(CurBook.[B50], CurBook.Cells(Rows.Count, "B").End(xlUp))

 

[jmthompson]

I am humbled by your greatness...truly. I promise to pay-it-forward in whatever capacity I can!