VBA remove leading zeros

[Lelewiwi]

Hi there!

I have a bunch of code that is working beautifully. I need to add something to the end that will remove leading zeros from negative numbers in column G. The current code shows negative numbers as "0-0.85". Any help is appreciated! Below is my current code:

Sub Macro1()

Dim strFile As String
Dim ws As Worksheet

Set ws = ActiveSheet

strFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")

If strFile = "False" Then Exit Sub

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
    .TextFileParseType = xlDelimited
    .TextFileCommaDelimiter = False
    .Refresh
End With

'
' Macro1 Macro
' cleans up UPS text data

' dim nRows as Long

' nRows = InputBox("Please find how many rows of data there are in the UPS Data file.", "UPS Data Rows", "Enter the number of rows HERE")

' create new cols for data
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Data"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Acct#"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Account"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Mo"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Month"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Amt"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Amt1"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Amount"
    
' extract data to new cols
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-1],18,6)"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=VALUE(RC[-1])"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-3],14,4)"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=DATE(""20""&RIGHT(RC[-1],2),LEFT(RC[-1],2),""01"")"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-5],5)"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=CONCAT(LEFT(RC[-1],3),""."",RIGHT(RC[-1],2))"
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=VALUE(RC[-1])"
    
' populate data
    Range("B2:H2").Select
    Selection.AutoFill Destination:=Range("B2:H75000")
    Range("B2:H75000").Select
    
' paste acct#'s to new wksheet "First Split"
    Columns("C:C").Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "First Split"
    Sheets("First Split").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
' clean up acct#'s + populate Amounts
    ActiveSheet.Range("$A$1:$A$75000").RemoveDuplicates Columns:=1, Header:=xlYes
    Range("B1").Select
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Amount"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=SUMIF(Sheet1!R1C3:R48611C8,'First Split'!RC[-1],Sheet1!R1C8:R48611C8)" ' <-- need to match :R##### to # of rows w/ data
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B38") ' keep eye on #rows
    Range("B2:B38").Select
    ActiveWindow.SmallScroll Down:=-3
    
' paste acct#'s & months to new wksheet "Month Splits" + clean up data
    Range("B1").Select
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "Month Splits"
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Account"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Month"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Amount"
    Range("A2").Select
    Sheets("Sheet1").Select
    Range("C1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Month Splits").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("B2").Select
    Sheets("Sheet1").Select
    Range("E1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Month Splits").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("B3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy"
    Columns("A:B").Select
    ActiveSheet.Range("$A$1:$B$75000").RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
    
' populate data
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=SUMIFS(Sheet1!R2C8:R75000C8,Sheet1!R2C3:R75000C3,'Month Splits'!RC[-2],Sheet1!R2C5:R75000C5,'Month Splits'!RC[-1])" ' <-- need to match R##### to # of rows w/ data
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C360") ' keep eye on #rows
    Range("C2:C360").Select

End Sub

 

[Joe4]

now have a bug later in the code in this portion

Where exactly?
Which line of code?
What is the error message that is being returned or the unexpected behavior you are seeing?

 

[Lelewiwi]

Sorry, I am new to all this. I get a "Run-time error 1004: Application-defined or object-defined error" with this line:

Sheets("First Split").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 

[Joe4]

Ah, OK. I see.

So certain things you cannot condense down. Certain things you have to be on the sheet you want before you can perform actions on it.
So when switching between sheet, you often cannot combine those (note that you are dealing with sheets there, not ranges).

(And you are also missing the range reference in that line anyway - you only have the sheet reference - you always need a range reference to tell it where on the sheet to paste it).

 

[Lelewiwi]

Ok, I reversed cutting down the Select/Selection/ActiveCell and inserted the original code that you gave me and it works like a charm. Thank you so much!

 

[Joe4]

You are welcome.
Glad I was able to help!