How to calculate this on all rows?
 

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

How to calculate this on all rows?
 

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


.

How to calculate this on all rows?
 

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


.

How to calculate this on all rows?
 

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

How to calculate this on all rows?
 

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

How to calculate this on all rows?
 

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

How to calculate this on all rows?
 

"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

How to calculate this on all rows?
 

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

How to calculate this on all rows?
 

Thanks Albert, that confirms what I had been told (I have an English
version, so cannot check). It's kinda neat that Excel translates it though
:-)

BTW, to get the English version, you can type the local name in a cell as a
proper formula, and then in the VB IDE, type this in the immediate window
?activecell.formula
and it gives you the English

Bob

"Albert" wrote in message
...
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

How to calculate this on all rows?
 

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


.

How to calculate this on all rows?
 

And once more, Bob,

I sometimes feel that it would be much better if Excel
uses the English expressions for formula even in the
cells, no matter which language Excel itself is... because
all the translations and the mental switching between the
VBA and the cell contents is sometimes very bothering...
for example: at university we have German Excel and
everytime I'm forced to write something there I have to
lookup what all the formula is called in German - because
sometimes its not an obvious translation like "Mittelwert"
and "Average" ... so I am happy to have English Excel
here...

Nevertheless, this has nothing to do with this thread, but
I felt I needed to write it out of my soul ;o)

Best

Markus


-----Original Message-----
Thanks Albert, that confirms what I had been told (I have
an English
version, so cannot check). It's kinda neat that Excel
translates it though
:-)

BTW, to get the English version, you can type the local
name in a cell as a
proper formula, and then in the VB IDE, type this in the
immediate window
?activecell.formula
and it gives you the English

Bob

"Albert" wrote in message
.. .
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








.

How to calculate this on all rows?
 

I hate it, but you're right, the way the translations are translated is
nice. :P

By the way, I'd like the sub:

Sub dropdown()
For i = 2 To 2976 '35562
Range("I" & i).Formula = "=Average(F" & i & ":g" & i & ":h" & i & ")"
Next i
End Sub

....To stop whenever ("A" & i) is empty.
You probably have the code in your mind right now. :P

Albert

Ps, Markus, most Dutch words sound like the German ones. ;)



"Bob Phillips" schreef in bericht
...
Thanks Albert, that confirms what I had been told (I have an English
version, so cannot check). It's kinda neat that Excel translates it though
:-)

BTW, to get the English version, you can type the local name in a cell as
a
proper formula, and then in the VB IDE, type this in the immediate window
?activecell.formula
and it gives you the English

Bob

How to calculate this on all rows?
 

Sub dropdown()
Dim cLastRow As Long
cLastRow = Cells(Rows.Count,"I").End(xlUp).Row
For i = 2 To cLastRow
Range("I" & i).Formula = "=Average(F" & i & ":g" & i & ":h" & i & ")"
Next i
End Sub


--

HTH

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


"Albert" wrote in message
...
I hate it, but you're right, the way the translations are translated is
nice. :P

By the way, I'd like the sub:

Sub dropdown()
For i = 2 To 2976 '35562
Range("I" & i).Formula = "=Average(F" & i & ":g" & i & ":h" & i & ")"
Next i
End Sub

...To stop whenever ("A" & i) is empty.
You probably have the code in your mind right now. :P

Albert

Ps, Markus, most Dutch words sound like the German ones. ;)



"Bob Phillips" schreef in bericht
...
Thanks Albert, that confirms what I had been told (I have an English
version, so cannot check). It's kinda neat that Excel translates it
though
:-)

BTW, to get the English version, you can type the local name in a cell
as
a
proper formula, and then in the VB IDE, type this in the immediate
window
?activecell.formula
and it gives you the English

Bob

How to calculate this on all rows?
 

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


.

How to calculate this on all rows?
 

Hi Albert,


By the way, I'd like the sub:

Sub dropdown()
For i = 2 To 2976 '35562
Range("I" & i).Formula = "=Average(F" & i & ":g" & i
& ":h" & i & ")"
Next i
End Sub


....To stop whenever ("A" & i) is empty.
You probably have the code in your mind right now. :P


..Range("A1", "A65532").SpecialCells(xlCellTypeBlanks).Cells
(1).Row

gives you the first empty cell as a number, you could use
this as the max for i like:


Sub dropdown()

For i = 2 To (Range("A1", "A65532").SpecialCells
(xlCellTypeBlanks).Cells
(1).Row - 1)

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

Next i

End Sub

But I personally would prefer Bobs method because its
shorter and I think it is faster to run, like it follows:


i = Range("A1", "A65532").SpecialCells
(xlCellTypeBlanks).Cells
(1).Row - 1)

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

BTW: Your homepage is really content-overloaded ;o)

Have a nice day!

Markus



Ps, Markus, most Dutch words sound like the German
ones. ;)



Ich weiß ;o) My boss is from the Netherlands...






"Bob Phillips"
schreef in bericht
...
Thanks Albert, that confirms what I had been told (I
have an English
version, so cannot check). It's kinda neat that Excel
translates it though
:-)

BTW, to get the English version, you can type the local
name in a cell as
a
proper formula, and then in the VB IDE, type this in
the immediate window
?activecell.formula
and it gives you the English

Bob



.

How to calculate this on all rows?
 

Thanks Bob, works excellent.
I expected only one line to be added, but this is even better.

Albert


"Bob Phillips" schreef in bericht
...
Sub dropdown()
Dim cLastRow As Long
cLastRow = Cells(Rows.Count,"I").End(xlUp).Row
For i = 2 To cLastRow
Range("I" & i).Formula = "=Average(F" & i & ":g" & i & ":h" & i & ")"
Next i
End Sub


--

HTH

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


"Albert" wrote in message
...
I hate it, but you're right, the way the translations are translated is
nice. :P

By the way, I'd like the sub:

Sub dropdown()
For i = 2 To 2976 '35562
Range("I" & i).Formula = "=Average(F" & i & ":g" & i & ":h" & i & ")"
Next i
End Sub

...To stop whenever ("A" & i) is empty.
You probably have the code in your mind right now. :P

Albert

Ps, Markus, most Dutch words sound like the German ones. ;)



"Bob Phillips" schreef in bericht
...
Thanks Albert, that confirms what I had been told (I have an English
version, so cannot check). It's kinda neat that Excel translates it
though
:-)

BTW, to get the English version, you can type the local name in a cell
as
a
proper formula, and then in the VB IDE, type this in the immediate
window
?activecell.formula
and it gives you the English

Bob

How to calculate this on all rows?
 

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


.



.

How to calculate this on all rows?
 

For i = 2 To (Range("A1",
"A65532").SpecialCells(xlCellTypeBlanks).Cells(1). Row - 1)

Thats what I meant in my response to Bob. ;)

Thanks,

Albert

How to calculate this on all rows?
 

Hi Albert,


-----Original Message-----
Thanks Bob, works excellent.
I expected only one line to be added, but this is even
better.

Albert

The difference is that Bob just defined an extra variable -
f.e. if you need it afterwards... its optional, sometimes
it shortens the codes but sometimes complicates it because
you get lost in fifty variables that you defined before...
and sometimes you simply don't find any more clever
variable names... but it depends if you want it or not...

Best

Markus



"Bob Phillips"
schreef in bericht
...
Sub dropdown()
Dim cLastRow As Long
cLastRow = Cells(Rows.Count,"I").End(xlUp).Row
For i = 2 To cLastRow
Range("I" & i).Formula = "=Average(F" & i & ":g" & i
& ":h" & i & ")"
Next i
End Sub


--

HTH

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


"Albert" wrote in message
...
I hate it, but you're right, the way the translations
are translated is
nice. :P

By the way, I'd like the sub:

Sub dropdown()
For i = 2 To 2976 '35562
Range("I" & i).Formula = "=Average(F" & i & ":g" &
i & ":h" & i & ")"
Next i
End Sub

...To stop whenever ("A" & i) is empty.
You probably have the code in your mind right now. :P

Albert

Ps, Markus, most Dutch words sound like the German
ones. ;)



"Bob Phillips"
schreef in bericht
...
Thanks Albert, that confirms what I had been told (I
have an English
version, so cannot check). It's kinda neat that
Excel translates it
though
:-)

BTW, to get the English version, you can type the
local name in a cell
as
a
proper formula, and then in the VB IDE, type this in
the immediate
window
?activecell.formula
and it gives you the English

Bob







.

How to calculate this on all rows?
 

"Markus Scheible" wrote in message
...
did you really bought that? It seems to me that you nearly
know everything about XL...

I wish! I only found about that VBA formula matter last week. I had the
inkling because as Albert says, VBA is always in English, but I didn't
appreciate that Excel converted from VBA English to Excel local.

How to calculate this on all rows?
 

Sub dropdown()
Dim cLastRow As Long
cLastRow = Cells(Rows.Count,"I").End(xlUp).Row
For i = 2 To cLastRow
Range("I" & i).Formula = "=Average(F" & i & ":g" & i & ":h" & i & ")"
Next i
End Sub

Suddenly it didn't work anymore, but it was probably
because you let it check for the last line in "I" while i
needed it to do that in "A".

'cLastRow = Cells(Rows.Count, "I").End(xlUp).Row
cLastRow = Cells(Rows.Count, "A").End(xlUp).Row

Works perfect now. I'm adding more stuff.

Albert