Problems with IF's
 

Hey Guys,

Thanks for your help in this, I always get confused when making thes
macros. I am trying to have a searchable spreadsheet. So I am usin
Autofilter, so based on some dropdown boxes, it returns some value, an
if that value is blank, I don't want it to filter that column. I kno
it is using Elseif, but I can't figure it out.

In addition, If U10 is =both, include both a long form and short for
of something, if its long, then include only long.

The Code:

Sub Macro3()
Range("s6:S10").Copy
Range("u6:u10").PasteSpecial (xlPasteValues)
Range("a5").Select
Selection.AutoFilter

If Range("u6").Value < 0 Then
Selection.AutoFilter Field:=1, Criteria1:=Range("u6").Value
End If

If Range("u7").Value < 0 Then
Selection.AutoFilter Field:=4, Criteria1:=Range("u7").Value
End If

If Range("u8").Value < 0 Then
Selection.AutoFilter Field:=5, Criteria1:=Range("u8").Value
End If

If Range("u9").Value < 0 Then
Selection.AutoFilter Field:=6, Criteria1:=Range("u9").Value
End If

If Range("u10").Value < 0 Then
Selection.AutoFilter Field:=1, Criteria1:=Range("u10").Value
End If


End Su

--
Message posted from http://www.ExcelForum.com

Problems with IF's
 

Sub Macro3()
Range("s6:S10").Copy
Range("u6:u10").PasteSpecial (xlPasteValues)
Range("a5").Select
Selection.AutoFilter
Set rng = ActiveSheet.AutoFilter.Range.Columns(1).Cells
If Range("u6").Value < 0 Then
Selection.AutoFilter Field:=1, Criteria1:=Range("u6").Value
If rng.SpecialCells(xlVisible).Count = 1 Then
Selection.AutoFilter Field:=1
End If
End If

If Range("u7").Value < 0 Then
Selection.AutoFilter Field:=4, Criteria1:=Range("u7").Value
If rng.SpecialCells(xlVisible).Count = 1 Then
Selection.AutoFilter Field:=4
End If
End If

If Range("u8").Value < 0 Then
Selection.AutoFilter Field:=5, Criteria1:=Range("u8").Value
If rng.SpecialCells(xlVisible).Count = 1 Then
Selection.AutoFilter Field:=5
End If
End If

If Range("u9").Value < 0 Then
Selection.AutoFilter Field:=6, Criteria1:=Range("u9").Value
If rng.SpecialCells(xlVisible).Count = 1 Then
Selection.AutoFilter Field:=6
End If
End If

If Range("u10").Value < 0 Then
Selection.AutoFilter Field:=1, Criteria1:=Range("u10").Value
If rng.SpecialCells(xlVisible).Count = 1 Then
Selection.AutoFilter Field:=1
End If
End If


End Sub

In addition, If U10 is =both, include both a long form and short form
of something, if its long, then include only long.

have no idea what your talking about.

--
Regards,
Tom Ogilvy


"trickdos " wrote in message
...
Hey Guys,

Thanks for your help in this, I always get confused when making these
macros. I am trying to have a searchable spreadsheet. So I am using
Autofilter, so based on some dropdown boxes, it returns some value, and
if that value is blank, I don't want it to filter that column. I know
it is using Elseif, but I can't figure it out.

In addition, If U10 is =both, include both a long form and short form
of something, if its long, then include only long.

The Code:

Sub Macro3()
Range("s6:S10").Copy
Range("u6:u10").PasteSpecial (xlPasteValues)
Range("a5").Select
Selection.AutoFilter

If Range("u6").Value < 0 Then
Selection.AutoFilter Field:=1, Criteria1:=Range("u6").Value
End If

If Range("u7").Value < 0 Then
Selection.AutoFilter Field:=4, Criteria1:=Range("u7").Value
End If

If Range("u8").Value < 0 Then
Selection.AutoFilter Field:=5, Criteria1:=Range("u8").Value
End If

If Range("u9").Value < 0 Then
Selection.AutoFilter Field:=6, Criteria1:=Range("u9").Value
End If

If Range("u10").Value < 0 Then
Selection.AutoFilter Field:=1, Criteria1:=Range("u10").Value
End If


End Sub


---
Message posted from http://www.ExcelForum.com/