Help with "Do until empty" Loop

[dfurn]

Ok, here it is.

I need to remove the dashes from a number (cells are formated as "General" because I need the preceding zero's) that is stored in Column B. The first cell containing the data is B2. The numbers found in columns B look like this, 00-838-5033. I need the output to be in Row H, as a value, in "General" format. Blank columns C through H have already been inserted to do the formulas.

For the loop portion, I need this to run until it reaches a blank cell in column B.

Here's the code I used (remember that I'm extremely new to this, so probably extremely inefficient in my coding):

Sub RemoveDash()
'
' RemoveDash Macro
'
Range("C2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],LEN(RC[-1])-9)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-2],LEN(RC[-2])-5)"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],LEN(RC[-1])-3)"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-4],LEN(RC[-4])-7)"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=RC[-4]&RC[-2]&RC[-1]"
Range("G2").Select
Selection.Copy
Range("H2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

Thanks in advance,
Dave

[Norie]

Perhaps you could do it without a loop?

Please Login or Register  to view this content.

[dfurn]

Norie,

Thanks for the quick reply. I've copied and pasted your code into the macro, but I'm getting the followoing error, "Compile error: Invalid use of proptery". The "Sub RemoveDash()" is highlighted in yellow and ".FormulaR1C1 " of the Range("E2:E" & LastRow).FormulaR1C1 "=RIGHT(RC[-1],LEN(RC[-1])-3)" line of code is selected (blue).

Any ideas????

Could it be because of the LastRow = Range("B" & Rows.Count).End(xlUp).Row referring to "B"???

I'm just grasping here!!!

[Norie]

It's a typo.

Specifically a missing =.

Please Login or Register  to view this content.

[dfurn]

I actually just saw that and was going to let you know, when I got the email stating that you replied. Thanks again.

The macro now runs with no errors, however, the pasting of resulting value is populated in cells of Column N and starting in cell N3 and finishing one row past the last row with data.

??????

[Norie]

Another 'typo'. well mistake really - I was in a rush, honest.

Anyway, 3rd time lucky.

Please Login or Register  to view this content.

[dfurn]

Norie,

Thanks so much for sticking with this. The thrid time was lucky. It works greast.

[Norie]

No problem.