Debug my Vlookup code? run time error 1004

jmthompson

Well-known Member
Joined
Mar 31, 2008
Messages
966
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), [MyPath & MyFile]Grand Rapids!(B5:D23), 3, False)"
Next c
End Sub

Run-Time error 1004 Application defined or object defined error

This is highlighted
Code:
c.Offset(0, 4).FormulaR1C1 = "=VLookup(c.Offset(0, 1), [MyPath & MyFile]Grand Rapids!(B5:D23), 3, False)"

I am fairly certain it is the table array reference that isn't done correctly, but my attempts to fix it haven't worked.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Try

c.Offset(0, 4).FormulaR1C1 = "=VLookup(" & c.Offset(0, 1).address & ", [" & MyPath & MyFile & "]Grand Rapids!(B5:D23), 3, False)"
 
Upvote 0
It's always hard getting right syntax for path / filename / sheet

this should be it though..

c.Offset(0, 4).FormulaR1C1 = "=VLookup(" & c.Offset(0, 1).address & ", '" & MyPath & "[" & MyFile & "]Grand Rapids'!(B5:D23), 3, False)"
 
Upvote 0
Still coming through with the 1004 error, same segment highlighted.

This is what the file names would look like based on my definitions at the start of the macro

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

Do I really want the path separated from the file name? If yes, do I need to eliminate the trailing \?

Do I even need the path?
 
Upvote 0
I am still having difficulty getting this code right.

My current version:
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

The problematic portion:
Code:
c.Offset(0, 4).Formula = "=VLOOKUP(" & c.Offset(0, 1).Address & ", '[" & MyPath & MyFile & "]Grand Rapids'!(B5:D23), 3, False)"

I've tried to enter my file names in a multitide of ways, I am not sure that is the problem. Could there be something wrong with the offset?


This will be a first step in a larger macro which is intended to look for a value in column "B", when that value is found, run a vlookup on the stated sheet in another workbook, the search criteria is found in column C of the first workbook, matched against column B in the second workbook and returns the value in column D of the second workbook into column F of the first workbook.

I will include steps for 26 values in column B with their corresponding sheets. Once I get the Vlookup code worked out, I will then need to deal with the fact that it is likely that the second workbook will not have all 26 worksheets all of the time. I am hoping that adding On Error Resume Next to the code will help, but I haven't tried that.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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