Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: IF statement in VBA

  1. #1
    Board Regular wrightyrx7's Avatar
    Join Date
    Sep 2011
    Location
    Manchester, UK
    Posts
    994
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default IF statement in VBA

    HI all,

    I have the following code but do not know how to put the formula in:-

    The formula i want to use is:
    Code:
    =IF(A3=A2,IF(B3=B2,G2+1,1),1)
    my attempt at putting it into VBA is:
    Code:
    Sub test()    Dim lastRow As Integer
        Dim rngSeq As Range
        Dim i As Range
        
        lastRow = Range("A3").End(xlDown).Row
    
    
        With ActiveSheet
            Set rngSeq = Range("M3:M" & lastRow)
    
    
            
                For Each i In rngSeq.Cells
                    i.Formula = "=if(A" & .Row = A" & .Row - 1,
                    
    
    
                Next i
        End With
    
    
    End Sub
    Please help
    Definitely not a PRO when it comes to VBA/Formula writing, but find it interesting.

    My practice is trying to help people on the forums so please be patient if I mess up :P

    A bit of feedback would be nice

  2. #2
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF statement in VBA

    Hi,

    Perhaps try it something like this:

    Code:
    Sub test()
        
        Dim lastRow As Long
        
        With ActiveSheet
            lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            .Range("M3:M" & lastRow).Formula = _
                "=IF(A3 = A2, IF(B3 = B2, G2 + 1, 1), 1)"
        End With
    
    End Sub

  3. #3
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF statement in VBA

    Try
    Code:
    Sub test()
    Dim lastRow As Long, rngSeq As Range
    lastRow = Range("A3").End(xlDown).Row
    Set rngSeq = Range("M3:M" & lastRow)
    rngSeq.Formula = "=IF(A3=A2,IF(B3=B2,G2+1,1),1)"
    End Sub
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  4. #4
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,137
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    3 Thread(s)

    Default Re: IF statement in VBA

    Some suggestions:
    Code:
    Sub test2()
    Dim lRow As Long, i As Long
    Application.ScreenUpdating = False    
        With ActiveSheet
            lRow = .Range("A" & Rows.Count).End(xlUp).row
            For i = 3 To lRow
                .Range("M" & i).Formula = "=IF(A" & i & "=A" & i - 1 & ",IF(B" & i & "=B" & i - 1 & ",G" & i & "+1,1),1)"
            Next i
        End With
    Application.ScreenUpdating = True
    End Sub
    And
    Code:
    Sub test3()
        Dim lRow As Long
            
        Application.ScreenUpdating = False
            
        With ActiveSheet
            lRow = .Range("A" & Rows.Count).End(xlUp).row
            .Range("M3").Formula = "=IF(AND(A3=A2,B3=B2),G2+1,1)"
            .Range("M3:M" & lRow).FillDown
        End With
        
        Application.ScreenUpdating = True
        
    End Sub


  5. #5
    Board Regular wrightyrx7's Avatar
    Join Date
    Sep 2011
    Location
    Manchester, UK
    Posts
    994
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF statement in VBA

    Fantastic!! thank you both.

    Tried them both for feedback purposes and both work great. Thank you again.



    Can any of you help to input the following FormulaArray into column "N" in the same way as the previous.

    Code:
    =INDEX(Allowances!D$1:D$198,MATCH(1,(Allowances!C$1:C$198=G3)*(Allowances!E$2:E$198=B3),0))
    I tried to input :

    Code:
    Sub test()    
        Dim lastRow As Long
        
        With ActiveSheet
            lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            
            .Range("N3:N" & lastRow).FormulaArray = _
                "=INDEX(Allowances!D$1:D$198,MATCH(1,(Allowances!C$1:C$198=G2)*(Allowances!E$2:E$198=B3),0))"
        End With
    
    
    End Sub
    But the G2 and B3 remain static and do not change.

    Regards
    Chris
    Definitely not a PRO when it comes to VBA/Formula writing, but find it interesting.

    My practice is trying to help people on the forums so please be patient if I mess up :P

    A bit of feedback would be nice

  6. #6
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF statement in VBA

    For entering ARRAY formula in VBA, you have to follow Jacks 2nd example.
    Enter the array formula in the first cell only, then fill down.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  7. #7
    Board Regular wrightyrx7's Avatar
    Join Date
    Sep 2011
    Location
    Manchester, UK
    Posts
    994
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF statement in VBA

    Thanks Jonmo1 the array formula is now working.

    Now for one last one if you can help because i keep getting an error message

    Code:
    =IF(I3="N","",IFERROR(VLOOKUP(A3,'Active TP'!$A$2:$E$1976,5,FALSE),""))
    I think when i put it into VBA i need some more "" in it, but not 100%

    Thanks again
    Definitely not a PRO when it comes to VBA/Formula writing, but find it interesting.

    My practice is trying to help people on the forums so please be patient if I mess up :P

    A bit of feedback would be nice

  8. #8
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF statement in VBA

    In addition to Jacks 2nd example, there are a few other methods you can try. For example:

    Code:
    Sub test()
    
        Dim lastRow As Long
        
        With ActiveSheet
            lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            With .Range("N3:N" & lastRow)
                .Formula = "=INDEX(Allowances!D$1:D$198,MATCH(1,(Allowances!C$1:C$198=G2)*(Allowances!E$2:E$198=B3),0))"
                .FormulaArray = .FormulaR1C1
            End With
        End With 
    
    End Sub
    This article from Colin Legg provides a detailed description of all your options:
    Working With Range.FormulaArray In VBA | RAD Excel

  9. #9
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF statement in VBA

    Yep, when entering a formula through VBA, and that formula contains quote marks, you have to double up the quote marks.

    Try
    =IF(I3=""N"","""",IFERROR(VLOOKUP(A3,'Active TP'!$A$2:$E$1976,5,FALSE),""""))
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  10. #10
    Board Regular wrightyrx7's Avatar
    Join Date
    Sep 2011
    Location
    Manchester, UK
    Posts
    994
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF statement in VBA

    PERFECT! Thank you so much
    Definitely not a PRO when it comes to VBA/Formula writing, but find it interesting.

    My practice is trying to help people on the forums so please be patient if I mess up :P

    A bit of feedback would be nice

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •