Using Macro & offset to put in formulas

M

miker1999

Hello!

I am creating a 'shared' workbook that users input data into and
would like to create a macro the user can use to put a new row in an
fill it with the required formula's.

The problem I am having is that I am trying to use Offset to put th
formulas in to the various columns for the row and I am stuck on
formula.

How would I use Offset for this formula:
=IF(A96="","",AO96&" - "&VLOOKUP(AQ96&AR96,CompLookUp,2,0))

This is what I tried:
.Offset(0, 12).Formula = "=IF(A" & .Row & "="","",AO" & .Row & "&"
"&VLOOKUP(AQ" & .Row & "&AR" & .Row & ",CompLookUp,2,0))"

It isn't working...Am I trying something too complex? Is there
better way? Help!
Mik
 
D

Dave Peterson

How about:

With ActiveCell 'whatever range???
.Offset(0, 12).Formula = "=IF(A" & .Row & "="""","""",AO" & .Row _
& "&"" - ""&VLOOKUP(AQ" & .Row & "&AR" & .Row & ",CompLookUp,2,0))"
End With

You have to double up on the double quotes used in formulas in the worksheet.
 
C

Cecilkumara Fernando

Mike,
this worked for me
Range("B1").Offset(4,0).Formula = _
"=IF(A" & range("A5").Row & "="""","""",AO" & _
range("A5").Row & "&""-""&VLOOKUP(AQ" & range("A5").Row _
& "&AR" & range("A5").Row & ",CompLookUp,2,0))"

I think you have to lode the .Row part to a variable first like
MyRow = range("A5").Row
and use it in vba coding

to get "" into the formula you must use """" in vba

Hope this will get you going

Cecil
 
Top