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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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
NorieAnalyst Assistant Commented:
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.