0

I have a column in excel that I import from a program and it always imports it in this format if these bars represent the cells, | | with trailing spaces and <> to represent a negative numbers. The positive numbers come out like this, | $758.92| also with trailing spaces. The number format is Currency and I want to be able to sum these numbers and not have to go down the column of 100 or 200 cells and change all of them.
I started a macro that I want to iterate over each cell and remove the trailing spaces and replace the <> with () to indicate a negative number and just remove the trailing spaces for the positive numbers.

Sub Macro1()
        Dim i As Integer, d As Integer
        i = 13
        Do Until .Cells(4, i).Value = ""
            For d = 4 To 100  *I want the loop to stop at the first empty cell.*
                Cells(d, i) = Trim(Cells(d, i))
                Cells(d, i) *Replace the < at the beginning and end with ()*
            Next d
            i = i + 1
        Loop
    End Sub
dscarf
  • 34
  • 1
  • 5

1 Answers1

0

Before initiating that first do loop, you can find the last cell of the column if it will be the same each time:

lRow= Range("A" & Rows.Count).End(xlUp).Row 'Replace A with column you want

This will store the last row, and you can replace d = 4 to 100 with:

for d = 4 to lRow
Busse
  • 853
  • 6
  • 15
  • I received an "Expected: end of statement" error when I used the For d = 4 to 1Row statement – dscarf Jun 29 '17 at 14:21
  • The variable I included was LRow, with a lowercase L. If you were using a 1(one) instead of an L, then that would cause that error. Make sure the variables match and try again. – Busse Jun 29 '17 at 14:24
  • Thank you I got it to work. Would you also know how to replace < with ( in each cell? – dscarf Jun 29 '17 at 14:29
  • `Cells(d, i) = Trim(Cells(d, i)) Cells(d, i) = Replace(Cells(d, i), "<", "(") Cells(d, i) = Replace(Cells(d, i), ">", ")")` I am guessing this is what you are referring to. The first line trims (from your code). The second line replaces all < with (. And the third line replaces all > with ). I wasn't sure exactly which lines were necessary. – Busse Jun 29 '17 at 14:38
  • No this helped I added the cells.replace lines but changed them to = replace instead. Thank you for your help. – dscarf Jun 29 '17 at 14:44
  • Thank you. Mark it as the correct answer if it helped you. – Busse Jun 29 '17 at 14:50