VB Code problem.

[books4cars]

I use the following vb code every day in excel:

Sub prop()
Dim rng1 As Range, c As Range
Set rng1 = Intersect(ActiveSheet.UsedRange, [A:G,I:O,Q:IV])
If Not rng1 Is Nothing Then
For Each c In rng1
c.Value = WorksheetFunction.Proper(c.Value)
Next
End If
[P:P].Replace "non", ""
[P:P].Replace "select...", ""
[e:e,n:n].Replace "P.O.", "PO"
[e:e,n:n].Replace "P. O.", "PO"
[e:e,n:n].Replace "p o", "PO"
[y:y].Replace " ", ""
[y:y].Replace "-", ""

Do
Set c = Cells.Find(" ")
If Not c Is Nothing Then Cells.Replace " ", " "
Loop Until c Is Nothing

For Each c In Intersect(ActiveSheet.UsedRange, [i:i,q:q])
If c Like "*Us*" Then c.Offset(, 1) = Left(c.Offset(, 1), 5)
Next

End Sub



I just added the two lines of code for column Y to remove spaces and hyphens from all cells in that column. When I run the code, the cells that are affected show notation like this 4.85321E+15 instead of a long number. All the cells are formatted to text before I run the code. How can I get just the numbers and not the notation?
Thx

 

[books4cars]

Can anybody help me? Is this posted in the wrong place?

 

[Norie]

Mordecai

Perhaps if you explained the purpose of the code you might get some responses.

It might also help to post some sample data, both before and after.

By the way I really wouldn't recommend using [] notation when referring to ranges.

And it might be an idea to explicitly reference the worksheet rather than using ActiveSheet.

 

[books4cars]

The numbers are credit cards which are on a sheet with other customer info. I am simply trying to remove all hyphens and spaces from that column.

As for not using the "[]" when refrecing a column, what do you suggest as an alternative?

 

[Norie]

Mordecai

It really would be helpful to see some sample data.

If you are worried about confidentiality then just create some sample data.

As to not using [], why not use Range("A:A") etc and qualify the worksheet(s) like this.

Worksheets("Sheet1").Range("A:A")

That should ensure that the correct range on the correct worksheet is being used in the code.

If you use [] without any worksheet reference then VBA will refer to the range it considers to be the active sheet.

And that might not be the one you want.

 

[books4cars]

SO I should replace

[y:y].Replace "-", ""

with

Worksheets("Sheet1").Range("y:y").Replace "-", ""