Loop with VBA

[thecat23]

Hi all,

I am trying to repeat the code below a number of times until it reaches row 28. Every time, the row number increases 1, e.g. all 21 becomes 22, 23 and so on until reaches 28. I am not sure how to do it with a loop, what I have done is to copy and paste the code 8 times, and change the row values manually. But this make the code too long, is there a easier way to do this? perhaps with a loop?

Thanks in advance.

If Range("AB21").Value <> "" Then
        Range("AW21").Value = 0
        Range("AX21").Value = Range("N10").Value
Else
       Range("AW21").ClearContents
       Range("AX21").ClearContents
End If

 

[duggie33]

Hi thecat23,

Is the value in N10 to be used in all cases where the if statement is true?

Doug

 

[mumps]

Try:

Sub Test()
    Application.ScreenUpdating = False
    Dim x As Long
    For x = 21 To 28
        If Range("AB" & x).value <> "" Then
            Range("AW" & x).Value = 0
            Range("AX" & x).Value = Range("N10").Value
        Else
            Range("AW" & x).ClearContents
            Range("AX" & x).ClearContents
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

 

[thecat23]

Hi thecat23,

Is the value in N10 to be used in all cases where the if statement is true?

Doug

Hi duggie33,

N10 is to remain the same and used in all cases.

Cheers!

 

[mumps]

Did you try the macro I suggested in Post #3?

 

[duggie33]

Sub CheckUpdate()

    Dim rngCheck As Range
    Dim rng As Range
    
    Set rngCheck = Sheet1.Range("AB21:AB28")
    
    For Each rng In rngCheck
        If rng.Value <> "" Then
            rng.Offset(0, 21).Value = 0
            rng.Offset(0, 22).Value = Sheet1.Range("N10").Value
        Else
            rng.Offset(0, 21).ClearContents
            rng.Offset(0, 22).ClearContents
        End If
    Next rng

End Sub

 

[thecat23]

Did you try the macro I suggested in Post #3?

Hi mumps

your code indeed works! and it was easy for me to understand as well.

Thanks mumps

 

[thecat23]

Sub CheckUpdate()

    Dim rngCheck As Range
    Dim rng As Range
   
    Set rngCheck = Sheet1.Range("AB21:AB28")
   
    For Each rng In rngCheck
        If rng.Value <> "" Then
            rng.Offset(0, 21).Value = 0
            rng.Offset(0, 22).Value = Sheet1.Range("N10").Value
        Else
            rng.Offset(0, 21).ClearContents
            rng.Offset(0, 22).ClearContents
        End If
    Next rng

End Sub

Hi duggie33,

Thanks for your help, I have change the offset value and it does what exactly what I was trying to do.

Thumps up.

 

[mumps]

Glad to help.