VBA Selecting all Rows with specific values in multiple columns

[arathore93]

Hello I am fairly new to macros but would love to be apart of the community.

I have a table with 1000 rows and 25 columns A through Y. I am trying to create a macro that will select ALL rows in the table which MUST contain a "0" in the "I" column AND a "2" in the "R" column.

I would then like to get the data from the NEXT ROW UP from the selected rows in the first part of the macro. IF the nextValue in columns "I" is ABOVE 0, sum up how many times and if the value is BELOW 0, sum up how many times.

This is what I have so far:

Sub newCode()
Dim i As Long: i = 1
nextValue As Integer
Dim rng As Range


Do While Range("I" & i).Value = 0 And Range("R" & i).Value = 2
i = i + 1


Loop
Range("I1:I" And "R1:R" & i - 1).EntireRow.End(xlUp).Select
'getting mismatch error

Next
nextValue = rng.Offset(1, 0).Value
If nextValue > 0 Then
'Need help here
ElseIf nextValue < 0 Then
'Need help here
Else



End Sub

 

[arathore93]

So this is what I have compiled so far. But am getting a mismatch type error.

Sub MLBmacrosSO2()

Dim nextValue As Integer

Dim row As Range
Dim sel As Range

Dim countPos As Integer
Dim countNeg As Integer

For i = 1 To 1000
If Range("I" & i).Value = 0 And Range("R" & i).Value = 2 Then
Set row = Range("A" & i).EntireRow
If sel Is Nothing Then
Set sel = row
Else
Set sel = Application.Union(sel, row)
End If
sel.Select

nextValue = Range("I" & i + 1).Value

If nextValue > 0 Then
countPos = countPos + 1
ElseIf nextValue < 0 Then
countNeg = countNeg + 1
End If

End If
Next

Debug.Print "countPos", countPos
Debug.Print "countNeg", countNeg
End Sub

 

[Z51]

Perhaps...

Sub arathore93()
Dim i As Long, countPos As Long, countNeg As Long
countPos = 0
countNeg = 0

For i = 1 To 1000
    With Cells(i, "I")
        If .Value = 0 And Cells(i, "R").Value = 2 Then
            If .Offset(-1, 0).Value < 0 Then
                countNeg = countNeg + 1
            ElseIf .Offset(-1, 0).Value > 0 Then
                countPos = countPos + 1
            Else
                'It's zero, do nothing
            End If
        End If
    End With
Next i
   
MsgBox "Values above zero:  " & countPos & vbCrLf & "Values below zero:  " & countNeg
End Sub

Note: my code uses the "NEXT ROW UP" (-1) as you asked for. In other words, the value in column I from the 'next row up' from the row that has i=0 and r=2. If you actually meant next row DOWN, change the Offset's to (1,0) instead of (-1,0).

 

[arathore93]

@

Perhaps...

Sub arathore93()
Dim i As Long, countPos As Long, countNeg As Long
countPos = 0
countNeg = 0

For i = 1 To 1000
    With Cells(i, "I")
        If .Value = 0 And Cells(i, "R").Value = 2 Then
          [B]  If .Offset(-1, 0).Value < 0 Then[/B]
                countNeg = countNeg + 1
            ElseIf .Offset(-1, 0).Value > 0 Then
                countPos = countPos + 1
            Else
                'It's zero, do nothing
            End If
        End If
    End With
Next i
  
MsgBox "Values above zero:  " & countPos & vbCrLf & "Values below zero:  " & countNeg
End Sub

Note: my code uses the "NEXT ROW UP" (-1) as you asked for. In other words, the value in column I from the 'next row up' from the row that has i=0 and r=2. If you actually meant next row DOWN, change the Offset's to (1,0) instead of (-1,0).

Thanks for the swift reply. I ran it and it was working then the second time I ran it it gave me a runtime error 1004 application defined or object defined error and highlighted the line I bolded. Also what does the vbCrLf mean? Thanks

 

[arathore93]

@Z51
If .Offset(-1, 0).Value < 0 Then
Thanks for the swift reply. I ran it and it was working then the second time I ran it it gave me a runtime error 1004 application defined or object defined error and highlighted the line I bolded. Also what does the vbCrLf mean? Idk why the code looks good.

 

[Z51]

vbCrLf = Visual Basic Carriage Return Line Feed

It's essentially like pressing the Enter key, based on old word processor (and probably typewriter) terminology. See here for a bit more detail: Differences Between vbLf, vbCrLf & vbCr Constants