Using Macro & offset to put in formulas
 

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

--
Message posted from http://www.ExcelForum.com

Using Macro & offset to put in formulas
 

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.

"miker1999 <" wrote:

Hello!

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

The problem I am having is that I am trying to use Offset to put the
formulas in to the various columns for the row and I am stuck on a
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 a
better way? Help!
Mike

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Using Macro & offset to put in formulas
 

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


"miker1999 " wrote in message
...
Hello!

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

The problem I am having is that I am trying to use Offset to put the
formulas in to the various columns for the row and I am stuck on a
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 a
better way? Help!
Mike


---
Message posted from http://www.ExcelForum.com/

Using Macro & offset to put in formulas
 

Mike

You may find these links instructive

http://www.dicks-blog.com/excel/2004...eet_formu.html
http://www.dicks-blog.com/excel/2004...t_formu_1.html

I generally use the Address property when building formulas, like

With Range("B80").Offset(0, 12)
.Formula = "=IF(" & _
.Offset(16, -13).Address(0, 0) & _
"="""",""""," & _
.Offset(16, 27).Address(0, 0) & _
")"
End With

which will return

=IF(A96="","",AO96)

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


"miker1999 " wrote in message
...
Hello!

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

The problem I am having is that I am trying to use Offset to put the
formulas in to the various columns for the row and I am stuck on a
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 a
better way? Help!
Mike


---
Message posted from http://www.ExcelForum.com/