How to use substitute function in macro?

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

Rick
Sat Mar 15 22:28:18 CDT 2008

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" <Eric@discussions.microsoft.com> wrote in message
news:6E0D3865-007A-48CA-8E78-95662E6968D2@microsoft.com...
> 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
Sat Mar 15 22:26:17 CDT 2008

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)

"Eric" <Eric@discussions.microsoft.com> wrote in message
news:6E0D3865-007A-48CA-8E78-95662E6968D2@microsoft.com...
> 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

Eric
Sun Mar 16 03:40:00 CDT 2008

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:="", _
> Compare:=vbTextCompare)
>
> Is that something you can work with?
> Post back if you have more questions.
> --------------------------
>
> Regards,
>
> Ron
> Microsoft MVP (Excel)
> (XL2003, Win XP)
>
> "Eric" <Eric@discussions.microsoft.com> wrote in message
> news:6E0D3865-007A-48CA-8E78-95662E6968D2@microsoft.com...
> > 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
Sun Mar 16 10:07:27 CDT 2008

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" <Eric@discussions.microsoft.com> wrote in message
news:D4E451F5-6B88-4362-BA1B-0CBC5EA9E358@microsoft.com...
> 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:="", _
>> Compare:=vbTextCompare)
>>
>> Is that something you can work with?
>> Post back if you have more questions.
>> --------------------------
>>
>> Regards,
>>
>> Ron
>> Microsoft MVP (Excel)
>> (XL2003, Win XP)
>>
>> "Eric" <Eric@discussions.microsoft.com> wrote in message
>> news:6E0D3865-007A-48CA-8E78-95662E6968D2@microsoft.com...
>> > 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
>>
>>
>>