Q:

why does VBA put 'range' instead of just range

Using VBA to place a formula in a cell like so
    Range("H2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-6],CompanyUserEntries!B2:X" & URowCount & ",1,TRUE),"""")"

But Excel ends up turning it into this
=IFERROR(VLOOKUP(B2;CompanyUserEntries!'B2':'X12';1;TRUE);"")

the ' symbol around the range causes the formula not to work. Any idea how i can reference this range propperly without breaking my formula?

A:

You can't mix R1C1 and A1 notations in the same formula. Try using only R1C1 notation :
    ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-6],CompanyUserEntries!R2C2:R" & URowCount & "C24,1,TRUE),"""")"

