Solved: Offset? ActiveCell?

[Amanda1]

Hi

Just in case you all thought I had left, forgive me - husband has been in hospital so things have been a bit hectic.

I have now officially given up and am pleading for help, before I go totally insane. I cannot get a line in my macro to work - and I have tried almost every combination known to mankind, (except obviously for the right one)!

Scenario - worksheet will always remain constant in name - Col E will always be the relevant column. Col E will be populated with numbers and the amount of rows populated will continually differ after each population.

Required - I need a line that will go to the next empty cell in Col e after the populated data - then I want to put in a "SUM" function that will add all the populated data in Col E and obviously give the result in the empty cell immediately under the data. Dead Easy - but do you think I can get it right - I have tried FormulaArray, FormulaR1C1 and ActiveCell, Offset etc etc - not a chance.


This is my latest attempt - no laughing guys pls!!



[VBA]Sub summing()
'
' summing Macro
' Macro recorded 2005/12/04
'
'
Range("E1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaArray = "=Sum(Offset(ActiveCell,-1,0))"

End Sub [/VBA]

It is bringing me back a "NAME" error - you will all obviously know why, but I am clueless. I need to do it via script because recording it just puts the actual cell address in and of course that will change every time data is populated, which is daily.

Please give me a hint - then I'll kick myself.

Thanks & take care

Amanda

[Bob Phillips]

[vba]
Dim iLastRow As Long

iLastRow = Cells(Rows.Count, "E").End(xlUp).Row
Range("E" & iLastRow + 1).Formula = "=SUM(E1:E" & iLastRow & ")"
[/vba]

[Norie]

Does this do what you want?

Sub summing()
Dim LastRow As Long
	LastRow = Range("E" & Rows.Count).End(xlUp).Row
	
	Range("E" & LastRow + 1).Formula = "=Sum(E1:E" & LastRow & ")"
	 
End Sub

[mdmackillop]

Hi Amanda,
Best wishes to your husband, and one solution for yourself.
Regards
MD

[VBA]
[E1].End(xlDown).Offset(1, 0).Formula = _
"=Sum(E1:" & [E1].End(xlDown).Address(0, 0) & ")"
[/VBA]

[Amanda1]

S'wonderful, S'marvelous. (It's an age thing with Tony)

You are brilliant - thank you so much - you have both just been acting as Father Xmas. I've got to work out what you did in the codes, so that is my bed time reading - but in the meantime, my script now works!

Again, thanks.

[Amanda1]

Hi Malcolm,

I was just writing my previous note before your message came on - so my thanks to you too. (Did you get the snow?)

Take care

Amanda

[Desert Piranha]

[VBA]
[E1].End(xlDown).Offset(1, 0).Formula = _
"=Sum(E1:" & [E1].End(xlDown).Address(0, 0) & ")"
[/VBA]

Hi md,
Could you explain to me the brackets you have around [E1].
Why are they used there ?
Why not (E1) ?

Can you make this code to use selected column
instead of hard coded column ?
Something like:[VBA]
Selection.End(xlDown).Offset(1, 0).Formula = _
"=Sum(1:" & [1].End(xlDown).Address(0, 0) & ")"
[/VBA]

[mdmackillop]

Hi Dave,
[E1] is equivalent to Range("E1") eg you can use [E1].Select. Round brackets do not serve the same purpose.

You could use the code as you have written it, but in a more complex code, the active cell may not be in the correct column. Amanda's question was for a column E solution. The 3 solutions shown will all write the formula into the relevant cell in Column E without moving the active cell. The act of selecting cells, if there are a lot of them, slows down the code

Regards
MD

[mdmackillop]

Hi Amanda,
We did det a little snow, but thankfully it's all cleared away
Regards
Malcolm

[Zack Barresse]

Hi md,
Could you explain to me the brackets you have around [E1].
Why are they used there ?
Why not (E1) ?

In addition to what MD has already posted, check out this article by johnske...

http://www.vbaexpress.com/forum/arti...ticle&artid=37

[Desert Piranha]

Hi Dave,
[E1] is equivalent to Range("E1") eg you can use [E1].Select. Round brackets do not serve the same purpose.
Regards
MD

Hi,
md & firefytr, Thx for the enlightenment,
johnske, Thx for creating the article.
Amanda1, Please excuse me for butting in on your thread.

[Bob Phillips]

Hi,
md & firefytr, Thx for the enlightenment,
johnske, Thx for creating the article.
Amanda1, Please excuse me for butting in on your thread.

Don't use it. It is slower, it is not as intuitive (thus it is less maintainable), and it is not part of the object model (which means that it may be deprecated at any release without warning).

[johnske]

Don't use it. It is slower

This is simply not always true, it is sometimes slower, sometimes faster, but any differences are only around 1% and the tests show these differences can be simply due to processes running in the background. I've pasted the code used to test this as a comment below the main body of the article here: http://www.vbaexpress.com/forum/arti...ticle&artid=37 I suggest you test the truth of that statement for yourself. (EDIT Until I can get someone to change it for me, the code in the 1st testing procedure needs to be changed from "For M = 1 To 2" to "For M = 1 To 20")

it is not as intuitive...

What is that supposed to mean? Just because most ppl are introduced to conventional notation first doesn't make it more intuitive, I find it very intuitive. Like most things in VBA it's simply another tool for getting a job done and like anything else you just have to get used to all the tools at your disposal and use them where it's appropriate...

Note that it's use is also included in the MS Help files and the MS on-line Office97 VBA coding manual, and its usage is also mentioned in MSDN posts for useage various versions of Office from Office97 up to and including (from memory) Office 2003. So I don't see that it'll disappear in the near future.

[Bob Phillips]

This is simply not always true, it is sometimes slower, sometimes faster, but any differences are only around 1% and the tests show these differences can be simply due to processes running in the background.

I have also tested this many times, usually after seeing you post it as an example, and I can categorically state that as yetr I have NEVER see the shortcut form faster. In addition, I have never heard anyone claim that it is faster. That suggests to me that it really is slower. As you say, the difference is usually slight, but without any perceived benefit I cannot see any reason for using it. A perceived benefit would be a performance increase in the region of 10% to compensate for what you lose.

I did two test runs before writing this, one with 5 cycles of 10,000 loops, and one with 5 cycles of 20,000 loops. The first timed at 1-8% slower, the second at 1-6% slower. However, I have seen it 20% lower on other tests. Whilst background factors would suggest that the difference might be negligible, there is certainly no benefit, and it might be that the background factors favoured the shortcut, we cannot know.

What is that supposed to mean? Just because most ppl are introduced to conventional notation first doesn't make it more intuitive, I find it very intuitive. Like most things in VBA it's simply another tool for getting a job done and like anything else you just have to get used to all the tools at your disposal and use them where it's appropriate...

How many times have you seen anyone posting '... what does Range("A1").Value mean ...'? How many times have you see someone ask what [A1] means. That is part of what I mean.

Also, you don't see [A1] the object browser, you don't get intellisense, all of this detracts from its intuitiveness.

Note that it's use is also included in the MS Help files and the MS on-line Office97 VBA coding manual, and its usage is also mentioned in MSDN posts for useage various versions of Office from Office97 up to and including (from memory) Office 2003. So I don't see that it'll disappear in the near future.

The Help files, manuals and MSDN are generally written by technical writers not developers. I wonder if it is the developers documentation, as that is the best chance of it being protected? I don't see it necessarily disappearing in the near future, MS are not qui9ck to deprecate, but if it is not part of the official product (as I believe that it is not) then there exists the chance of it just disappearing, or even worse, changing how it does function.

I stand by everything I said. It is ugly syntax, and I believe it should be avoided.

[Amanda1]

I'm starting to regret that I asked how to do something in just one column.

Amanda

[Bob Phillips]

I'm starting to regret that I asked how to do something in just one column.

Amanda

You are right, we should have our own thread in the Pedantry forum

[Amanda1]

Ouch!