How to execute IF AND in a loop?

Question

I want to execute if and in a loop.
The code runs when not in a loop. (When I run the code one by one it gives the result I want).

I use ActiveCell as reference since I will use this code on another sheet with a different location.

Sub cobalagi()
Dim a, b As Range
Set a = ActiveCell.Offset(0, 3)
Set b = ActiveCell.Offset(0, 4)

For I = 1 To GetBaris
    If Left(a.Value, 8) = "KML/INV/" And b.Value = "Project - cost" Then
        ActiveCell.Value = "Inv"
    Else
        ActiveCell.Value = "Bukan Inv"
    End If
    ActiveCell.Offset(1, 0).Select
Next I
End Sub

The code for GetBaris.

Function GetBaris() As Long
    GetBaris = Range(ActiveCell.Offset(0, 1).Address, ActiveCell.Offset(0, 1).End(xlDown).Address).Rows.Count
End Function

The data and result of looping. First row is correct, but all the rest row should result in "Bukan Inv".

Instead of `For I = 1 To GetBaris`, could you try adding above the `for` this? `Dim vGetBaris as integer`. Under that, `vGetBaris = GetBaris()`. Then, change the `for` to `For I = 1 to vGetBaris` and see if that helps. — zedfoxus, May 22 '22 at 04:11
hello @zedfoxus thank you for responding, but it still don't work — Reynaldi Karundeng, May 22 '22 at 04:25
This likely won't make a difference in your issue, but just so you know... `Dim a, b As Range` is not doing what you think. It's defining `a` as a `variant` and `b` as a `Range`. What you want is `Dim a As Range, b as Range`. — pgSystemTester, May 22 '22 at 04:25
[Don't use select as part of your code.](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — pgSystemTester, May 22 '22 at 04:30
thank you for the information, I'm still new at vba. I see many people do that, and also this code runs well (if not in a loop) so i think it works.. but i'll literate more to know about this, thank you @pgSystemTester — Reynaldi Karundeng, May 22 '22 at 04:32

score 0 · Answer 1 · answered May 22 '22 at 04:40

I think this should solve your issues. If you have to use select then you can, but the sub runcobalagi should work. The reason you were having issues is because you kept checking against the first row, and while you're selection was checking, your offset a and b remained the same.

Try this...

Sub cobalagi()

   Call runcobalagi(AcvtiveCell)
   'or Call runcobalagi(Range("A4"))

End Sub


Private Sub runcobalagi(theStartCell As Range)
   Const a As Long = 3
   Const b As Long = 4
   Dim i As Long
   
   With theStartCell
      For i = 0 To GetBaris - 1
         If Left(.Offset(i, a).Value, 8) = "KML/INV/" And .Offset(i, b).Value = "Project - cost" Then
            
            .Offset(i, 0).Value = "INV"
          Else
            .Offset(i, 0).Value "Bukan Inv"
         End If
      
      Next i
   End With

End Sub

Note the correction as of 1 minute ago from this comment... – pgSystemTester May 22 '22 at 04:42 — pgSystemTester, May 22 '22 at 04:42

score 0 · Answer 2 · answered May 22 '22 at 05:45

Fill Column With `True` or `False` Depending on Two Conditions

Option Explicit

Sub Cobalagi()
    
    ' Source
    
    ' Column 1
    Const sCol1 As String = "D"
    Const sCrit1 As String = "KML/INV/" ' begins with
    ' Column 2
    Const sCol2 As String = "E"
    Const sCrit2 As String = "Project - cost" ' is equal
    
    ' Destination
    Const dCol As String = "A"
    Const dTrue As String = "Inv"
    Const dFalse As String = "Bukan Inv"
    
    ' Both
    Const fRow As Long = 4
    
    ' Reference the worksheet.
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    ' Calculate the last non-empty row in column 1.
    Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, sCol1).End(xlUp).Row
    If lRow < fRow Then Exit Sub ' no data in row range
    
    Dim sString As String
    Dim r As Long
    Dim IsMatch As Boolean
    
    ' Loop through the rows.
    For r = fRow To lRow
        
        ' Check.
        
        ' Column 1
        sString = CStr(ws.Cells(r, sCol1).Value)
        If InStr(1, sString, sCrit1, vbTextCompare) = 1 Then ' begins with
            
            ' Column 2
            sString = CStr(ws.Cells(r, sCol2).Value)
            If StrComp(sString, sCrit2, vbTextCompare) = 0 Then ' is equal
                
                IsMatch = True

            'Else ' is not equal; do nothing i.e. 'IsMatch = False'
            End If
        
        'Else ' doesn't begin with; do nothing
        End If
        
        ' Write result.
        If IsMatch Then
            ws.Cells(r, dCol).Value = dTrue
            IsMatch = False
        Else
            ws.Cells(r, dCol).Value = dFalse
        End If
    
    Next r
            
    ' Inform.
    MsgBox "Cobalagi is done.", vbInformation

End Sub

How to execute IF AND in a loop?

2 Answers2

Fill Column With True or False Depending on Two Conditions

Fill Column With `True` or `False` Depending on Two Conditions