Remove h

[C]

My spreadsheet has 7 Columns of data..A:G. I have monthly sales numbers to
the right of that with the month headers starting in H4. The monthly numbers
will have a trailing h with them. The monthly columns may be H6:N50 or H6 to
JA 5000. The numbers in H6:...could be one diget to 10 digets. Is there any
way to go through this and eliminate the h?

thanks in advance,

CB

 

[Paul]

The following code should do what you're after:


Code:
--------------------


Sub removeH()
Range(Cells(6, "H"), Cells(Rows.Count, Columns.Count)).Replace _
What:="h", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

 

[B Lynn B]

you could do this with formulas, but since you're asking in the excel
programming group, here's some code that will do it. This has user manually
select the cells they want it to work on. But you could just as easily
define a set range...

Sub StripRightH()

For Each cell In Selection.Cells
If cell <> "" And UCase(Right(cell, 1)) = "H" Then _
cell.Value = Left(cell, Len(cell) - 1)
Next cell

End Sub

 

[Paul]

Not sure why my posts don't appear on the actual newsgroups all th
time. Anyway..

The following code should do what you're after:

Sub removeH()
Range(Cells(6, "H"), Cells(Rows.Count, Columns.Count)).Replace _
What:="h", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

 

[Gord Dibben]

Your first post in this thread showed up 18 minutes ago.


Gord

 

[C]

This works fine for a selection. Is there another way to do this by asking
for the range from the user, then converting this back to text as the cells
see them as a number stored as text.

Thanks

 

[Paul J.]

Sub removeH()
Range(Cells(6, "H"), Cells(Rows.Count, Columns.Count)).Replace _
What:="h", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

 

[Paul]

Odd.. I'm monitoring the newsgroups right on Microsoft.com and throug
codecage.com (where I'm submitting replies), and it takes 40-60 minute
for my replies from codecage.com to appear on the actual newsgroups, an
I'm refreshing several times per minute.

The posting times when they do appear (adjusted for Pacific -> Centra
time) is 40+ minutes later on average. I just re-posted the same answe
by logging directly into the newgroups and it took a few minutes t
appear there. (Understandable but still slow.)

Darn technology!!

Your first post in this thread showed up 18 minutes ago.

Gord