Tom Ogilvy
12/20/2006 1:25:00 PM
Sub PurchaseReport()
Dim JDEdata, x as Long
'Correct JDE Credit Format
For x = 4 To myRows
JDEdata = Range("G" & x)
If Right(JDEdata, 1) = "-" Then
Range("G" & x) = "-" & Left(JDEdata,len(JDEData)-1)
End If
Next x
End sub
If you wanted to use the worksheet function substitute instead of the VBA
function Left and Len, you would do
Range("G" & x) = "-" & Application.WorksheetFunction.Substitute(JDEdata,
"-", , 1)
You could also use
Range("G" & x) = cdbl(JDEdata)
--
Regards,
Tom Ogilvy
"Helen" wrote:
> This macro is supposed to reformat numbers that have a "-" sign in them.
> Placing the "-" at the front instead of the end of the number.
>
> I'm getting an error message asking me to define the sub / function, what
> have I done wrong?
>
> Sub PurchaseReport()
>
> Dim JDEdata
>
> 'Correct JDE Credit Format
>
> Range("G4").Select
>
> For x = 4 To myRows
> JDEdata = Range("G" & x)
>
> If Right(JDEdata, 1) = "-" Then
>
> Application.WorksheetFunction = Substitute(JDEdata, "-", , 1)
>
> Application.WorksheetFunction = Substitute(JDEdata, Left(JDEdata), "-" &
> Left(JDEdata), 1)
>
> End If
> Next x