VBA Vlookup to copy and paste

[Callandor]

Good morning. I currently have 3 sheets. Sheet3 B40 = Lookup_value. Sheet 2 F4:H34 = Table_array. 3 = Col_index_num, and 0 = Range_lookup. Sheet1 J14 = the value I want pasted. Here is my question, issue. I want the date from Sheet3 to Vlookup the correct cell from Sheet2. I then want the value from Sheet1 J14 copy and pasted into the Vlookup cell. Below is what I was working on, however, I can't seem to get it to work. Once I can make this work, I want to be able to control this with a button that will post the value, and not the formula that is in J14. I want the information to be static. Help please.

Sub test()

Application.WorksheetFunction.VLookup(Worksheets("Sheet3").Range("B40"), Worksheets("Sheet2").Range("F4:H34"), 3, 0) = Worksheets("Sheet1").Range("J14").Value

End Sub

[Olly]

Try:

Please Login or Register  to view this content.

[Callandor]

Olly,

I am getting the following error:

Run-time error'424':
Object Required

[Olly]

Olly,

I am getting the following error:

Run-time error'424':
Object Required

Which suggests you haven't tweaked that code to match your worksheet codenames...

Might be easier if you attached your workbook.

[Callandor]

Olly,

I got it work. Thanks for that. It actually helped me in another issue I was having. The problem with that sub you wrote me is that it doesn't do what I need it to. Unfortunately, I cannot upload the workbook. I am going to try and explain it again though, so hopefully that will be enough.

A=sheet3.Range("B40"), which is formulated as =Today()-1, or yesterday's date.
B=sheet2.Range("F4:F34"), which has all of the days of the month.
C=sheet1.Range("J14"). This is the cell I want to copy from.
D=This is the cell I want to paste the information to, which is located in column D, or two columns from the day of the month.

I want A to find the matching day in B. Once they are a match, I want to copy C into D. Since D isn't fixed, I need it to depend entirely on A, which will change as the days change.

I understand if you cannot help due to me being unable to upload the workbook. You have my thanks either way.

[Olly]

To clarify - you want to paste the value of 'C' (Sheet1.Range("J14")) into Sheet 2, Column D, in the row where the value of Sheet2 Column F matches Sheet3.Range("B40").

If that's the requirement, then try:

Please Login or Register  to view this content.

[Callandor]

Olly,

The sub is working and not working. My days of the month are controlled using formulas. When I run it, it gives me a runtime "91" error. When I write out the days of the month, instead of using the formula, the sub works. Is the .find method trying to find the code instead of the value? Thanks.

[Olly]

This would be much easier if you were to attach a sample workbook...

[Callandor]

Hopefully this is going to post a sample workbook.

[Olly]

Okay, change to:

Please Login or Register  to view this content.

See the extra statement, highlighted in red

[Callandor]

Works great. Thanks for all of your help.