0

Two questions 1) Can I autofill logical formulas to a cell using VBA and how (there is something wrong with my code) and 2)Can I autofill it only when data is entered and how?

The formula I want to put works when I put into a a cell but not VBA:

=IF(ISNUMBER(SEARCH("$",$A2)),"Scanner 2",IF(ISNUMBER(SEARCH("#",$A2)),"Scanner 1","Error"))

For the VBA I also used Relative Coordinates, did not work: 

Range("C2").Value="=IF(ISNUMBER(SEARCH('$',RC[-2])),'Scanner 2', IF(ISNUMBER(SEARCH('#',RC[-2])),'Scanner 1','Error'))"

Pictures that may help:

Picture of VBA and debug highlighted!

Picture of Spreadsheet

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
Arianne_2130p
  • 15
  • 5
  • 1
    try `Range("C2").FormulaR1C1="=IF(ISNUMBER(SEARCH('$',RC[-2])),'Scanner 2', IF(ISNUMBER(SEARCH('#',RC[-2])),'Scanner 1','Error'))"` – Sphinx Jul 10 '19 at 14:09
  • No, adding .Formula or .FormulaR1C1 did not work – Arianne_2130p Jul 10 '19 at 14:45
  • 1
    `Range("C2").formular1c1="=IF(ISNUMBER(SEARCH(""$"",RC[-2])),""Scanner 2"", IF(ISNUMBER(SEARCH(""#"",RC[-2])),""Scanner 1"",""Error""))"` – SJR Jul 10 '19 at 15:05
  • Hi SJR Thank you so much , the double quotations rather than singles and .Formula instead of .Value worked – Arianne_2130p Jul 10 '19 at 15:25

1 Answers1

1

The problem is a combination of the use of single quotes + you didn't add as . Formula:

So:

Range("C2").Formula = "=IF(ISNUMBER(SEARCH(""$"",$A2)),""Scanner 2"",IF(ISNUMBER(SEARCH(""#"",$A2)),""Scanner 1"",""Error""))"

Should work

Also, I see in your code you are using .Select, there are many ways to avoid using this. Check this link.

The below example will do the same thing:

With ThisWorkbook.Sheets("Sheet1")
    .Range("C2:C100").Formula = "=IF(ISNUMBER(SEARCH(""$"",$A2)),""Scanner 2"",IF(ISNUMBER(SEARCH(""#"",$A2)),""Scanner 1"",""Error""))"
End With

You'll notice Excel will auto-adapt the formula to the correct cell references.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Thank you so much JvdV, greatly appreciate! Would you know how I can have this calculate or formula executed automatically as i enter data into cell A? – Arianne_2130p Jul 10 '19 at 15:23
  • @Arianne_2130p, glad it helped you, please refer to [this](https://stackoverflow.com/help/someone-answers) link if you think this has answered your question. Edit: To calculate you could add a single line in my example code `.Calculate`. – JvdV Jul 10 '19 at 15:25