IF function in a macro

  • Thread starter Thread starter Docknet
  • Start date Start date
D

Docknet

Hi,

I need help with a macro. I have a column of numbers

123.35
589.25
632.55
-789.53
-1754.53

This column could have ten rows it could have a thousand rows. I need to
change the data into two columns
Col A Col B
123.35
589.25
632.55
789.53 C
1754.53 C

I need to strip out any negative number and if the number is negative place
a C in the column next to it. I can do it by using Two IF Statement

In column B I put

=IF(A1<0,-A1,A1)

In column C I put

=If(A1<0,"C","")

This works if I copy and paste it, but how do I convert this into a macro?
I tried the recorder but it didn't work.

Thanks for the help
 
Hi
try the following (processes the current selection)
sub foo()
dim rng as range
dim cell as range
application.screenupdating=false
set rng = selection
for each cell in rng
if cell.value<0 then
cell.value=-1*cell.value
cell.offset(0,1).value="C"
end if
next
application.screenupdating=true
end sub
 
Hi,

I need help with a macro. I have a column of numbers

123.35
589.25
632.55
-789.53
-1754.53

This column could have ten rows it could have a thousand rows. I need to
change the data into two columns
Col A Col B
123.35
589.25
632.55
789.53 C
1754.53 C

I need to strip out any negative number and if the number is negative place
a C in the column next to it. I can do it by using Two IF Statement

In column B I put

=IF(A1<0,-A1,A1)

In column C I put

=If(A1<0,"C","")

This works if I copy and paste it, but how do I convert this into a macro?
I tried the recorder but it didn't work.

Thanks for the help
Click to expand...

Here's one method:

=========================
Sub ProcNum()
Dim c As Range

For Each c In Selection
If IsNumeric(c.Value) And Not IsEmpty(c.Value) Then
c.Offset(0, 1) = Abs(c.Value)
If c.Value < 0 Then
c.Offset(0, 2) = "C"
Else
c.Offset(0, 2) = ""
End If
End If
Next c

End Sub
======================


--ron
 
Back
Top