insert Excel formula with VBA code

[Arcadian]

Hi all,

I have some VBA code wich inserts new rows. In one of the cells I want VBA to enter the following formula:

Range.Formula = "=IF(I2="";"";G2*VLOOKUP(I2;Brongegevens!B2:C7;2))"

But somehow I get errors. I suppose it has something to do with the quotes being part of the formula, but also part of the VBA code, but I don't know all the VBA rules to do this correctly. Google told me quotes need to be in quotes too, but I must be doing it wrong

Also, eventually I would like the row numbers in the formula to be equal to the variable NextFree. I assume it would look something like this:

"=IF(I" & NextFree & "="";"";G" & NextFree & "*VLOOKUP(I" & NextFree & ";Brongegevens!B2:C7;2))"

But again, I'm not sure of the correct syntax.

I hope someone can help me.

 

[t0ny84]

Hi

When using formulas in VBA you need to include additional quotations.

Also you are missing your range at the beginning.

Range.Formula should be Range ("your range"). Formula.

Range("YourRange").Formula = "=IF(I2="""","""",G2*VLOOKUP(I2,Brongegevens!B2:C7,2,FALSE))"

The below should assist.

Range("YourRange").Formula = "=IF(I" & NextFree & "="""","""",G" & NextFree & "*VLOOKUP(I" & NextFree & ",Brongegevens!B2:C7,2,FALSE))"

 

[Arcadian]

It works perfectly, thank you very much

 

[Arcadian]

I did a quick test, because I have the Dutch language setting active and it turns out VBA doesn't like it if I use the Dutch translation of the formula's.