0

The below formula is working. But I need to pass var1 variable instead of string which is A in the formula.

Can someone pls guide how can I use a variable in vba?

Should I use it with & ?

var1 = Worksheets("Macro").Range("F10").Value

ActiveCell.FormulaR1C1 = _ "==IF(ISNUMBER(SEARCH("A",A2)),"A1","B")"

AM getting error if i try with
ActiveCell.FormulaR1C1 = _ "==IF(ISNUMBER(SEARCH(var1,A2)),"A1","B")"

enter image description here

GodKnows
  • 33
  • 6
  • `ActiveCell.Formula = "=IF(ISNUMBER(SEARCH(" & var1 & ",A2)),""A1"",""B"")"` – Scott Craner Jul 21 '22 at 15:55
  • @ScottCraner thanks, Its working but false statement is getting printed. =IF(ISNUMBER(SEARCH(@A,@'A2')),"A1","B") is in formula section, but A is present in the column A2, still false statement is getting printed – GodKnows Jul 21 '22 at 16:41
  • @ScottCraner If i run manually with =IF(ISNUMBER(SEARCH("A",A')),"A1","B") now A1 is getting printed. The @ symbol is getting generated in the formula , Is @ causing any problem ? – GodKnows Jul 21 '22 at 17:07
  • `ActiveCell.Formula2 = "=IF(ISNUMBER(SEARCH(" & var1 & ",A2)),""A1"",""B"")"` – Scott Craner Jul 21 '22 at 17:08
  • @ScottCraner yeah am using the same formula. Eventhough the value is present in the A2 column , False part (B) is getting printed. – GodKnows Jul 21 '22 at 17:12
  • are you using `ActiveCell.Formula2` instead of `ActiveCell.FormulaR1C1`? – Scott Craner Jul 21 '22 at 17:12
  • @ScottCraner Yeah.. ``` ActiveCell.Formula2 = _ "=IF(ISNUMBER(SEARCH(" & var1 & ",A2)),""A1"",""B"")" ``` Also i have attached the image for reference – GodKnows Jul 21 '22 at 17:17
  • 1
    try: `ActiveCell.Formula2 = "=IF(ISNUMBER(SEARCH(""" & var1 & """,A2)),""A1"",""B"")"` – Scott Craner Jul 21 '22 at 17:23
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/246654/discussion-between-godknows-and-scott-craner). – GodKnows Jul 21 '22 at 17:28
  • @ScottCraner Thank you so much for the quick help, it worked. Just for knowledge gaining, why it wasnt working earlier is it because of @ created in the excel formula? – GodKnows Jul 21 '22 at 17:30

0 Answers0