I got help a week or so ago on some VBA for a routine where I needed to change the number in a cell to a negative number based on the content of the cell to its right. However, after I copied it into VBA it will not work. I've changed the range ID on both but the only effect I've had is for it to change some cells to negative and it appears to be random at best. Here's an example of the dat

G

169.00 C
241.30
0.14 C
2276.0

For example I have a column of numbers and in the column directly to the right are cells with the letters "CR" for credit. What I want to do is to change the numbers to a negative if CR is in the cell to its right. Below is the code I received.

Sorry for my ignorance on VBA. I'm okay with formulas, but just now learning how to better use macros, much less write VBA. Maybe I just don't understand what my ranges are, all I know is that I can't get it to work

Thanks...stev

Sub ChangingCredit(

Dim credit As Strin

Range("C7").Select 'starting poin

Do Until x = 10 'Change number for extra number of blanks between dat
rng = ActiveCel
credit = ActiveCell.Offset(0, 1).Range("A1").Value 'assuming Cr right of numbe
If credit = "CR" The
ActiveCell.Value = rng * -
End I
ActiveCell.Offset(1, 0).Selec
x = x +
Loo
End Su

Hi Steve
so you want to change column G if column H contains the value 'HR'.
Change your macro to
Sub ChangingCredit()
'
Dim credit As String
dim rng as range
dim cell as range
set rng = range("H7:H17") 'range to be searched - adapt to your
needs
for each cell in range
if cell.value = "CR" then
cell.offset(0,-1).value = cell.offset(0,-1).value*(-1)
end if
next
End Sub


No need for activating and selecting cells

--
Regards
Frank Kabel
Frankfurt, Germany

Steve wrote:
I got help a week or so ago on some VBA for a routine where I needed
to change the number in a cell to a negative number based on the
content of the cell to its right. However, after I copied it into VBA
it will not work. I've changed the range ID on both but the only
effect I've had is for it to change some cells to negative and it
appears to be random at best. Here's an example of the data

G H

169.00 CR
241.30
0.14 CR
2276.00

For example I have a column of numbers and in the column directly to
the right are cells with the letters "CR" for credit. What I want to
do is to change the numbers to a negative if CR is in the cell to its
right. Below is the code I received.

Sorry for my ignorance on VBA. I'm okay with formulas, but just now
learning how to better use macros, much less write VBA. Maybe I just
don't understand what my ranges are, all I know is that I can't get
it to work.

Thanks...steve

Sub ChangingCredit()
'
Dim credit As String

Range("C7").Select 'starting point

Do Until x = 10 'Change number for extra number of blanks between
data rng = ActiveCell
credit = ActiveCell.Offset(0, 1).Range("A1").Value
'assuming Cr right of number If credit = "CR" Then
ActiveCell.Value = rng * -1
End If
ActiveCell.Offset(1, 0).Select
x = x + 1
Loop
End Sub