Using VBA to insert dynamic formulas into a worksheet.

[Dave]

Hi,

I hope someone has a little patience with me on this one and can help.
Here goes...

I'm trying to use VBA to insert formulas into a defined range on the
worksheet. I'm doing this so the user cannot fiddle with the formulas
if they open the workbook and disable the macros ie. no formulas will
be visible to them to mess with.

This is what the formula looked like when it was embedded in the
worksheet...

=IF(D17 <>"",VLOOKUP(D17,Food_Prices,2,FALSE),"")

I changed it in my VBA to look like this...

Range("Unit").Formula =
"=IF(ISBLANK(D17),"""",VLOOKUP(D17,Food_Prices,2,FALSE))"

No problem so far - only that it pastes in the same formula into evry
cell in the range ie. they ALL have D17 in. So I set up a loop to try
and change the addresses like this...

For Each unit_cell In Range("Unit")

unit_cell.Select
addr = ActiveCell.Offset(-1).Address

unit_cell.Formula =
"=IF(ISBLANK(VALUE(addr)),"""",VLOOKUP(D17,Food_Prices,2,FALSE))"

Next

Yep, you guessed it - 2 hours later and all I've ended up with is this
in every cell in the range...

=IF(ISBLANK(VALUE(addr)),"",VLOOKUP(D17,Food_Prices,2,FALSE))

I'm now running out of ideas - can someone please show me how to
change the actual cell address depending on where the actual address
of my named range is ?

I hope that made sense.

Many thanks,
Dave

 

[Don Guillett]

try it this way. If you see TWO .. in front of formula, change to ONE

Sub putformulas()
With Range("Unit")
.Formula = _
"=IF(ISBLANK(D17),"""",VLOOKUP(D17,Food_Prices,2,FALSE))"
End With
End Sub

 

[Guest]

Your original code worked fine for me (I pasted into a module with no
changes).

Range("Unit").Formula =
"=IF(ISBLANK(D17),"""",VLOOKUP(D17,Food_Prices,2,FALSE))"

If "Unit" was E17:E27, the formulae used D1727. What is "Unit" defined as?

You could try putting the formula in the first cell and then fill the range.
Range("Unit").Cells(1).Formula = _
"=IF(ISBLANK(D17),"""",VLOOKUP(D17,Food_Prices,2,FALSE))"
Range("Unit").FillDown

Or copy it
Range("Unit").Cells(1).Formula = _
"=IF(ISBLANK(D17),"""",VLOOKUP(D17,Food_Prices,2,FALSE))"
Range("Unit").Cells(1).Copy Range("Unit")


Alternatively, worksheet protection might be an option. Go to
Format/Cells/Protection and lock the cells you want protected and unlock the
cells you want people to be able to change (it may be easier to select all
cells using the gray box above row1 and to the left of column A and unlocking
all of the cells, then select the cells you want protected and lock them).
Then apply worksheet protection (Tools/Protection/Protect Sheet). The locked
cells cannot be changed unless worksheet protection is removed. Just be
aware that worksheet protection can be broken by a macro (easily found w/an
internet search), so its not foolproof.

 

[Tom Ogilvy]

Sub BB()
Range("Unit").Formula = _
"=IF(ISBLANK(D17),"""",VLOOKUP(D17,Food_Prices,2,FALSE))"

End Sub

should adjust the D17 for each row as long as the cells are not formatted as
text - so if the formula appears in the cell as a formula and not a text
string, the D17 should adjust, same as if you manually copied it down. The
code worked fine for me.

 

[Dave]

Thanks for all the replies.

And looking at them, I realise I made a mess of my original request -
in that, I cannot guarantee that D17 will be the first cell - that's
why I wanted to use the named range ie. Unit.

Yes it does work with D17 hard coded in, but if the user inserts rows
into my named range of 'Unit' I cannot use D17 - I need to work out
where the range now starts and code it as per...

No problem so far - only that it pastes in the same formula into evry
cell in the range ie. they ALL have D17 in. So I set up a loop to try
and change the addresses like this...

For Each unit_cell In Range("Unit")

unit_cell.Select
addr = ActiveCell.Offset(-1).Address

unit_cell.Formula =
"=IF(ISBLANK(VALUE(addr)),"""",VLOOKUP(D17,Food_Prices,2,FALSE))"

Next

So what I think I need is some way of working out where the range Unit
starts and inserting the start address into my formula.

At the moment 'Unit' does start at D17 - but it may possible change to
D20 or D46 - am I making sense?

I'm sorry to be such a pain.

Thanks again,
Dave

 

[Tom Ogilvy]

Sub BB()
Dim n as Long
n = Range("Unit")(1).Row
Range("Unit").Formula = _
"=IF(ISBLANK(D" & n & "),"""",VLOOKUP(D" &
n & ",Food_Prices,2,FALSE))"

End Sub

 

[Dave]

On Sat, 6 Jan 2007 16:10:14 -0500, "Tom Ogilvy" <[email protected]>
wrote:

Thanks Tom.

 

[Guest]

Assuming it is possible they could also insert columns, moving "Unit" to the
right or left, you could try:


Sub test()
Dim strAddress As String

On Error Resume Next
strAddress = Range("Unit")(1)(1, 0).Address(False, False)

If Len(strAddress) > 0 Then _
Range("Unit").Formula = _
"=IF(ISBLANK(" & strAddress & _
"),"""",VLOOKUP(" & strAddress & _
",Food_Prices,2,FALSE))"

End Sub