0

I am currently working on an Excel VBA macro to automate spreadsheet formatting and formula filling.

I keep running into a type mismatch issue, with string and variant/string types.

The command to insert the formula (left side of = sign) is type variant/string while the formula itself is string type. How do i convert the referenced list/formula to a variant type, or the left side to a string type?

Can give more detail as necessary, Ive searched all over and cant find a solution. Thanks.

code in question:

Sub AddFormulas()

    lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row

    Range("G4").Formula = "=INDEX('wks'!$A$2:$A$411, MATCH(1, COUNTIF(F4, " * "&'wks'!$A$2:$A$411&" * "), 0))"
    Range("G4").AutoFill Range("G4:G" & lr)
End Sub

BigBen
  • 46,229
  • 7
  • 24
  • 40
Nathan Thomas
  • 1
  • 1
  • Please [edit] your question with your actual code, instead of just a description of it. – BigBen Sep 22 '20 at 18:12
  • i tried to upload a screenshot, and it said it would link it in the post, but didn't. lol. code incoming. – Nathan Thomas Sep 22 '20 at 18:15
  • `Range("G4:G" & lr).Formula = "=INDEX('wks'!$A$2:$A$411, MATCH(1, COUNTIF(F4, ""* ""&'wks'!$A$2:$A$411&"" * ""), 0))"`. You need to double up quotes, and you also don't need to `AutoFill`. – BigBen Sep 22 '20 at 18:20
  • Thanks! Why not autofill? I need the code copied down all rows of column G referencing the same row in column F. – Nathan Thomas Sep 22 '20 at 18:25
  • Excel will automatically update the relative references when you write the formula to the entire range, e.g. `F4` to `F5`, `F6`, `F7`, etc. So `AutoFill` is not actually needed. – BigBen Sep 22 '20 at 18:26
  • youre the man, thanks ben – Nathan Thomas Sep 22 '20 at 18:27

0 Answers0