VBA How to include a range that the user inputs into a cell formula!

[KRossVD]

I'm trying to have a formula reference certain cells and sheets. Because the sheet and which column it reads from can change, I need to be able to have the user select the range from an input box and then have those ranges print in the cell formula, but I can't figure it out! Here is the relevant part of my code:

Dim TableLabel As Range
Dim RowLabel As Range
Dim ColLabel As Range

Set TableLabel = Application.InputBox("Entire Data Table", Type:=8)
Set RowLabel = Application.InputBox("Select Horizontal Labels", Type:=8)
Set ColLabel = Application.InputBox("Select Vertical Labels", Type:=8)

Dim TableLabel2 As String
Dim RowLabel2 As String
Dim ColLabel2 As String
TableLabel2 = TableLabel
RowLabel2 = RowLabel
ColLabel2 = ColLabel


ActiveCell.Formula = "'=(INDEX(" & TabelLabel2 & ", MATCH($A3," & RowLabel2 & ", 0), MATCH(B$2," & ColLabel2 & ", 0))"

 

[scott_n_phnx]

For your range names, in the formula, add <b>.Value</b> to each.

 

[Momentman]

ActiveCell.Formula = "'=(INDEX(" & TabelLabel2 & ", MATCH($A3," & RowLabel2 & ", 0), MATCH(B$2," & ColLabel2 & ", 0))"

Haven't looked through the code thoroughly but i can see an error here
ActiveCell.Formula = "'=(INDEX(" & TabelLabel2 & ", MATCH($A3," & RowLabel2 & ", 0), MATCH(B$2," & ColLabel2 & ", 0))"

The "Table" is spelt as "Tabel"

 

[KRossVD]

For your range names, in the formula, add .Value to each.

Where Am I supposed to add.value exactly? Nothing seems to be working...

 

[Rick Rothstein]

ActiveCell.Formula = "'=(INDEX(" & TabelLabel2 & ", MATCH($A3," & RowLabel2 & ", 0), MATCH(B$2," & ColLabel2 & ", 0))"

You declared your variables as Ranges... since you are constructing a formula from them, you would need to supply the addresses of the ranges, using absolute referencing I would guess, to the formula. Try it this way...

ActiveCell.Formula = "'=(INDEX(" & TabelLabel2.Address & ", MATCH($A3," & RowLabel2.Address & ", 0), MATCH(B$2," & ColLabel2.Address & ", 0))"

 

[Momentman]

Your code should look like this

Sub formuladtins()
    Dim TableLabel As Range
    Dim RowLabel As Range
    Dim ColLabel As Range
    Dim TableLabel2 As String
    Dim RowLabel2 As String
    Dim ColLabel2 As String
    
    Set TableLabel = application.InputBox("Entire Data Table", Type:=8)
    Set RowLabel = application.InputBox("Select Horizontal Labels", Type:=8)
    Set ColLabel = application.InputBox("Select Vertical Labels", Type:=8)
    
    
    TableLabel2 = TableLabel.Address
    RowLabel2 = RowLabel.Address
    ColLabel2 = ColLabel.Address
    
    ActiveCell.Formula = "=INDEX(" & TableLabel2 & ", MATCH($A3," & RowLabel2 & ", 0), MATCH(B$2," & ColLabel2 & ", 0))"
End Sub

I see Mr. Rothstein had already posted a solution
1. Just edit the "Tabelabel" to "Tablelabel" in Mr. rick's one liner
2. There's a single quote just after the double quote starting the index formula, don't see what its doing there asides from throwing up an error ["'=INDEX("]

 

[KRossVD]

Your code should look like this

Sub formuladtins()
    Dim TableLabel As Range
    Dim RowLabel As Range
    Dim ColLabel As Range
    Dim TableLabel2 As String
    Dim RowLabel2 As String
    Dim ColLabel2 As String
    
    Set TableLabel = application.InputBox("Entire Data Table", Type:=8)
    Set RowLabel = application.InputBox("Select Horizontal Labels", Type:=8)
    Set ColLabel = application.InputBox("Select Vertical Labels", Type:=8)
    
    
    TableLabel2 = TableLabel.Address
    RowLabel2 = RowLabel.Address
    ColLabel2 = ColLabel.Address
    
    ActiveCell.Formula = "=INDEX(" & TableLabel2 & ", MATCH($A3," & RowLabel2 & ", 0), MATCH(B$2," & ColLabel2 & ", 0))"
End Sub

This works almost perfectly, except that it doesn't include the sheet name in the address. Is there a way to do this? The formula is being put into a different workbook than the table is in so I need the workbook and worksheet name to precede the raneg address if possible

 

[KRossVD]

This works almost perfectly, except that it doesn't include the sheet name in the address. Is there a way to do this? The formula is being put into a different workbook than the table is in so I need the workbook and worksheet name to precede the raneg address if possible

I figured it out. I just had to add (External:=True) right after address. Thank you guys so much for all the help, you've been a real life saver!