Hi
I'm fairly new to VBA and excel so this might be an easy one. Two problems; firstly I've a column of figures in an excel spread sheet. Some of the figures are negative but are in the wrong format ie they are written with the negative sign to the right of the figure (eg 34-)
instead of to the left (-34). I thus need to move the sign from the right to the left.
Also I have headers repeated throughout the spreadsheet that I want to delete. Any tips?

Cheers
Tony

__________________
Cheers
Tony

well, one solution to the first problem is as follows (and yes I know its not the neatest code but I'm only on a coffee break)..

Public Sub SortNegatives()
Dim iData As Integer: Dim iRow As Integer: Dim iCol As Integer
iRow = 2 ' Where iRow is the first row where the values are stored
iCol = 3 ' Where iCol is the column in which the values are stored
Do Until Cells(iRow, iCol).Value = ""
    If Right(Cells(iRow, iCol).Value, 1) = "-" Then
        iData = "-" & Mid(Cells(iRow, iCol).Value, 1, Len(Cells(iRow, iCol).Value) - 1)
        Cells(iRow, iCol).Value = iData
    End If
    iRow = iRow + 1
Loop
End Sub

Just call that procedure and specify which column and row to start in. If those deatils change you can always add them in via an input box just to make life easier.

as for the second problem...

Sub ClearHeaders()
With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
End With
End Sub

That can be repeated for each sheet on a for each loop if need be.

Hi

Thanks for that. The code for fixing the negatives does indeed work for a few records but I have just over 20000 and an overflow occurs. Is there a way around this?



Cheers
Tony