# 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?
Financial ControlAsked:
###### Who is Participating?
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.

Finance 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.
Finance 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.
Excel & VBA ExpertCommented:
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
``````
Change the range accordingly
Jcutcliffe_ChangeValues.xlsm
Finance 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.
Excel & VBA ExpertCommented:
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
``````
Jcutcliffe_ChangeValues.xlsm
Analyst 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
``````
Finance 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.
Financial ControlAuthor 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?
Group Finance ManagerCommented:
Rob

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

Undoing A VBA Subroutine
Excel & VBA ExpertCommented:
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
``````

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.

Financial ControlAuthor Commented:
Superb!
Excel & VBA ExpertCommented:
You're Welcome! Glad I was able to help :)
Finance AnalystCommented:
@Roy - your link just comes back to this question
Group Finance ManagerCommented:
###### 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.