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 |
How to use substitute function in macro?
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 |
How to use substitute function in macro?
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 |
How to use substitute function in macro?
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 |
How to use substitute function in macro?
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 |
All times are GMT +1. The time now is 07:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com