Re-post: Using Combobox input in formula

[alion]

I am posting this thread again, guys please help me out.

--------------------
This is in continuation with my earlier question which was answered by John Davis (http://www.mrexcel.com/forum/showthr...77#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

 

[VoG]

Duplicate of http://www.mrexcel.com/forum/showthread.php?t=574977

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: http://www.mrexcel.com/forum/showthread.php?t=99490).