PDA

View Full Version : Putting a Variable into a formula



simora
04-16-2015, 08:12 PM
I have a formula in Excel VBA code



.Range("BU2").Resize(SumRow - 1).Formula = "=SUMIF(B:B,BT2,U:U)"

that Sums values when I reference a specific column. ( Column U ) in this case

Now, I want to dynamically select a column and have that formula applied to the selected column.

This is how I'm finding the column.



With Sheets("Sheet1")
Set Col = Range("H1:S1")

With Col.Find(ComboBox1, LookAt:=xlWhole)
rsCol = .Column
'MsgBox rsCol

End With
End With



How do I get the value of rsCol ( The column Letter ) into this formula ?


= "=SUMIF(B:B,BT2,U:U)"

This gives me the column letter



ColNo = rsCol
fCol = Split(Cells(, ColNo).Address, "$")(1)

But I cant get this bit to work


.Range("CU2").Resize(SumRow - 1).Formula = "=SUMIF(B:B,CT2,'fCol:fCol')"


Thanks

mancubus
04-16-2015, 10:42 PM
you dont need so many variables.



Dim fCol As String

fCol = Split(Cells(, Range("H1:S1").Find(ComboBox1, LookAt:=xlWhole).Column).Address, "$")(1)

Range("CU2").Resize(SumRow - 1).Formula = "=SUMIF(B:B,CT2," & fCol & ":" & fCol & ")"

simora
04-17-2015, 09:11 PM
Thanks mancubus:
Your reply solved the problem.
Your code was much more elegant & precise than what I had attempted.