vba code for inserting formula

[Tim]

I just can't make my code work. If have different text in B2:B632
that, based on what the text is, needs to write a formula in G2:G632.
There are about 30 different text's in B, and they are names of Named
Ranges. I was trying to write:
(Simplified)
If B2.value = 1stNamedRange Then G2.Formula =
"=Average(1stNamedRange)
If B3.value = 2ndNamedRange Then G3.Formula =
"=Average(2ndNamedRange)
If B4.value = 1stNamedRange Then G4.Formula =
"=Average(1stNamedRange) , etc.

I wanted an array (in vba) that loops through each cell in B2:B632,
then places the formula in G2:G632.

I tried "If c.Value = 1stNamedRange Then
Worksheets("Sheet1").Range("g2:g632").Formula =
"=Average(1stNamedRange)"

I keep getting the error "Else without If", but my last line is "End
if"

Note: I used names other than 1stNamedRange in my Named Ranges. I can
adjust the VBA, if someone could show me what I'm doing wrong. Also, I
didn't declare anything at the beginning of the VBA. I tried different
stuff, but guess I'm totally lost. Thanks.

 

[JE McGimpsey]

The simplest, though not-very-efficient way, is

Worksheets("Sheet1").Range("G2:G632").Formula = _
"=AVERAGE(INDIRECT(B2))"

 

[Tim]

The simplest, though not-very-efficient way, is

Worksheets("Sheet1").Range("G2:G632").Formula = _
"=AVERAGE(INDIRECT(B2))"







- Show quoted text -

Couldn't make that work, JE. Thanks. I should have said that B column
text and Named Range aren't exactly the same. I need "If B2:B632 =
certain text, then average this Named Range, if it's
a different certain text, the average that Named Range. I wanted to
use 30 if's, or 29 if's and an elseIf, since there's only 30 possible
formula's for Column G.

I have made workbook work in the past by inserting the formula's in
each cell, but this is an annual project, and B2:B632 change every
year, so I wanted to automate formula's in Column G.

 

[David Heaton]

Tim,

If i understand you correctly you could use Select Case....for example

for i =2 to 362
select case sheet1.range("b" & i).value
case 1
sheet1.range("g" & i).formula="=Average(" & sheet1.range("b" &
i) & ")"
case 2
case 3
etc
next i

Regards

David

 

[David Heaton]

I forgot to mention that instead of 'Case 1" , "Case 2", etc, you would
change it to be the name of the named ranges..ie. case "MyRange1",
"MyRange2" etc.

 

[Tim]

Cool, will try that. I had thought about using case, thanks for the
syntax. That throws me alot.