Using combo box for VBA formula

[alion]

This is in continuation with my earlier question which was answered by John Davis (http://www.mrexcel.com/forum/showthread.php?p=2840777#post2840777). I am referring to the .Formula line in the code pasted below. In earlier version, we had B1>2,B1<=3 but now I am trying to link the values with combobox. I created two new variables (i dont know if they are called variables) low and high As Long and then I replaced 2 and 3 in with 'low' and 'high' so formula becomes B1>low,B1<=high. this change is giving type mismatch error in the second last line. Please help me out.


Sub Alion()
Sheets("Sheet1").Activate
Dim lr As Long
Dim lr2 As Long
Dim low As Long
Dim high As Long
low = ComboBox1
high = ComboBox2

lr = Cells(Rows.Count, 1).End(3).Row



With Range("C1:C" & lr)

.Formula = "=IF(AND(B1>2,B1<=3),A1,"""")"
.Value = .Value
.AutoFilter
.AutoFilter Field:=1, Criteria1:="<>"
.SpecialCells(xlCellTypeVisible).Copy Range("X1")
.AutoFilter

End With

lr2 = Cells(Rows.Count, 24).End(3).Row

MsgBox Join(WorksheetFunction.Transpose(Range("X1:X" & lr2)), vbLf)

End Sub

 

[alion]

Sorry admin for violating the rules. Please guys I need answer to this. Thanks alot.

 

[VoG]

That would be like this but you need to assign values to low and high before this

.Formula = "=IF(AND(B1>" & low & ",B1<=" & high & "),A1,"""")"

 

[alion]

Thanks for the reply VoG. I am assigning values to low and high using combo boxes. I have assigned this module to command button so when i click it, it stores values of combobox1 to low and combobox2 to high. the formula code comes afterwards so values are already assigned to 'high' and 'low' (if i am correct). I tried using your suggestion but it is giving me type mismatch error in the last line

"MsgBox Join(WorksheetFunction.Transpose(Range("X1:X" & lr2)), vbLf)"

 

[alion]

anyone?

 

[alion]

please guys.. I need this.. thanks alot

 

[shg]

What's the value of lr2 when the code fails?

What's in X1:X(lr2)?

 

[alion]

What's the value of lr2 when the code fails?

What's in X1:X(lr2)?

lr2 is zero when code fails because lr2 is counting non-empty rows. X1:X(lr2) contains variable names which satisfies the criteria. I understand why you are asking the question. If lr2 becomes zero then code will lead us nowhere. I think my problem is with the use of combo boxes. I have numbers inside but they are not being recognized by the .Formula property (i think)