CODEWITHSUNDEEP
vbaexcel
M_S_SAJJAN
M_S_SAJJAN

Reputation: 167

How do I use a variable in FormulaR1C1?

I have a formula to get the closest value to given value i.e., 

=SMALL(R1C1:R1C144,COUNTIF(R1C1:R1C144,""<"" &8)+1 )

will give the closest value to 8.

I want to use variable instead of direct number "(8)"

Please help me. This is my current code

Private Sub CommandButton1_Click() 
Dim col As Integer 
col = Me.TextBox1.Value
ActiveCell.FormulaR1C1 = "=SMALL($A$1:$EN$1,COUNTIF($A$1:$EN$1," < "&col)+1)"
End Sub

Upvotes: 1

Views: 3623

Answers (2)

Tim Williams
Tim Williams

Reputation: 166351

Private Sub CommandButton1_Click() 
Dim col As Integer 
col = Me.TextBox1.Value
 'EDIT: .FormulaR1C1 should be .Formula   
ActiveCell.Formula = "=SMALL($A$1:$EN$1,COUNTIF($A$1:$EN$1,""<" & col & """)+1)"
End Sub

Upvotes: 1

Mike Kellogg
Mike Kellogg

Reputation: 1178

If you are wanting to do this in excel seperate from VBA then you don't really need a variable. You should just put any value you want in a separate cell (in this example cell A5) and then reference that cell in you formula like this:

=SMALL(R1C1:R1C144,COUNTIF(R1C1:R1C144,""<"" & A5)+1 )

I don't believe you'd want a VBA solution since your algorithm so far is all in Excel syntax. However if you did want to do it in VBA you'd need to find the algorithm in VBA syntax and declare a variable like this:

Dim myNumber As Integer

myNumber = 5          'put whatever number you want here

Upvotes: 0

Related Questions