Applying this VBA code to succeeding rows

[santosclarencephilip]

Hi,


Thank you in advance for your help. I'm trying to apply this vba code to every succeeding rows.

Private Sub CommandButton1_Click()


If Range("I2").Value > 0 Then
Sheets("Sheet1").Range("G2").ClearContents
Else
Sheets("Sheet1").Range("G2").ClearContents
Range("G2").Value = Range("L2").Value
End If


End Sub

I tried finding solutions from other threads relating about my problem but to no success.

Thanks again.

Clarence Santos

 

[JackDanIce]

Welcome to the board. Try:

Private Sub CommandButton1_Click()

    Dim x   As Long
    Dim msg As String

    Application.ScreenUpdating = False

    With ActiveSheet
        x = .Cells(.Rows.Count, 9).End(xlUp).Row - 1
        
        msg = "IF(@1>0,@2,"""")"
        msg = Replace(msg, "@1", .Cells(2, 9).Resize(x).Address)
        msg = Replace(msg, "@2", .Cells(2, 12).Resize(x).Address)
        
        .Cells(2, 7).Resize(x).Value = Evaluate(msg)
        
    End With

    Application.ScreenUpdating = True

End Sub

 

[santosclarencephilip]

Thanks for the answer JackDanIce, the code worked on all rows but i did not get my desired result because the code I first provided was wrong.

This is the VBA code I need to apply on all rows:

Private Sub CommandButton1_Click()


If Range("I2").Value <> 0 Then
Sheets("Sheet1").Range("G2").ClearContents
End If
If Range("I2").Value < 0 Then
Sheets("Sheet1").Range("G2").ClearContents
Range("G2").Value = Range("L2").Value
End If


End Sub

Thanks again and apologies for the wrong code i provided.

 

[JackDanIce]

I think you're saying "If value in column I is not zero, then replace column G with value from column L", try:

Private Sub CommandButton1_Click()


    Dim x   As Long
    Dim arr As Variant
    
    x = Cells(Rows.Count, 9).End(xlUp).Row
    
    arr = Cells(2, 7).Resize(x - 1, 6).Value
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        If Abs(arr(x, 3)) <> 0 Then arr(x, 1) = arr(x, 6)
    Next x
    
    Cells(2, 7).Resize(UBound(arr, 1)).Value = Application.Index(arr, , 1)
    Erase arr


End Sub

 

[santosclarencephilip]

I still did not get the desired result from your last code.

This is what i have in mind with my code:

"If I2 is not equal to zero then clear contents of G2 else do nothing, after the contents of G2 has been cleared, if I2 is less than 0 then copy contents of L2 to G2 else do nothing."

and the code must apply to all succeeding rows.

Thanks for taking the time to help me and cheers.

 

[JackDanIce]

Do you mean:

If I2 <> 0 then clear G2
If I2 <0 AND G2 is blank then G2 = L2?

Try:

Private Sub CommandButton1_Click()

    Dim x   As Long
    Dim arr As Variant
    
    x = Cells(Rows.Count, 9).End(xlUp).Row
    
    arr = Cells(2, 7).Resize(x - 1, 6).Value
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        If Abs(arr(x, 3)) <> 0 Then arr(x, 1) = vbNullString
        If arr(x, 3) < 0 And Len(arr(x, 1)) = 0 Then arr(x, 1) = arr(x, 6)
    Next x
    
    Cells(2, 7).Resize(UBound(arr, 1)).Value = Application.Index(arr, , 1)
    Erase arr

End Sub

 

[santosclarencephilip]

Thank you very much JackDanIce, it worked like a charm.
Thanks again for your time. Cheers

 

[santosclarencephilip]

Just a follow up question, what values will I change if i will use A2 instead of G2?

 

[JackDanIce]

If G maps to 7 (A to 1, B to 2, etc) then here:

 arr = Cells(2, 7).Resize(x - 1, 6).Value

Is reading the data from G2:Lx-1 where x is the row number of the last used row, -1 to account for fact start row is row 2.

You can read further if you search for VBA cell objects, here's one suggestion: https://www.guru99.com/vba-range-objects.html