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
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
