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

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:="", _
Compa=vbTextCompare)

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

Regards,

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

"Eric" wrote in message
...
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

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" wrote in message
...
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

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" wrote:

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:="", _
Compa=vbTextCompare)

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

Regards,

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

"Eric" wrote in message
...
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

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)

"Eric" wrote in message
...
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" wrote:

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:="", _
Compa=vbTextCompare)

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

Regards,

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

"Eric" wrote in message
...
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