Decrease value based on other column

Question

If Column D contains a value and Column O contains "x" then I want to decrease the value in Column D by 10 but I am getting a ‘Compile error: Syntax error’ with the code below, can you help?

Sub Decrease()

Dim Ttt As Long

Dim Rrr As Long

Ttt = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row

For Rrr = Ttt To 2 Step -1

If (Cells(Rrr, "O") = "x") Then

    Cells(Rrr, "D") - 10

End If

Next Rrr

End Sub

Answer 1

Apologies for the confusion. It seems I made a mistake in my previous response.

Sub Decrease()

    Dim Ttt As Long

    Dim Rrr As Long

    Ttt = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row

    For Rrr = Ttt To 2 Step -1

        If Cells(Rrr, "O") = "x" Then

            Cells(Rrr, "D").Value = Cells(Rrr, "D").Value - 10

        End If

    Next Rrr

End Sub

Answer 2

Hi William R!

Please try this: Sub Decrease() Dim Ttt As Long Dim Rrr As Long Ttt = ActiveSheet.Cells(Rows.Count, "D"). End(xlUp). Row For Rrr = Ttt To 2 Step -1 If Cells(Rrr, "O") = "x" Then Cells(Rrr, "D"). Value = Cells(Rrr, "D"). Value - 10 End If Next Rrr End Sub

Kindly let me know, if you require additional assistance, I will be glad to help further.

Best Regards, Shakiru

Answer 3

ASKNiko thanks for your input. I think you might have meant to reply to a different post.

If Column D contains a value and Column O contains "x" then I want to decrease the value in Column D by 10 but I am getting a ‘Compile error: Syntax error’ with the code below. I am looking for help with this problem.

Sub Decrease()

Dim Ttt As Long

Dim Rrr As Long

Ttt = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row

For Rrr = Ttt To 2 Step -1

If (Cells(Rrr, "O") = "x") Then

    Cells(Rrr, "D") - 10

End If

Next Rrr

End Sub

Answer 4

To separate the data into different sheets based on the natural account number, you can use VBA code in Excel. Here is an example code that you can modify to fit your specific needs:

Sub SeparateDataByAccount()

    Dim sourceSheet As Worksheet

    Dim targetSheet As Worksheet

    Dim lastRow As Long

    Dim accountNumber As String

    Dim row As Long

    ' Set the source sheet where the data is located

    Set sourceSheet = ThisWorkbook.Worksheets("SourceSheet")

    ' Loop through each row in the source sheet

    For row = 2 To sourceSheet.Cells(Rows.Count, 1).End(xlUp).Row

        ' Get the account number from column A

        accountNumber = sourceSheet.Cells(row, 1).Value

        ' Check if a sheet exists for the account number, create one if it doesn't

        On Error Resume Next

        Set targetSheet = ThisWorkbook.Worksheets(accountNumber)

        On Error GoTo 0

        If targetSheet Is Nothing Then

            ' Create a new sheet for the account number

            Set targetSheet = ThisWorkbook.Worksheets.Add

            targetSheet.Name = accountNumber

            ' Copy the headers from the source sheet

            sourceSheet.Rows(1).Copy Destination:=targetSheet.Rows(1)

        End If

        ' Find the last row in the target sheet

        lastRow = targetSheet.Cells(Rows.Count, 1).End(xlUp).Row

        ' Copy the current row of data to the target sheet

        sourceSheet.Rows(row).Copy Destination:=targetSheet.Rows(lastRow + 1)

    Next row

End Sub

The code will loop through each row in the source sheet, check the account number, and copy the row to the corresponding sheet based on the account number. If a sheet does not exist for a particular account number, it will create a new sheet and copy the row there.

Make sure to adjust the code according to your specific column and sheet names.