I am still a little groggy so if i missed something let me know.
Option Explicit
Sub Find1or3()
Dim icell As Integer, Count As Integer, icell2 As Integer
Dim x As String, y As String
'err handling incase Row 130:140 are full ie Row 140 has a value
If IsEmpty(ActiveSheet.Range("B140")) Then
'nothing
Else
x = MsgBox("Are you sure Range B130:B140 is empty?", vbYesNo)
If x = vbYes Then
GoTo 1
ElseIf x = vbNo Then
Exit Sub
End If
End If
1:
If IsEmpty(ActiveSheet.Range("B152")) Then
'nothing
Else
y = MsgBox("Are you sure Range B142:B152 is empty?", vbYesNo)
If y = vbYes Then
GoTo 2
ElseIf y = vbNo Then
Exit Sub
End If
End If
2:
'utilized counter so we could fill a specific range only
Count = 0
For icell = 4 To 25
If ActiveSheet.Range("I" & icell).Value = "3" Then
ActiveSheet.Range("B" & icell).Copy
ActiveSheet.Range("B130").Offset(Count, 0).PasteSpecial xlPasteAll
Count = Count + 1
End If
Next icell
'used multiple loops cause counter gets a little messy with multiple if statements
Count = 0
For icell2 = 4 To 25
If ActiveSheet.Range("I" & icell2).Value = "1" Then
ActiveSheet.Range("B" & icell2).Copy
ActiveSheet.Range("B142").Offset(Count, 0).PasteSpecial xlPasteAll
Count = Count + 1
End If
Next icell2
End Sub
Bookmarks