Code to change formulae on worksheet?

[Simon Lloyd]

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

 

[Mallycat]

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

Matt

 

[Simon Lloyd]

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

 

[michael.beckinsale]

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

 

[Simon Lloyd]

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

 

[michael.beckinsale]

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

 

[Simon Lloyd]

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

Regards,
Simon