Data Table Ranges as Dimensions

[tfortier]

I am trying to use VBA to activate a what-if analysis data table and to set up the row and column two factor variable. DT_MFG_Act - is a range name in the Workbook. intJSRow, intActCol, and intGACRow are all integers that represent a row or a column number. This worked yesterday when I originally wrote it but now I am getting the runtime error1004 Range of object global failed. The error occurs on the last line which is setting up the table. I believe it has something to do with my range names being variables. I'd really like to keep them as variables because the column number will vary based on the month we are in and the row number will change based on the worksheet that I call.


Can anyone help?? Thanks in advance.

' Select the range that will house the data table.
Range("DT_MFG_Act").Select

'set up the "result cell" in the data Table
ActiveCell.FormulaR1C1 = "=+MFG!R" & intTotalRowMFG & "C" & intTotalColAct

'Select the range again
Range("DT_MFG_Act").Select

'This is where the error happens
Selection.Table RowInput:=Range(Cells(intJSRow, intActCol)), ColumnInput:=Range(Cells(intGACRow, intActCol))

 

[StephenCrump]

Welcome to the Forum

You need Cells(), rather than Range(Cells()). You also don't need to use Select.

With Range("DT_MFG_Act")
    .Cells(1, 1).FormulaR1C1 = "=MFG!R" & intTotalRowMFG & "C" & intTotalColAct
    .Table RowInput:=Cells(intJSRow, intActCol), ColumnInput:=Cells(intGACRow, intActCol)
End With

I assume RowInput and ColumnInput are on the same worksheet as Range("DT_MFG_Act"), rather than on Worksheets("MFG")?

 

[tfortier]

Welcome to the Forum

You need Cells(), rather than Range(Cells()). You also don't need to use Select.

With Range("DT_MFG_Act")
    .Cells(1, 1).FormulaR1C1 = "=MFG!R" & intTotalRowMFG & "C" & intTotalColAct
    .Table RowInput:=Cells(intJSRow, intActCol), ColumnInput:=Cells(intGACRow, intActCol)
End With

I assume RowInput and ColumnInput are on the same worksheet as Range("DT_MFG_Act"), rather than on Worksheets("MFG")?

That worked great!! Thank you so much for the tip! You are amazing!

 

[StephenCrump]

Great, I'm glad it worked for you.