Code to change formulae on worksheet?
 

Hi all i'm trying to create some code that will change all the formulae
on a certain sheet because i am using the Indirect function i dont want
to have to change 3000 formulae manually so i was hoping i could get
this right......but i am having trouble.....Any ideas?

Regards,
Simon

Sub frmlaChng()
Dim mycell
Dim rng As Range
Dim r
Set rng = Range("PriceForm")
With Sheets("summary sheet")
r = "F" & mycell.Row.Number
For Each mycell In rng
mycell.Text = "=VLOOKUP(INDIRECT(" & r & "),EventList,2,FALSE)"
Next mycell
End With
End Sub


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=563936

Code to change formulae on worksheet?
 

It is very hard to tell. Can you post a sample spreadsheet so we can see
what you are trying to do

Matt


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=563936

Code to change formulae on worksheet?
 

Matt all i am trying to do is save me a lot of manual work by running
the code above, once run it should put this =VLOOKUP(INDIRECT(" F2
"),EventList,2,FALSE) in each cell in my named range except it will
read =VLOOKUP(INDIRECT(" F3 "),EventList,2,FALSE) on row 3 and "F4" on
row 4 thats why i tried to use =VLOOKUP(INDIRECT(" & r &
"),EventList,2,FALSE) where r would be exchanged for F and the row
number, it really doesnt matter what spreadsheet its working with as i
am trying to populate each cell in the named range using the code.

Regards,
Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=563936

Code to change formulae on worksheet?
 

Hi Simon

Try the following which l have slightly modified



Sub frmlaChng()
Dim mycell
Dim rng As Range
Dim r
Set rng = Range("PriceForm")
With Sheets("summary sheet")
For Each mycell In rng
r = "F" & mycell.Row
mycell.Formula = "=VLOOKUP(INDIRECT(" & r &
"),EventList,2,FALSE)"
Next mycell
End With
End Sub





Regards

Michael Beckinsale

Code to change formulae on worksheet?
 

Hi Micheal, thanks for the reply and mods..........i
worked!..........well so far as it put the required formula in all th
cells i wanted, the only trouble is all the formula have this loo
=VLOOKUP(INDIRECT(F1),eventlist,2,FALSE) which gives #Ref! but th
formula needs to look like thi
=VLOOKUP(INDIRECT("F1"),eventlist,2,FALSE), it's just the inverte
commas that are missing i tried adding double quotes but this gave a
error.......do you know how to fixx this?

Regards,
Simo

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=56393

Code to change formulae on worksheet?
 

Hi simon,

Sorry for not replying sooner but l have not visited the newsgroup
until now.

Try this

mycell.Formula = "=VLOOKUP(INDIRECT(" & Chr(34) & r & Chr(34) &
"),EventList,2,FALSE)"

let me know how it goes

Regards

Michael

Code to change formulae on worksheet?
 

Micheal, It worked a treat............thanks saved hours of manual
formula change!

Regards,
Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=563936