hi,
i run a macro that gets data from a mainframe and put it into exel. there are 3 columns where the data has a qty in them
i put this data then into a pivot table . the problem i have is that the minus numbers come down as follows
300- etc
in the macro i would like to put some code that will change any number with the - at the back to teh frony of the number
ie -300
thanks
steve
Hi,
Formula would be
HTH
Please Login or Register to view this content.
Carim
something like
Sub something()
Dim LookRange As Range
Dim Item As Range
Set LookRange = Range("A1:A100") 'set the range you want to search
For Each Item In LookRange
If Mid(Item, Len(Item), 1) = "-" Then
Item = "-" & Mid(Item, 1, Len(Item) - 1)
End If
Next
End Sub
--Mark
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
hi,
thanks both
this code changes as needed but it errors at the end with the if mid (item line
Please Login or Register to view this content.
steve
Hi,
Just tested one cell ...
HTHPlease Login or Register to view this content.
Carim
what version of excel do you have
and what kind of error did it give you?
i think the problem might be that you're looking in a blank cell.
Sub something()
Dim LookRange As Range
Dim Item As Range
Set LookRange = Range("A1:A100") 'set the range you want to search
For Each Item In LookRange
if item=blank then
goto skiptonext:
end if
If Mid(Item, Len(Item), 1) = "-" Then
Item = "-" & Mid(Item, 1, Len(Item) - 1)
End If
skiptonext:
Next
End Sub
Last edited by MDubbelboer; 11-29-2006 at 02:25 PM.
carim,
i need it to change in the cell it is already in and in 3 columns maybe 50000 lines down
steve
Ok then ...
Please Login or Register to view this content.
thanks to both of you works great
MDubbelboer it was the blanks
steve
Glad your problem is now fixed.
Thanks for the feedback
Carim
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks