Today I discovered the global scripting possibilities in Excel.
Until now, in cell I2, I calculated the average of 3 values like this:

=GEMIDDELDE(F2:G2:H2)

'Gemiddelde' means 'average' in Dutch (I use a Dutch Excel).

I'd like to make the sheet do this on all rows. Any ideas?
It shouldn't be too hard I guess, but I'm not a VB programmer.

Thanks,

Albert

Hi Albert,

two possibilities: if you just want to do it once, you
could drag and drop it to the rows.... just click at the
bottom border of the cell I2 and drop down while you are
clicking until you reach the cell you want to end...

another possibility would be in VBA, if you need it more
often:

Sub dropdown()

For i = 1 To 35562

Range("I" & i).Formula = "=Gemiddelde(F" & i & ":g" & i
& ":h" & i & ")"
Next i

End Sub

Best

Markus

PS: gemiddelde and Mittelwert somehow correspond ;o)



-----Original Message-----
Today I discovered the global scripting possibilities in
Excel.
Until now, in cell I2, I calculated the average of 3
values like this:

=GEMIDDELDE(F2:G2:H2)

'Gemiddelde' means 'average' in Dutch (I use a Dutch
Excel).

I'd like to make the sheet do this on all rows. Any ideas?
It shouldn't be too hard I guess, but I'm not a VB
programmer.

Thanks,

Albert


.

or even

i = 35562
With Range("E2")
.Formula = "=GEMIDDELDE(F2:H2)"
.AutoFill Destination:=Range("E2:E" & i), Type:=xlFillDefault
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Markus Scheible" wrote in message
...
Hi Albert,

two possibilities: if you just want to do it once, you
could drag and drop it to the rows.... just click at the
bottom border of the cell I2 and drop down while you are
clicking until you reach the cell you want to end...

another possibility would be in VBA, if you need it more
often:

Sub dropdown()

For i = 1 To 35562

Range("I" & i).Formula = "=Gemiddelde(F" & i & ":g" & i
& ":h" & i & ")"
Next i

End Sub

Best

Markus

PS: gemiddelde and Mittelwert somehow correspond ;o)



-----Original Message-----
Today I discovered the global scripting possibilities in
Excel.
Until now, in cell I2, I calculated the average of 3
values like this:

=GEMIDDELDE(F2:G2:H2)

'Gemiddelde' means 'average' in Dutch (I use a Dutch
Excel).

I'd like to make the sheet do this on all rows. Any ideas?
It shouldn't be too hard I guess, but I'm not a VB
programmer.

Thanks,

Albert


.

Chapeau Bob ;o)


-----Original Message-----
or even

i = 35562
With Range("E2")
.Formula = "=GEMIDDELDE(F2:H2)"
.AutoFill Destination:=Range("E2:E" & i),
Type:=xlFillDefault
End With

i = 35562
With Range("E2")
.Formula = "=GEMIDDELDE(F2:H2)"
.AutoFill Destination:=Range("E2:E" & i), Type:=xlFillDefault
End With

Sub dropdown()
For i = 1 To 35562
Range("I" & i).Formula = "=Gemiddelde(F" & i & ":g" & i
& ":h" & i & ")"
Next i
End Sub

Thanks guys, this is excellent!

Albert

Albert,

Something I forgot to mention.

Apparently, when creating cell formulae using VBA, you should enter the
formula in English, with US English separators (such as a comma rather than
a semi-colon), and let Excel convert it to the local form. So in your
example, it seems that you should use

.Formula = "=AVERAGE(F2:H2)"

I would be interested in your experience.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Albert" wrote in message
...
i = 35562
With Range("E2")
.Formula = "=GEMIDDELDE(F2:H2)"
.AutoFill Destination:=Range("E2:E" & i), Type:=xlFillDefault
End With

Sub dropdown()
For i = 1 To 35562
Range("I" & i).Formula = "=Gemiddelde(F" & i & ":g" & i
& ":h" & i & ")"
Next i
End Sub

Thanks guys, this is excellent!

Albert

Hi Bob,

You're right about that, I had to change the formula to =Average.
I kinda expected this error, because formula's in cells are called
"formule's" in
my Dutch version. But in VB its Formula, like in the code you gave me.

So everything else in cells is different too. Like IF is ALS. I think it was
a big
mistake to translate it for cells and not for VB (and I mean: it shouldn't
have
been translated at all). This is very confusing.

If you need to know anything else, let me know!

Albert


"Bob Phillips" schreef in bericht
...
Albert,

Something I forgot to mention.

Apparently, when creating cell formulae using VBA, you should enter the
formula in English, with US English separators (such as a comma rather
than
a semi-colon), and let Excel convert it to the local form. So in your
example, it seems that you should use

.Formula = "=AVERAGE(F2:H2)"

I would be interested in your experience.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Albert" wrote in message
...
i = 35562
With Range("E2")
.Formula = "=GEMIDDELDE(F2:H2)"
.AutoFill Destination:=Range("E2:E" & i), Type:=xlFillDefault
End With

Sub dropdown()
For i = 1 To 35562
Range("I" & i).Formula = "=Gemiddelde(F" & i & ":g" & i
& ":h" & i & ")"
Next i
End Sub

Thanks guys, this is excellent!

Albert

"Markus Scheible" wrote in message
...

PS: gemiddelde and Mittelwert somehow correspond ;o)

Markus,

After adding a reply to Albert, it brought me back to this statement. As I
recall MITTELWERT is the German AVERAGE function, so could you clarify for
my understanding what you meant by this statement?

Many Thanks

Bob

Hi Bob,

this was just my note that the Dutch and the German
language sometimes have very close wording - and because
Albert seems to be from the Netherlands and I am from
Germany I thought about it ;o)

Sorry to confuse you...

Best

Markus



-----Original Message-----

"Markus Scheible"
wrote in message
...

PS: gemiddelde and Mittelwert somehow correspond ;o)

Markus,

After adding a reply to Albert, it brought me back to
this statement. As I
recall MITTELWERT is the German AVERAGE function, so
could you clarify for
my understanding what you meant by this statement?

Many Thanks

Bob


.

Hi Markus,

It wasn't confusion, I just thought that maybe there was something else here
I didn't know about ;-)

Thanks anyway

Bob


"Markus Scheible" wrote in message
...
Hi Bob,

this was just my note that the Dutch and the German
language sometimes have very close wording - and because
Albert seems to be from the Netherlands and I am from
Germany I thought about it ;o)

Sorry to confuse you...

Best

Markus



-----Original Message-----

"Markus Scheible"
wrote in message
...

PS: gemiddelde and Mittelwert somehow correspond ;o)

Markus,

After adding a reply to Albert, it brought me back to
this statement. As I
recall MITTELWERT is the German AVERAGE function, so
could you clarify for
my understanding what you meant by this statement?

Many Thanks

Bob


.

Hi Bob,

did you really bought that? It seems to me that you nearly
know everything about XL... btw: why aren't you MVP yet?

Best

Markus



-----Original Message-----
Hi Markus,

It wasn't confusion, I just thought that maybe there was
something else here
I didn't know about ;-)

Thanks anyway

Bob


"Markus Scheible"
wrote in message
...
Hi Bob,

this was just my note that the Dutch and the German
language sometimes have very close wording - and because
Albert seems to be from the Netherlands and I am from
Germany I thought about it ;o)

Sorry to confuse you...

Best

Markus



-----Original Message-----

"Markus Scheible"
wrote in message
...

PS: gemiddelde and Mittelwert somehow correspond ;o)

Markus,

After adding a reply to Albert, it brought me back to
this statement. As I
recall MITTELWERT is the German AVERAGE function, so
could you clarify for
my understanding what you meant by this statement?

Many Thanks

Bob


.



.