PDA

View Full Version : Not able to use the find command in vba ,Please help



allsourav
08-01-2018, 02:14 AM
I have string s which contains subham$


Now using excel's built in command find i lie to know the position of the dollar symbol,there might be other ways but i like to use find in vba code and use a variable inside it


Dim s As String
s = "subham$"
Sheets("Sheet1").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "=FIND(""$""," & s & ")"


I am getting the error Application defined or object defined error,what am I doing wrong please help


Thanks in Advance
Sourav Bhattacharya

mana
08-01-2018, 04:45 AM
ActiveCell.FormulaR1C1 = "=FIND(""$"",""" & s & """)"

sdsurzh
08-01-2018, 06:26 AM
Hi Mana,

Suppose i want to refer a cell range (B1) then what would be the code.

Regards,
SK

nikki333
08-05-2018, 08:26 AM
There is no need for using .select


Sheets("Sheet1").Range("A1").Formula = "=FIND(""$"", B1)"

p45cal
08-05-2018, 02:46 PM
but i like to use find in vba code and use a variable inside it
s = "subham$"
x = Application.WorksheetFunction.Find("$", s)
referring to cell B1 instead:
y = Application.WorksheetFunction.Find("$", Range("B1"))

You don't always need both Application and WorksheetFunction, it could be:
y = WorksheetFunction.Find("$", Range("B1"))
or
y = Application.Find("$", Range("B1"))
but it's more common to use Instr:
x = InStr(s, "$")

allsourav
08-06-2018, 01:15 AM
Thanks so very much

Sourav Bhattacharya

p45cal
08-06-2018, 02:27 AM
oh groan, cross posted without telling us…