How do I use VBA defined variables in a function?

[himperson1]

i've defined a variable in vba and am now trying to get a function to call it. the value assigned to the variable is correctly being defined when i mouse over it, but i'm getting an error when the macro tries to run the function part of the code.

This is the array formula that i'm trying to emulate: {=MATCH(1,(B1:CP1=A7)*(B2:CP2=B7),0)}

Dim myDay As Variant
    Dim myShift As Variant
    myDay = InputBox("Enter day of the month")
    myShift = InputBox("Enter your shift")


    Locate = Application.WorksheetFunction.Match(1, (Sheets("Trend").Range("B1:CP1") = myDay) * (Sheets("Trend").Range("B2:CP2") = myShift), 0)


'    Worksheets("Inventory").Range("B12").Copy Worksheets("Trend").Range("location based on index/match").Offset(1,0)
'    Worksheets("Inventory").Range("B27").Copy Worksheets("Trend").Range("Location based on index/match").Offset(1,-1)
'    Worksheets("Inventory").Range("B36").Copy Worksheets("Trend").Range("Location based on index/match").Offset(1,-2)

 

[Marcelo Branco]

In VBA you should use Evaluate to get results of array formulas

Something like this
Assumes myDay is numeric and myShift is text

Locate = Sheets("Trend").Evaluate("=MATCH(1,(B1:CP1=" & myDay & ")*(B2:CP2=" & Chr(34) & myShift & Chr(34) & "),0)")

Hope this helps

M.

 

[himperson1]

It worked great. Thanks.

Now I'm trying to get the last part of the code to work using your advice:

Worksheets("Inventory").Range("B12").Copy Worksheets("Trend").Range(Cells(" & Locate & ", 3)).Offset(1, 0)

Since locate gives me the column number w/in the array that fulfills the match statement, i'm trying to use that column number and 3 for row to paste the data from the "Inventory" page. Then offset by 1 column due to headings in A.

Getting error 13 type mismatch.

 

[Jonmo1]

Cells would be used 'instead of' Range
And it's syntax is Cells(Row#, Column# OR ColumnLetter)

Try

Worksheets("Inventory").Range("B12").Copy Worksheets("Trend").Cells(3, Locate).Offset(1, 0)


Also, it seems like extra work to go to row 3 then offset 1 row.
Just go straight to row 4 to begin with

Worksheets("Inventory").Range("B12").Copy Worksheets("Trend").Cells(4, Locate)

 

[Rick Rothstein]

It worked great. Thanks.

Now I'm trying to get the last part of the code to work using your advice:

Worksheets("Inventory").Range("B12").Copy Worksheets("Trend").Range(Cells(" & Locate & ", 3)).Offset(1, 0)

Since locate gives me the column number w/in the array that fulfills the match statement, i'm trying to use that column number and 3 for row to paste the data from the "Inventory" page. Then offset by 1 column due to headings in A.

Getting error 13 type mismatch.

If Locate is a variable, then you don't need to quote or concatenate it into the Cells object's argument list, just use it directly...

Worksheets("Inventory").Range("B12").Copy Worksheets("Trend").Range(Cells(Locate, 3)).Offset(1, 0)

 

[himperson1]

Excellent. Thank you all.
For posterity, my final code looked like the following:

    Dim myDay As Variant    Dim myShift As Variant
    myDay = InputBox("What day is today?", "Enter day of the month")
    myShift = InputBox("What shift is this for?", "Enter shift")
    
    Locate = Sheets("Trend").Evaluate("=MATCH(1,(B1:CP1=" & myDay & ")*(B2:CP2=" & myShift & "),0)")
    
    Worksheets("Inventory").Range("B12").Copy
    Worksheets("Trend").Cells(3, Locate + 1).PasteSpecial xlPasteValues
    Worksheets("Inventory").Range("B27").Copy
    Worksheets("Trend").Cells(4, Locate + 1).PasteSpecial xlPasteValues
    Worksheets("Inventory").Range("B36").Copy
    Worksheets("Trend").Cells(5, Locate + 1).PasteSpecial xlPasteValues