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.

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?

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.

In a spare column to the right of your data use this formula:

=B2*IF(LEFT($A2,2)*1=93,-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.

```
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 accordinglyJcutcliffe_ChangeValues.xlsm

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.

```
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
```
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
```

http://spreadsheetpage.com/index.php/site/tip/undoing_a_vba_subroutine/

All Courses

From novice to tech pro — start learning today.

Open in new window