0

Im trying to use VBA to insert this formula

=IF(OR(J2="S06",J2="S07", J2="S08"), "Yes", "No"

into a range of cells, where the value of J can go from J2, to J(i), i being the last row with data in it, I however cant get that formula to work within VBA. I have a similar one in the same macro that looks like this

For i = 2 To LastRow
   truefalse = "=LEFT(A" & i & ",3)"
   Range("J" & i).Value = truefalse
Next i

This one works fine, but I cant get the variable on this to set properly, no matter what I try. Any help would be greatly appreciated.

SDev95
  • 11
  • 3
  • 2
    You don't need a loop to do this. `Range("J2:J" & LastRow).Formula = "=LEFT(A2,3)"`. – BigBen Aug 05 '22 at 14:21
  • Well, what did you you try with that formula? Note you would use `""` for each single `"` in excel, to allow VBA to input an actual mark. You can paste that to a range, where the first row of the range matches your formula numbers, and as it goes down the range, will offset any non-fixed values, e.g., `$J1` would update each row and not column as it moves over the range. – Cyril Aug 05 '22 at 14:23
  • With your original formula, something like: `Range("K2:K" & LastRow).Formula = "=IF(OR(J2=""S06"",J2=""S07"", J2=""S08""), ""Yes"", ""No"")"` – BigBen Aug 05 '22 at 14:25
  • Will that change the value of J2 along to J3 and so on? – SDev95 Aug 05 '22 at 14:26
  • 1
    `Range.Formula = "=IF(OR(J2={""S06"",""S07"",""S08""}),""Yes"",""No"")"` – Scott Craner Aug 05 '22 at 14:26
  • "Will that change the value of J2 along to J3 and so on?" - yes. – BigBen Aug 05 '22 at 14:27

0 Answers0