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!

 

[jmthompson]

Wait- I think I figured it out, I don't have cases for all of the possible values...

Yep! All fixed!

Sorry!!!