vba code for inserting formula  
Author Message
GeneMoffice





PostPosted: Sun Nov 04 12:11:36 PST 2007 Top

Excel >> vba code for inserting formula

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.

Excel392  
 
 
JE





PostPosted: Sun Nov 04 12:11:36 PST 2007 Top

Excel >> vba code for inserting formula The simplest, though not-very-efficient way, is

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





> 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.
 
 
Tim





PostPosted: Sun Nov 04 13:45:32 PST 2007 Top

Excel >> vba code for inserting formula
> The simplest, though not-very-efficient way, is
>
> Worksheets("Sheet1").Range("G2:G632").Formula = _
> "=AVERAGE(INDIRECT(B2))"
>

>
>
>

> > 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.- Hide quoted text -
>
> - 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





PostPosted: Sun Nov 04 21:36:46 PST 2007 Top

Excel >> vba code for inserting formula 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




>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.
>

 
 
David





PostPosted: Sun Nov 04 21:45:40 PST 2007 Top

Excel >> vba code for inserting formula 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,
>
> 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
>
>


>>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.
>>
>

 
 
Tim





PostPosted: Tue Nov 06 05:07:21 PST 2007 Top

Excel >> vba code for inserting formula Cool, will try that. I had thought about using case, thanks for the
syntax. That throws me alot.