CODEWITHSUNDEEP
excelif-statementfor-loopexcel-2013vba
Tarik
Tarik

Reputation: 77

if-statement in a for-loop with a counter

I have the following situation I can't figure it out. Is there a way to get a counter in a if loop without overwriting itself.

I have this code:

lastrow = Sheets("sheetx").Cells.SpecialCells(xlCellTypeLastCell).Row

For a = 1 To lastrow
    If Sheets("sheetx").Cells(a, 1).Value = "aa" Then
        Sheets("sheetx").Cells(a, 2).Value = "zz"
    End If
    If Sheets("sheetx").Cells(a, 1).Value = "bb" Then
        Sheets("sheetx").Cells(a, 2).Value = "yy"
    End If
    If Sheets("sheetx").Cells(a, 1).Value = "cc" Then
        Sheets("sheetx").Cells(a, 2).Value = "zz"
    End If
    If Sheets("sheetx").Cells(a, 1).Value = "dd" Then
        Sheets("sheetx").Cells(a, 2).Value = "ww"
    End If
Next a

picture I get - picture I want to get

the data in the left is what I get. But I want to have the data in the right. When the if-statement of "aa" assigns more then 8 times a "zz" then it has to stop (see image right) assigning "zz". Is there an easy way to fix this?

Upvotes: 0

Views: 1245

Answers (1)

djikay
djikay

Reputation: 10628

You can keep a separate counter to count how many times "aa" has been replaced. Something like this:

Dim aaCounter As Integer

lastrow = Sheets("sheetx").Cells.SpecialCells(xlCellTypeLastCell).Row

aaCounter = 1
For a = 1 To lastrow
    If Sheets("sheetx").Cells(a, 1).Value = "aa" Then
        If aaCounter <= 8 Then
          Sheets("sheetx").Cells(a, 2).Value = "zz"
          aaCounter = aaCounter + 1
        End If
    End If
    If Sheets("sheetx").Cells(a, 1).Value = "bb" Then
        Sheets("sheetx").Cells(a, 2).Value = "yy"
    End If
    If Sheets("sheetx").Cells(a, 1).Value = "cc" Then
        Sheets("sheetx").Cells(a, 2).Value = "zz"
    End If
    If Sheets("sheetx").Cells(a, 1).Value = "dd" Then
        Sheets("sheetx").Cells(a, 2).Value = "ww"
    End If
Next a

You can do similar for the other values if you wish.

Upvotes: 1

Related Questions