Excel-VBA loop ifs

Question

I've written 2 macros to do this task but I'm trying to consolidate and make it more efficient.

If there is a value = 1 in column I (it will either be blank or = 1) then look at column G
If the Value in column G < 30 OR if the Value in column H < 0.03 THEN overwrite the value in column I to = "0" ... (if not then don't change the value in column I and move on to check the next)

The Ranges are I9:I45000, G9:G45000, and H9:H45000.

I think there is a simple solution but after a few hours my un-educated self can't find it.

Module1:

Dim rngCell As Range, _
    rngDataRange As Range

Set rngDataRange = Range("G9:G45000")
For Each rngCell In rngDataRange
    With rngCell
        If .Value < 30 Then
            .Offset(0, 2).Value = "0"    'A[rngCell] to C[rngCell]
        End If
    End With
Next rngCell
End Sub

Module2:

Sub Macro1()
Dim rngCell As Range, _
    rngDataRange As Range

Set rngDataRange = Range("H9:H45000")

For Each rngCell In rngDataRange
    With rngCell
        If .Value < 0.03 Then
            .Offset(0, 1).Value = "0"    'A[rngCell] to C[rngCell]
        End If
    End With
Next rngCell
End Sub

This is the macro I run first.... It puts values in some of the cells in column I (where column C has values less than 1575):

Sub Macro1 () Dim rngCell As Range,_ rngDataRange As Range

Set rngdataRange = Range (C9:C45000)

For Each rngCell In rngDataRange
    With rngCell
        If .Value < 1575 Then
           .Offset (0,6).Value="1"
        End If
    End With
Next rngCell

End Sub

Thanks for the guidance. Sorry for my ignorance.It should be edited... hopefully somewhat correctly — VBA Noob
– VBA Noob, Commented Aug 10, 2017 at 14:58
You mentioned that you are checking to see if there is a value of 1 in Column 'I', but I don't see that anywhere in your code. Are you doing that calculation somewhere else? — K Paul
– K Paul, Commented Aug 10, 2017 at 15:00
Is there a problem with the code? If it works as intended but it's slow, repetitive or otherwise inefficient, consider presenting your code on Code Review; Stack Overflow is about specific programming issues... which I'm still not seeing in this post :( — Mathieu Guindon
– Mathieu Guindon, Commented Aug 10, 2017 at 15:03

Variatus · Accepted Answer · 2017-08-10 23:43:18Z

2

This should do the job.

Sub CheckClmI()

    Dim Rl As Long                      ' Last row
    Dim R As Long

    Application.ScreenUpdating = False
    With ActiveSheet
        ' Used range should be enough
        Rl = .UsedRange.Rows.Count

        For R = 9 To Rl
            If Val(.Cells(R, "I").Value) = 1 Then
                If Val(.Cells(R, "G").Value) < 30 Or _
                   Val(.Cells(R, "H").Value < 0.03) Then
                   .Cells(R, "I").Value = 0
                End If
            End If
        Next R
    End With
    Application.ScreenUpdating = True
End Sub

edited Aug 10, 2017 at 23:43

answered Aug 10, 2017 at 15:13

Variatus

14.4k2 gold badges16 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

K Paul Over a year ago

This looks like the way to go. I just didn't understand what was going on with the whole Column I part...

VBA Noob Over a year ago

Thank you. However, when I run it, the macro fills column I with 0's even in instances where there was not a 1 to begin with and/or where columns G and H have values greater than (30 or 0.03)... not sure why this is though, to my untrained eye your answer looks good.

VBA Noob Over a year ago

Seems like I need some language that says what to do if the IF statement is false... Like move the Next R line up before the End If ... though when I try to do that I get an error message and it won't let me try it :(

Variatus Over a year ago

I did that bit latest last night and in a hurry. It contained a serious referencing error which occurred to me this morning before breakfast and that was the time I fixed it. Apologies! I also added Val() functions. They will ensure that numbers are processed even if they might be in Text format. I don't understand "move the next R line upd If". Please explain.

Variatus Over a year ago

Your value of 45000 throws a question. Is there a column in your sheet which has a value in every used row from row 9 until the end? If you want to move rows this fact would be very useful.

|

K Paul · Accepted Answer · 2017-08-10 15:15:42Z

0

What about something like this?

Sub Macro1()
    OnError Goto OopsIDidItAgain
    Dim rngCell As Range, rngDataRange As Range

    Application.ScreenUpdating = False
    Set rngDataRange = Range("G9:G45000")

    For Each rngCell In rngDataRange
        With rngCell
            If .Value < 30 Or .Offset(0, 1).Value < 0.03 Then .Offset(0, 2).Value = "0"
        End With
    Next rngCell
OopsIDidItAgain:
    Application.ScreenUpdating = True
End Sub

edited Aug 10, 2017 at 15:15

answered Aug 10, 2017 at 15:10

K Paul

14410 bronze badges

3 Comments

TJYen Over a year ago

Since the range is large, I would turn off screenupdating before the start of the macro and re-activate it after the for loop

K Paul Over a year ago

@TJYen, true true

Variatus Over a year ago

Yes. I incorporated that in my answer as well.

Davesexcel · Accepted Answer · 2017-08-10 15:19:53Z

0

I like to count the rows so you don't have wasted loops.

Dim LstRw As Long
Dim Rng As Range, c As Range

LstRw = Cells(Rows.Count, "G").End(xlUp).Row
Set Rng = Range("G9:G" & LstRw)
For Each c In Rng.Cells
    If c < 30 Or c.Offset(, 1) < 0.03 Then c.Offset(, 2) = 0
Next c

answered Aug 10, 2017 at 15:19

Davesexcel

6,9922 gold badges31 silver badges44 bronze badges

Comments

Erin Halbmaier · Accepted Answer · 2017-08-10 15:21:43Z

0

You can just do all the tests in one go:

Dim rngCell As Range
Dim rngDataRange As Range
Dim iCell as range
Dim hVal as variant

Set rngDataRange = Range("G9:G45000")
For Each rngCell In rngDataRange
    With rngCell
        Set iCell = .Offset (0,2)
        hVal = .Offset (0,1).Value

        If iVal = 0 or iVal = vbnullstring then
            If .Value < 30 or hVal > .3 Then
                iCell.Value = "0"    
            End If
        End if
    End With
 Next rngCell
 End Sub

edited Aug 10, 2017 at 15:21

answered Aug 10, 2017 at 15:17

Erin Halbmaier

3545 silver badges9 bronze badges

Collectives™ on Stack Overflow

Excel-VBA loop ifs

4 Answers 4

7 Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related