Including Worksheet Function into .Range

useriQFl1 0
Viewed 32

I am trying to replace a simple "copy/paste" portion in my macro. The updated code currently looks like this:

Sub Daily()

Dim X As Long
X = WorksheetFunction.Lookup(range("Reported_day"), Worksheets("Averages").Rows("3:3"), Worksheets("Averages").Rows("2:2"))

Sheets("Cost").Range("Tons").Value = Sheets("Averages").Range("4, X").Value

The above lines are to replace:

X = WorksheetFunction.Lookup(Range("Reported_day"), Worksheets("Daily Avgs (year)").Rows("3:3"), Worksheets("Avgerages").Rows("2:2"))
Sheets("Cost").Range("Tons").Copy
Sheets("Averages").Cells(4, X).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

The specific portion of the script returning the 1004 error is the .Range("4, X").Value.

Is there a way I can include the Worksheet Function into the range? The 1004 Error is an "Application defined or object defined error." I feel like an Array would solve my problem, but I am not sure. There is probably something simple I am missing, as I am quite new to VBA. Thank you in advance.

vba excel
1 Answers

Updated script to include the following lines:

    Dim X As Integer
X = WorksheetFunction.Lookup(Range("Reported_day"), Worksheets("Averages ").Rows("3:3"), Worksheets("Averages").Rows("2:2"))

Sheets("Averages").Cells(4, X).Resize(Sheets(" Cost").Range("Tons").Rows.Count).Value = Sheets("Cost").Range("Tons").Value

With some editing to the Excel spreadsheet itself, I was able to get the cells to properly update. This significantly sped up the macro. Thank you for those who commented, your input was extremely helpful.

useriQFl10
Related