How to use substitute function in macro?

[Eric]

Does anyone have any suggestions on using substitute function in macro?
I would like to remove any "B" character within the return value from
Sheets("Temp").Range("$J$15").Value.

------------------------------
Macro coding
myCell.Offset(0, 5).Value = Sheets("Temp").Range("$J$15").Value
------------------------------

For example
if the return value for Sheets("Temp").Range("$J$15").Value is 25.8B, then I
would like to substitute "B" with "", which should return 25.8 into
myCell.Offset(0, 5).Value.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

 

[Ron Coderre]

How about just using the VBA version:

Try this:

myCell.Offset(0, 5).Value = _
Replace( _
Expression:=Sheets("Temp").Range("$J$15").Value, _
Find:="B", _
Replace:="", _
Compare:=vbTextCompare)

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Rick Rothstein \(MVP - VB\)]

Don't use the SUBSTITUTE function... since you are in a macro and your
substitution is pretty basic, use the VBA Replace function instead (it will
be faster)..

myCell.Offset(0, 5).Value = Replace(Sheets("Temp").Range("$J$15").Value,
"B", "")

Rick

 

[Eric]

Referring to the post index function
Does anyone have any suggestions on how to use index function in coding macro?

myCell.Offset(0, 2).Value =
Index(B:B [under Temp worksheet],match("Industry",A:A [under Temp
worksheet],0))

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

 

[Ron Coderre]

Here are several alternatives:

myCell.Offset(0, 2).Value = _
WorksheetFunction.Index(Sheets("Temp").Range("B:B"), _
WorksheetFunction _
.Match("Industry", Sheets("Temp").Range("A:A"), 0)).Value

or...

myCell.Offset(0, 2).Value = _
Sheets("Temp").Range("B:B") _
.Cells(WorksheetFunction _
.Match("Industry", Sheets("Temp").Range("A:A"), 0))

or...

myCell.Offset(0, 2).Value = _
Sheets("Temp").Cells(WorksheetFunction _
.Match("Industry", Sheets("Temp").Range("A:A"), 0), 2)

or...with no worksheet function calls:

myCell.Offset(0, 2).Value = _
Sheets("Temp").Range("A:A") _
.Find(What:="Industry", LookAt:=xlWhole, MatchCase:=False) _
.Offset(ColumnOffset:=1).Value


Note, though, you'll probably need to trap the error
caused if "Industry" is not found.

Something like this:

Dim rFindResult As Range
Set rFindResult = Sheets("Temp").Range("A:A") _
.Find(What:="Industry", LookAt:=xlWhole, MatchCase:=False)

Select Case rFindResult Is Nothing
Case Is = True
myCell.Offset(0, 2).Value = "n/a"
Case Else
myCell.Offset(0, 2).Value = rFindResult.Offset(ColumnOffset:=1)
End Select


Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Referring to the post index function
Does anyone have any suggestions on how to use index function in coding
macro?

myCell.Offset(0, 2).Value =
Index(B:B [under Temp worksheet],match("Industry",A:A [under Temp
worksheet],0))

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

How about just using the VBA version:

Try this:

myCell.Offset(0, 5).Value = _
Replace( _
Expression:=Sheets("Temp").Range("$J$15").Value, _
Find:="B", _
Replace:="", _
Compare:=vbTextCompare)

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)