Do Loop that loops down Column A and finds any string that begins with "93", then multplies all values in that row by -1?

Hi Guys, I have an accounts spreadsheet where all the Account Numbers begin with 93 or 94 in Column A. How do I create a macro that can loop down Column A , find all the numbers in the rows that begin with 93, then multiply all the values in the same row by -1? For example, if Cell A45 = 935678, then multiply all the numbers in row 45 to the right of Column A by -1 so if B45 = 57, the result will be -57?
JCutcliffeAsked:
Who is Participating?
 
ShumsDistinguished Expert - 2017Commented:
Try below:
Sub ChangeValues()
Dim Ws As Worksheet
Dim LRow As Long, i As Long, c As Long
Set Ws = Worksheets("Sheet1") 'Change your sheet name here
LRow = Ws.Range("A" & Rows.Count).End(xlUp).Row 'Change the last row column here
Application.ScreenUpdating = False
For i = 2 To LRow
    For c = 4 To 156
        If Left(Ws.Cells(i, 1), 2) = 93 Then
            Ws.Cells(i, c).Value = Ws.Cells(i, c) * -1
        End If
    Next c
Next i
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
Rob HensonFinance AnalystCommented:
Will or can the account numbers starting with 93 be in a block so you can select the relevant cells as a block?

If so you can use a feature of Paste Special.

Type -1 into a spare cell and use Ctrl + C to copy it to the clipboard.

Select the values that need changing and then use the Paste Special dialogue. In the top section choose Values and then in the middle section choose the Multiply option and then click OK. All selected values will have been multiplied by the -1.

I did try with AutoFilter hiding the rows not required and selecting the whole data set but unfortunately the hidden rows get affected as well; hence the reason for the question whether they will be in a block.
1
 
Rob HensonFinance AnalystCommented:
Alternatively, you can do it with a simple IF formula.

In a spare column to the right of your data use this formula:
=B2*IF(LEFT($A2,2)*1=93,-1,1)

This would be relevant to row 2 and column B so should go in row 2. Copy to the right enough columns to cover the columns of the dataset and down enough rows for all the data. You can then copy this block and paste back into the source columns as values.
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ShumsDistinguished Expert - 2017Commented:
Try below
Sub ChangeValues()
Dim Ws As Worksheet
Dim LRow As Long, i As Long
Set Ws = Worksheets("Sheet1") 'Change your sheet name here
LRow = Ws.Range("A" & Rows.Count).End(xlUp).Row 'Change the last row column here
Application.ScreenUpdating = False
For i = 2 To LRow
    If Left(Ws.Cells(i, 1), 2) = 93 Then
        Ws.Cells(i, 2).Value = Ws.Cells(i, 2) * -1
    End If
Next i
Application.ScreenUpdating = True
End Sub

Open in new window

Change the range accordingly
Jcutcliffe_ChangeValues.xlsm
0
 
Rob HensonFinance AnalystCommented:
@Shums - if I read correctly:
1) that will do both 93 and 94 values, author only needs 93 values changed
2) that will only affect column 2, needs to affect all columns of the particular row.
0
 
ShumsDistinguished Expert - 2017Commented:
If he needs all the columns then try below:
Sub ChangeValues()
Dim Ws As Worksheet
Dim LRow As Long, LCol As Long, i As Long, c As Long
Set Ws = Worksheets("Sheet1") 'Change your sheet name here
LRow = Ws.Range("A" & Rows.Count).End(xlUp).Row 'Change the last row column here
LCol = Ws.Cells(1, Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
For i = 2 To LRow
    For c = 2 To LCol
        If Left(Ws.Cells(i, 1), 2) = 93 Then
            Ws.Cells(i, c).Value = Ws.Cells(i, c) * -1
        End If
    Next c
Next i
Application.ScreenUpdating = True
End Sub

Open in new window

Jcutcliffe_ChangeValues.xlsm
0
 
NorieVBA ExpertCommented:
Perhaps something like this.
Dim rng As Range
Dim arrDataIn As Variant
Dim I As Long
Dim J As Long



    Set rng = Sheets("Sheet1").Range("A1").CurrentRegion

    arrDataIn = rng.Offset(1).Resize(rng.Rows.Count - 1).Value
    
    For I = LBound(arrDataIn) To UBound(arrDataIn)
        Select Case Left(arrDataIn(I, 1), 2)
            Case "93", "94"
                For J = 2 To UBound(arrDataIn, 2)
                    If arrDataIn(I, J) <> "" Then
                        arrDataIn(I, J) = arrDataIn(I, J) * -1
                    End If
                Next J
            Case Else
                ' do nothing
            End Select
    Next I
        
    rng.Offset(1).Resize(rng.Rows.Count - 1).Value = arrDataIn

Open in new window

0
 
Rob HensonFinance AnalystCommented:
BTW - the reason for my suggestion for doing it with Paste Special is that VBA routines clear the Undo history; running the macro will do what is required but will not be able to Undo.
0
 
JCutcliffeAuthor Commented:
Hi Shums, apologies, your Macro is near perfect but I want to multiply all Value by -1, between Column D and EZ in the row where the string in the column A starts with 93. How do I tweak it?
0
 
Roy CoxGroup Finance ManagerCommented:
Rob

John Walkenbach has code to solve this if it is an issue

Undoing A VBA Subroutine
0
 
JCutcliffeAuthor Commented:
Superb!
0
 
ShumsDistinguished Expert - 2017Commented:
You're Welcome! Glad I was able to help :)
0
 
Rob HensonFinance AnalystCommented:
@Roy - your link just comes back to this question
0
 
Roy CoxGroup Finance ManagerCommented:
0
All Courses

From novice to tech pro — start learning today.