VBA: just one loop instead of one for any check

[Nelson78]

Hello everybody.

I have a macro that produces the right result.
The aspect to improuve is in making it quicker: as you can see in this small part, I have a lot of checks to perform in any single cell.
I suppose that checking everything in a single loop could reduce in a significant way the execution time.

Any suggestion?

'35
For Each cell35 In Sheets("record").Range("BL2:BN" & lrt)
    If Left(cell35.Offset(0, -6).Value, 2) = "35" Then cell35.Offset.Value = "35" & " " & cell35.Offset(0, 3).Value
Next

'39
For Each cell39 In Sheets("record").Range("BL2:BN" & lrt)
    If Left(cell39.Offset(0, -6).Value, 2) = "39" Then cell39.Offset.Value = "39" & " " & cell39.Offset(0, 3).Value
Next cell39

'321
For Each cell321 In Sheets("record").Range("BL2:BN" & lrt)
    If Left(cell321.Offset(0, -6).Value, 3) = "321" Then cell321.Offset.Value = "321" & " " & cell321.Offset(0, 3).Value
Next cell321

'322
For Each cell322 In Sheets("record").Range("BL2:BN" & lrt)
    If Left(cell322.Offset(0, -6).Value, 3) = "322" Then cell322.Offset.Value = "322" & " " & cell322.Offset(0, 3).Value
Next cell322

'323
For Each cell323 In Sheets("record").Range("BL2:BN" & lrt)
    If Left(cell323.Offset(0, -6).Value, 3) = "323" Then cell323.Offset.Value = "323" & " " & cell323.Offset(0, 3).Value
Next cell323

'324
For Each cell324 In Sheets("record").Range("BL2:BN" & lrt)
    If Left(cell324.Offset(0, -6).Value, 3) = "324" Then cell324.Offset.Value = "324" & " " & cell324.Offset(0, 3).Value
Next cell324

 

[Fluff]

You can do that like

For Each Cell In Sheets("record").Range("BL2:BN" & lrt)
    If Left(Cell.Offset(0, -6).Value, 2) = "35" Then
      Cell.Value = "35" & " " & Cell.Offset(0, 3).Value
   ElseIf Left(Cell.Offset(0, -6).Value, 2) = "39" Then
      Cell.Value = "39" & " " & Cell.Offset(0, 3).Value
   ElseIf Left(Cell.Offset(0, -6).Value, 2) = "321" Then
      Cell.Value = "321" & " " & Cell.Offset(0, 3).Value
   End If
Next Cell

Just add the other requirements

 

[Nelson78]

You can do that like

For Each Cell In Sheets("record").Range("BL2:BN" & lrt)
    If Left(Cell.Offset(0, -6).Value, 2) = "35" Then
      Cell.Value = "35" & " " & Cell.Offset(0, 3).Value
   ElseIf Left(Cell.Offset(0, -6).Value, 2) = "39" Then
      Cell.Value = "39" & " " & Cell.Offset(0, 3).Value
   ElseIf Left(Cell.Offset(0, -6).Value, 2) = "321" Then
      Cell.Value = "321" & " " & Cell.Offset(0, 3).Value
   End If
Next Cell

Just add the other requirements

Fine, thank's you.

 

[Fluff]

You're welcome