Functions Trigger
 

Dear all,

I am eager to know how can the normal icons like "autosum" be triggered
(just like effect of clicking the icon) in VBA ?

Rgds,

Functions Trigger
 

Hi Frankie,

Try:

'=============
Public Sub DoAutoSum()
'/// Jim Rech
Dim x As CommandBarControl
Set x = CommandBars.FindControl(id:=226)
If Val(Application.Version) = 10 Then _
Set x = x.Controls(1)
x.Execute
If selection.Cells.Count = 1 Then
x.Execute
End If
End Sub
'<<=============


---
Regards,
Norman


"Frankie" wrote in message
...
Dear all,

I am eager to know how can the normal icons like "autosum" be triggered
(just like effect of clicking the icon) in VBA ?

Rgds,

Functions Trigger
 

Thanks Norman.
It works well.

How can I repeat this VBA command until all autosum formulae are set up in a
column where list of values are there. I want to insert the autosum
formulae with variable sum ranges.


Rgds,

"Norman Jones" wrote:

Hi Frankie,

Try:

'=============
Public Sub DoAutoSum()
'/// Jim Rech
Dim x As CommandBarControl
Set x = CommandBars.FindControl(id:=226)
If Val(Application.Version) = 10 Then _
Set x = x.Controls(1)
x.Execute
If selection.Cells.Count = 1 Then
x.Execute
End If
End Sub
'<<=============


---
Regards,
Norman


"Frankie" wrote in message
...
Dear all,

I am eager to know how can the normal icons like "autosum" be triggered
(just like effect of clicking the icon) in VBA ?

Rgds,

Functions Trigger
 

With limited information, here is a possibility:

Sub DoSums()
Dim StartColumn as Long, EndColumn as Long
Dim i as Long
StartColumn = 3
EndColumn = 12
for i = startcolumn to endcolumn
cells(rows.count,i).End(xlup)(2).Select
DoAutoSum
Loop
End Sub

Public Sub DoAutoSum()
'/// Jim Rech
Dim x As CommandBarControl
Set x = CommandBars.FindControl(id:=226)
If Val(Application.Version) = 10 Then _
Set x = x.Controls(1)
x.Execute
If selection.Cells.Count = 1 Then
x.Execute
End If
End Sub

--
Regards,
Tom Ogilvy

"Frankie" wrote:

Thanks Norman.
It works well.

How can I repeat this VBA command until all autosum formulae are set up in a
column where list of values are there. I want to insert the autosum
formulae with variable sum ranges.


Rgds,

"Norman Jones" wrote:

Hi Frankie,

Try:

'=============
Public Sub DoAutoSum()
'/// Jim Rech
Dim x As CommandBarControl
Set x = CommandBars.FindControl(id:=226)
If Val(Application.Version) = 10 Then _
Set x = x.Controls(1)
x.Execute
If selection.Cells.Count = 1 Then
x.Execute
End If
End Sub
'<<=============


---
Regards,
Norman


"Frankie" wrote in message
...
Dear all,

I am eager to know how can the normal icons like "autosum" be triggered
(just like effect of clicking the icon) in VBA ?

Rgds,

Functions Trigger
 

An error appeared "loop without do"
In fact, my spreadsheet has 2 columns only, column "A" contains wordings
"total" & "grouptotal" scattered in the column and column "B" a list of
values. I just want to insert an autosum formula adjacent to the
"total/grouptotal" located in column "A". Since the sum ranges vary &
grouptotals are required at times, I can only locate the total/subtotal
manually in column "A" & click autosum in column "B" where the sum ranges
appear. I wonder if VBA can automate this process.

Hope you can help on this.
Thanks & Rgds,

"Tom Ogilvy" wrote:

With limited information, here is a possibility:

Sub DoSums()
Dim StartColumn as Long, EndColumn as Long
Dim i as Long
StartColumn = 3
EndColumn = 12
for i = startcolumn to endcolumn
cells(rows.count,i).End(xlup)(2).Select
DoAutoSum
Loop
End Sub

Public Sub DoAutoSum()
'/// Jim Rech
Dim x As CommandBarControl
Set x = CommandBars.FindControl(id:=226)
If Val(Application.Version) = 10 Then _
Set x = x.Controls(1)
x.Execute
If selection.Cells.Count = 1 Then
x.Execute
End If
End Sub

--
Regards,
Tom Ogilvy

"Frankie" wrote:

Thanks Norman.
It works well.

How can I repeat this VBA command until all autosum formulae are set up in a
column where list of values are there. I want to insert the autosum
formulae with variable sum ranges.


Rgds,

"Norman Jones" wrote:

Hi Frankie,

Try:

'=============
Public Sub DoAutoSum()
'/// Jim Rech
Dim x As CommandBarControl
Set x = CommandBars.FindControl(id:=226)
If Val(Application.Version) = 10 Then _
Set x = x.Controls(1)
x.Execute
If selection.Cells.Count = 1 Then
x.Execute
End If
End Sub
'<<=============


---
Regards,
Norman


"Frankie" wrote in message
...
Dear all,

I am eager to know how can the normal icons like "autosum" be triggered
(just like effect of clicking the icon) in VBA ?

Rgds,

Functions Trigger
 

Loop should have been next, but it appears that isn't what you want anyway.

You can certainly loop through the column and find the cells that contain
total or grouptotal. Then put your sum in the cell to the right.

Here is some pseudo code to get you going.

set rng = Range("A2",Cells(rows.count,1).End(xlup))
for each cell in rng
if instr(1,cell,"total",vbTextcompare then
cell.offset(0,1).Select
' do your total
end if
Next

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy


"Frankie" wrote:

An error appeared "loop without do"
In fact, my spreadsheet has 2 columns only, column "A" contains wordings
"total" & "grouptotal" scattered in the column and column "B" a list of
values. I just want to insert an autosum formula adjacent to the
"total/grouptotal" located in column "A". Since the sum ranges vary &
grouptotals are required at times, I can only locate the total/subtotal
manually in column "A" & click autosum in column "B" where the sum ranges
appear. I wonder if VBA can automate this process.

Hope you can help on this.
Thanks & Rgds,

"Tom Ogilvy" wrote:

With limited information, here is a possibility:

Sub DoSums()
Dim StartColumn as Long, EndColumn as Long
Dim i as Long
StartColumn = 3
EndColumn = 12
for i = startcolumn to endcolumn
cells(rows.count,i).End(xlup)(2).Select
DoAutoSum
Loop
End Sub

Public Sub DoAutoSum()
'/// Jim Rech
Dim x As CommandBarControl
Set x = CommandBars.FindControl(id:=226)
If Val(Application.Version) = 10 Then _
Set x = x.Controls(1)
x.Execute
If selection.Cells.Count = 1 Then
x.Execute
End If
End Sub

--
Regards,
Tom Ogilvy

"Frankie" wrote:

Thanks Norman.
It works well.

How can I repeat this VBA command until all autosum formulae are set up in a
column where list of values are there. I want to insert the autosum
formulae with variable sum ranges.


Rgds,

"Norman Jones" wrote:

Hi Frankie,

Try:

'=============
Public Sub DoAutoSum()
'/// Jim Rech
Dim x As CommandBarControl
Set x = CommandBars.FindControl(id:=226)
If Val(Application.Version) = 10 Then _
Set x = x.Controls(1)
x.Execute
If selection.Cells.Count = 1 Then
x.Execute
End If
End Sub
'<<=============


---
Regards,
Norman


"Frankie" wrote in message
...
Dear all,

I am eager to know how can the normal icons like "autosum" be triggered
(just like effect of clicking the icon) in VBA ?

Rgds,

Functions Trigger
 

Fantastic. Super Thanks to Tom and Norman.

I love you guys. Have spent several nights to figure it out.

With very best regards,



"Tom Ogilvy" wrote:

Loop should have been next, but it appears that isn't what you want anyway.

You can certainly loop through the column and find the cells that contain
total or grouptotal. Then put your sum in the cell to the right.

Here is some pseudo code to get you going.

set rng = Range("A2",Cells(rows.count,1).End(xlup))
for each cell in rng
if instr(1,cell,"total",vbTextcompare then
cell.offset(0,1).Select
' do your total
end if
Next

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy


"Frankie" wrote:

An error appeared "loop without do"
In fact, my spreadsheet has 2 columns only, column "A" contains wordings
"total" & "grouptotal" scattered in the column and column "B" a list of
values. I just want to insert an autosum formula adjacent to the
"total/grouptotal" located in column "A". Since the sum ranges vary &
grouptotals are required at times, I can only locate the total/subtotal
manually in column "A" & click autosum in column "B" where the sum ranges
appear. I wonder if VBA can automate this process.

Hope you can help on this.
Thanks & Rgds,

"Tom Ogilvy" wrote:

With limited information, here is a possibility:

Sub DoSums()
Dim StartColumn as Long, EndColumn as Long
Dim i as Long
StartColumn = 3
EndColumn = 12
for i = startcolumn to endcolumn
cells(rows.count,i).End(xlup)(2).Select
DoAutoSum
Loop
End Sub

Public Sub DoAutoSum()
'/// Jim Rech
Dim x As CommandBarControl
Set x = CommandBars.FindControl(id:=226)
If Val(Application.Version) = 10 Then _
Set x = x.Controls(1)
x.Execute
If selection.Cells.Count = 1 Then
x.Execute
End If
End Sub

--
Regards,
Tom Ogilvy

"Frankie" wrote:

Thanks Norman.
It works well.

How can I repeat this VBA command until all autosum formulae are set up in a
column where list of values are there. I want to insert the autosum
formulae with variable sum ranges.


Rgds,

"Norman Jones" wrote:

Hi Frankie,

Try:

'=============
Public Sub DoAutoSum()
'/// Jim Rech
Dim x As CommandBarControl
Set x = CommandBars.FindControl(id:=226)
If Val(Application.Version) = 10 Then _
Set x = x.Controls(1)
x.Execute
If selection.Cells.Count = 1 Then
x.Execute
End If
End Sub
'<<=============


---
Regards,
Norman


"Frankie" wrote in message
...
Dear all,

I am eager to know how can the normal icons like "autosum" be triggered
(just like effect of clicking the icon) in VBA ?

Rgds,