On Sun, 16 Aug 2009 15:46:37 GMT, Lars-Åke Aspelin
wrote:

On Sun, 16 Aug 2009 08:28:01 -0700, Dee Sperling
wrote:

I need to add text to the beginning of a number if it starts with 7 and has a
length of 5 digits.

Below is what I wrote, but it puts the text in front of every number that
starts with 7, regardless of length. What did I do wrong?

Range("A2").Select
RowCount = 2
'do this as long as there is anything in column A
Do While Range("A" & (RowCount + 1)) < ""
'if PTAN starts with 7 and is 5 digits long, prepend with QQQQQ0
If (Left(Range("B" & RowCount), 1) = 7 And _
(Len(Range("B" & RowCount) = 5))) Then
Range("B" & RowCount) = "QQQQQ0" & Range("B" & RowCount)
RowCount = RowCount + 1
Else
RowCount = RowCount + 1
End If
Loop

The reason I'm doing this is that I need to have the leading zeros retained.
I will then be concatenating the numbers to the first cell in the row (using
the code that Joel so graciously wrote for me), separated by spaces. I will
then replace all the QQQQQ with nothing.

Thank you for your time. Any input is gratefully accepted.

Dee Sperling


This is wrong:

Len(Range("B" & RowCount) = 5)

It should be like

Len(Range("B" & RowCount)) = 5

Hope this helps / Lars-Åke

5 happens to the number of characters in FALSE
And FALSE is what the expression
Range("B" & RowCount) = 5
is evaluated to.

Lars-Åke