CODEWITHSUNDEEP
excelvba
Wolverine
Wolverine

Reputation: 13

How to loop rows in groups of three?

I have 3 fields for one sample.

I can make the macro display the results for one set of three samples.

How do I loop the next 3 samples below, and so forth?

Screenshot

Sub Data()    
    
    If Range("H33").Value <= 40 And Range("H34").Value > 42 And Range("H35").Value > 42 Then
         Range("J33").Value = "Present"
         Range("K33").Value = "SARS-CoV-2 DETECTED"
     Else:
         Range("K33").Value = "SARS-CoV-2 not detected"
         Range("J33").Value = "Absent"
        
    End If

End Sub

Upvotes: 1

Views: 97

Answers (3)

Bee_Riii
Bee_Riii

Reputation: 1039

Personally I think this would be better done as a formula.

In cell J33

=IF(G33="Sample",IF(AND(H33<=40,H34>42,H35>42),"Present","Absent"),"")

In Cell K33

=IF(G33="Sample",IF(AND(H33<=40,H34>42,H35>42),"SARS-CoV-2 DETECTED","SARS-CoV-2 not detected"),"")

You can copy this down and it will only show a value on those rows with Sample. You can take that bit out if you like.

Speaking as someone who basically learned VBA before really getting into the functions available to excel I found myself often trying to create solutions in VBA that were much simpler using excel functions, or at least much faster. So if you haven't already done so I suggest doing some research on what excel functions can actually do for you. If you're regularly using excel you'll be amazed at what you can do out of the box without VBA.

Upvotes: 0

FaneDuru
FaneDuru

Reputation: 42236

Please, try the next code. It uses arrays, works only in memory and will be very fast in case of a big range to be processed. It works independent of the string 'Sample', which probably will be something else (variable, too):

Sub DataCovidResults()
 Dim sh As Worksheet, lastR As Long, arrH, arrFin, i As Long

 Set sh = ActiveSheet 'use here the sheet you need
 lastR = sh.Range("H" & sh.Rows.count).End(xlUp).row
 arrH = sh.Range("H2:H" & lastR).value    'put the H:H range in an array
 ReDim arrFin(1 To UBound(arrH), 1 To 2)  'redim the final array (to collect the result)

 For i = 1 To UBound(arrH) Step 3
    If arrH(i, 1) <= 40 And arrH(i + 1, 1) > 42 And arrH(i + 1, 1) > 42 Then
        arrFin(i, 1) = "Present": arrFin(i, 2) = "SARS-CoV-2 DETECTED"
    Else
        arrFin(i, 1) = "Absent": arrFin(i, 2) = "SARS-CoV-2 not detected"
    End If
 Next i
 'drop the processing result array at once:
 sh.Range("I2").Resize(UBound(arrFin), UBound(arrFin, 2)).value = arrFin
End Sub

Upvotes: 1

Simon
Simon

Reputation: 1375

You could do something like this to keep it simple:

Sub Data()

Dim cell As Range, i As Long

For Each cell In Range("G1:G20") 'Your column of the sample names
    If cell.Value = "Sample" Then 'If equals sample. Otherwise you could do: If cell.Value <> "" Then
        i = cell.Row
        If Range("H" & i).Value <= 40 And Range("H" & i + 1).Value > 42 And Range("H" & i + 2).Value > 42 Then
            Range("J" & i).Value = "Present"
            Range("K" & i).Value = "SARS-CoV-2 DETECTED"
        Else
            Range("K" & i).Value = "SARS-CoV-2 not detected"
            Range("J" & i).Value = "Absent"
        End If
    End If
Next

End Sub

Upvotes: 1

Related Questions