Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Shorter Sub
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default Shorter Sub
Is there an easier way to achieve this? All it does it
take the resulting value from the formula in one column
and stores it as a Value in the next Column.

I need to keep it as a value, as it accumulates
throughout the week, and the sheet it comes from is
cleared every day.

Hope you can understand what I mean, I am very
inexperienced with VBA

Pete

Private Sub MinorStops()
Dim TotalBagshooter(16) As Long, DailyBagShooter(16) As
Long
Dim TotalDischargeBelt(16) As Long, DailyDischargeBelt
(16) As Long
Dim TotalFlatBeltConv(16) As Long, DailyFlatBeltConv(16)
As Long
Dim TotalBagHeight(16) As Long, DailyBagHeight(16) As Long
Dim TotalProfileBoard(16) As Long, DailyProfileBoard(16)
As Long
Dim Ctr
Ctr = 0

For Ctr = 1 To 16
DailyBagShooter(Ctr) = Sheets("MinorStops").Cells(1 +
Ctr, 2).Value
TotalBagshooter(Ctr) = Sheets("MinorStops").Cells(1 +
Ctr, 3).Value
TotalBagshooter(Ctr) = TotalBagshooter(Ctr) +
DailyBagShooter(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 3).Value =
TotalBagshooter(Ctr)
Next Ctr

Ctr = 0
For Ctr = 1 To 16
DailyDischargeBelt(Ctr) = Sheets("MinorStops").Cells
(1 + Ctr, 4).Value
TotalDischargeBelt(Ctr) = Sheets("MinorStops").Cells
(1 + Ctr, 5).Value
TotalDischargeBelt(Ctr) = TotalDischargeBelt(Ctr) +
DailyDischargeBelt(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 5).Value =
TotalDischargeBelt(Ctr)
Next Ctr

Ctr = 0
For Ctr = 1 To 16
DailyFlatBeltConv(Ctr) = Sheets("MinorStops").Cells(1
+ Ctr, 6).Value
TotalFlatBeltConv(Ctr) = Sheets("MinorStops").Cells(1
+ Ctr, 7).Value
TotalFlatBeltConv(Ctr) = TotalFlatBeltConv(Ctr) +
DailyFlatBeltConv(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 7).Value =
TotalFlatBeltConv(Ctr)
Next Ctr

Ctr = 0
For Ctr = 1 To 16
DailyBagHeight(Ctr) = Sheets("MinorStops").Cells(1 +
Ctr, 8).Value
TotalBagHeight(Ctr) = Sheets("MinorStops").Cells(1 +
Ctr, 9).Value
TotalBagHeight(Ctr) = TotalBagHeight(Ctr) +
DailyBagHeight(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 9).Value =
TotalBagHeight(Ctr)
Next Ctr

Ctr = 0
For Ctr = 1 To 16
DailyProfileBoard(Ctr) = Sheets("MinorStops").Cells(1
+ Ctr, 10).Value
TotalProfileBoard(Ctr) = Sheets("MinorStops").Cells(1
+ Ctr, 11).Value
TotalProfileBoard(Ctr) = TotalProfileBoard(Ctr) +
DailyProfileBoard(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 11).Value =
TotalProfileBoard(Ctr)
Next Ctr

End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 339
Default Shorter Sub

"Pete" wrote in message
...
Is there an easier way to achieve this? All it does it
take the resulting value from the formula in one column
and stores it as a Value in the next Column.

I need to keep it as a value, as it accumulates
throughout the week, and the sheet it comes from is
cleared every day.

Hope you can understand what I mean, I am very
inexperienced with VBA

Pete

Private Sub MinorStops()
Dim TotalBagshooter(16) As Long, DailyBagShooter(16) As
Long
Dim TotalDischargeBelt(16) As Long, DailyDischargeBelt
(16) As Long
Dim TotalFlatBeltConv(16) As Long, DailyFlatBeltConv(16)
As Long
Dim TotalBagHeight(16) As Long, DailyBagHeight(16) As Long
Dim TotalProfileBoard(16) As Long, DailyProfileBoard(16)
As Long
Dim Ctr
DailyBagShooter(Ctr) = Sheets("MinorStops").Cells(1 +
Ctr, 2).Value
TotalBagshooter(Ctr) = Sheets("MinorStops").Cells(1 +
Ctr, 3).Value
TotalBagshooter(Ctr) = TotalBagshooter(Ctr) +
DailyBagShooter(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 3).Value =
TotalBagshooter(Ctr)Ctr = 0

For Ctr = 1 To 16

Next Ctr

Ctr = 0
For Ctr = 1 To 16
DailyDischargeBelt(Ctr) = Sheets("MinorStops").Cells
(1 + Ctr, 4).Value
TotalDischargeBelt(Ctr) = Sheets("MinorStops").Cells
(1 + Ctr, 5).Value
TotalDischargeBelt(Ctr) = TotalDischargeBelt(Ctr) +
DailyDischargeBelt(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 5).Value =
TotalDischargeBelt(Ctr)
Next Ctr

Ctr = 0
For Ctr = 1 To 16
DailyFlatBeltConv(Ctr) = Sheets("MinorStops").Cells(1
+ Ctr, 6).Value
TotalFlatBeltConv(Ctr) = Sheets("MinorStops").Cells(1
+ Ctr, 7).Value
TotalFlatBeltConv(Ctr) = TotalFlatBeltConv(Ctr) +
DailyFlatBeltConv(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 7).Value =
TotalFlatBeltConv(Ctr)
Next Ctr

Ctr = 0
For Ctr = 1 To 16
DailyBagHeight(Ctr) = Sheets("MinorStops").Cells(1 +
Ctr, 8).Value
TotalBagHeight(Ctr) = Sheets("MinorStops").Cells(1 +
Ctr, 9).Value
TotalBagHeight(Ctr) = TotalBagHeight(Ctr) +
DailyBagHeight(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 9).Value =
TotalBagHeight(Ctr)
Next Ctr

Ctr = 0
For Ctr = 1 To 16
DailyProfileBoard(Ctr) = Sheets("MinorStops").Cells(1
+ Ctr, 10).Value
TotalProfileBoard(Ctr) = Sheets("MinorStops").Cells(1
+ Ctr, 11).Value
TotalProfileBoard(Ctr) = TotalProfileBoard(Ctr) +
DailyProfileBoard(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 11).Value =
TotalProfileBoard(Ctr)
Next Ctr

End Sub

It seems you are doing pretty much the same thing 5 times. This means that
you could use a nested loop

For i = 1 to 5
Ctr = 0
For Ctr = 1 To 16
'Your code here. Some of the constants should be calculated from the
varaiable i
Next Ctr
Next i

/Fredrik


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,071
Default Shorter Sub
There are other ways that better leverage the XL object model that
would be more appropriate if your data set was really large. But, as
things stand:

Option Explicit

Sub CumulativeSave(SrcCells As Range)
Dim i As Long
For i = 1 To SrcCells.Rows.Count
SrcCells.Offset(0, 1)(i).Value = _
SrcCells.Offset(0, 1)(i).Value + SrcCells(i).Value
Next i
End Sub
Sub MinorStops()
Dim Ctr As Byte
For Ctr = 1 To 5
CumulativeSave _
Sheets("MinorStops").Cells(2, (Ctr - 1) * 2 + 2) _
.Resize(16, 1)
Next Ctr
End Sub


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Is there an easier way to achieve this? All it does it
take the resulting value from the formula in one column
and stores it as a Value in the next Column.

I need to keep it as a value, as it accumulates
throughout the week, and the sheet it comes from is
cleared every day.

Hope you can understand what I mean, I am very
inexperienced with VBA

Pete

Private Sub MinorStops()
Dim TotalBagshooter(16) As Long, DailyBagShooter(16) As
Long
Dim TotalDischargeBelt(16) As Long, DailyDischargeBelt
(16) As Long
Dim TotalFlatBeltConv(16) As Long, DailyFlatBeltConv(16)
As Long
Dim TotalBagHeight(16) As Long, DailyBagHeight(16) As Long
Dim TotalProfileBoard(16) As Long, DailyProfileBoard(16)
As Long
Dim Ctr
Ctr = 0

For Ctr = 1 To 16
DailyBagShooter(Ctr) = Sheets("MinorStops").Cells(1 +
Ctr, 2).Value
TotalBagshooter(Ctr) = Sheets("MinorStops").Cells(1 +
Ctr, 3).Value
TotalBagshooter(Ctr) = TotalBagshooter(Ctr) +
DailyBagShooter(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 3).Value =
TotalBagshooter(Ctr)
Next Ctr

Ctr = 0
For Ctr = 1 To 16
DailyDischargeBelt(Ctr) = Sheets("MinorStops").Cells
(1 + Ctr, 4).Value
TotalDischargeBelt(Ctr) = Sheets("MinorStops").Cells
(1 + Ctr, 5).Value
TotalDischargeBelt(Ctr) = TotalDischargeBelt(Ctr) +
DailyDischargeBelt(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 5).Value =
TotalDischargeBelt(Ctr)
Next Ctr

Ctr = 0
For Ctr = 1 To 16
DailyFlatBeltConv(Ctr) = Sheets("MinorStops").Cells(1
+ Ctr, 6).Value
TotalFlatBeltConv(Ctr) = Sheets("MinorStops").Cells(1
+ Ctr, 7).Value
TotalFlatBeltConv(Ctr) = TotalFlatBeltConv(Ctr) +
DailyFlatBeltConv(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 7).Value =
TotalFlatBeltConv(Ctr)
Next Ctr

Ctr = 0
For Ctr = 1 To 16
DailyBagHeight(Ctr) = Sheets("MinorStops").Cells(1 +
Ctr, 8).Value
TotalBagHeight(Ctr) = Sheets("MinorStops").Cells(1 +
Ctr, 9).Value
TotalBagHeight(Ctr) = TotalBagHeight(Ctr) +
DailyBagHeight(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 9).Value =
TotalBagHeight(Ctr)
Next Ctr

Ctr = 0
For Ctr = 1 To 16
DailyProfileBoard(Ctr) = Sheets("MinorStops").Cells(1
+ Ctr, 10).Value
TotalProfileBoard(Ctr) = Sheets("MinorStops").Cells(1
+ Ctr, 11).Value
TotalProfileBoard(Ctr) = TotalProfileBoard(Ctr) +
DailyProfileBoard(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 11).Value =
TotalProfileBoard(Ctr)
Next Ctr

End Sub

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default Shorter Sub
Hi,

Pardon my ignorance, but how would I initiate the
CumulativeSave Sub?

CumulativeSave("B2:B17")?

Pete

-----Original Message-----
There are other ways that better leverage the XL object
model that
would be more appropriate if your data set was really
large. But, as
things stand:

Option Explicit

Sub CumulativeSave(SrcCells As Range)
Dim i As Long
For i = 1 To SrcCells.Rows.Count
SrcCells.Offset(0, 1)(i).Value = _
SrcCells.Offset(0, 1)(i).Value + SrcCells
(i).Value
Next i
End Sub
Sub MinorStops()
Dim Ctr As Byte
For Ctr = 1 To 5
CumulativeSave _
Sheets("MinorStops").Cells(2, (Ctr - 1) * 2
+ 2) _
.Resize(16, 1)
Next Ctr
End Sub


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Is there an easier way to achieve this? All it does it
take the resulting value from the formula in one
column
and stores it as a Value in the next Column.

I need to keep it as a value, as it accumulates
throughout the week, and the sheet it comes from is
cleared every day.

Hope you can understand what I mean, I am very
inexperienced with VBA

Pete

Private Sub MinorStops()
Dim TotalBagshooter(16) As Long, DailyBagShooter(16)
As
Long
Dim TotalDischargeBelt(16) As Long, DailyDischargeBelt
(16) As Long
Dim TotalFlatBeltConv(16) As Long, DailyFlatBeltConv
(16)
As Long
Dim TotalBagHeight(16) As Long, DailyBagHeight(16) As
Long
Dim TotalProfileBoard(16) As Long, DailyProfileBoard
(16)
As Long
Dim Ctr
Ctr = 0

For Ctr = 1 To 16
DailyBagShooter(Ctr) = Sheets("MinorStops").Cells
(1 +
Ctr, 2).Value
TotalBagshooter(Ctr) = Sheets("MinorStops").Cells
(1 +
Ctr, 3).Value
TotalBagshooter(Ctr) = TotalBagshooter(Ctr) +
DailyBagShooter(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 3).Value =
TotalBagshooter(Ctr)
Next Ctr

Ctr = 0
For Ctr = 1 To 16
DailyDischargeBelt(Ctr) = Sheets
("MinorStops").Cells
(1 + Ctr, 4).Value
TotalDischargeBelt(Ctr) = Sheets
("MinorStops").Cells
(1 + Ctr, 5).Value
TotalDischargeBelt(Ctr) = TotalDischargeBelt(Ctr)
+
DailyDischargeBelt(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 5).Value =
TotalDischargeBelt(Ctr)
Next Ctr

Ctr = 0
For Ctr = 1 To 16
DailyFlatBeltConv(Ctr) = Sheets("MinorStops").Cells
(1
+ Ctr, 6).Value
TotalFlatBeltConv(Ctr) = Sheets("MinorStops").Cells
(1
+ Ctr, 7).Value
TotalFlatBeltConv(Ctr) = TotalFlatBeltConv(Ctr) +
DailyFlatBeltConv(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 7).Value =
TotalFlatBeltConv(Ctr)
Next Ctr

Ctr = 0
For Ctr = 1 To 16
DailyBagHeight(Ctr) = Sheets("MinorStops").Cells(1
+
Ctr, 8).Value
TotalBagHeight(Ctr) = Sheets("MinorStops").Cells(1
+
Ctr, 9).Value
TotalBagHeight(Ctr) = TotalBagHeight(Ctr) +
DailyBagHeight(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 9).Value =
TotalBagHeight(Ctr)
Next Ctr

Ctr = 0
For Ctr = 1 To 16
DailyProfileBoard(Ctr) = Sheets("MinorStops").Cells
(1
+ Ctr, 10).Value
TotalProfileBoard(Ctr) = Sheets("MinorStops").Cells
(1
+ Ctr, 11).Value
TotalProfileBoard(Ctr) = TotalProfileBoard(Ctr) +
DailyProfileBoard(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 11).Value =
TotalProfileBoard(Ctr)
Next Ctr

End Sub

.

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,071
Default Shorter Sub
You start MinorStops. It, in turn, calls the Cumulative sub.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi,

Pardon my ignorance, but how would I initiate the
CumulativeSave Sub?

CumulativeSave("B2:B17")?

Pete

-----Original Message-----
{snip}
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default Shorter Sub
Sorry, I have realised my mistake, thanks this works for
me now. Brilliant

Pete

-----Original Message-----
There are other ways that better leverage the XL object
model that
would be more appropriate if your data set was really
large. But, as
things stand:

Option Explicit

Sub CumulativeSave(SrcCells As Range)
Dim i As Long
For i = 1 To SrcCells.Rows.Count
SrcCells.Offset(0, 1)(i).Value = _
SrcCells.Offset(0, 1)(i).Value + SrcCells
(i).Value
Next i
End Sub
Sub MinorStops()
Dim Ctr As Byte
For Ctr = 1 To 5
CumulativeSave _
Sheets("MinorStops").Cells(2, (Ctr - 1) * 2
+ 2) _
.Resize(16, 1)
Next Ctr
End Sub


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Is there an easier way to achieve this? All it does it
take the resulting value from the formula in one
column
and stores it as a Value in the next Column.

I need to keep it as a value, as it accumulates
throughout the week, and the sheet it comes from is
cleared every day.

Hope you can understand what I mean, I am very
inexperienced with VBA

Pete

Private Sub MinorStops()
Dim TotalBagshooter(16) As Long, DailyBagShooter(16)
As
Long
Dim TotalDischargeBelt(16) As Long, DailyDischargeBelt
(16) As Long
Dim TotalFlatBeltConv(16) As Long, DailyFlatBeltConv
(16)
As Long
Dim TotalBagHeight(16) As Long, DailyBagHeight(16) As
Long
Dim TotalProfileBoard(16) As Long, DailyProfileBoard
(16)
As Long
Dim Ctr
Ctr = 0

For Ctr = 1 To 16
DailyBagShooter(Ctr) = Sheets("MinorStops").Cells
(1 +
Ctr, 2).Value
TotalBagshooter(Ctr) = Sheets("MinorStops").Cells
(1 +
Ctr, 3).Value
TotalBagshooter(Ctr) = TotalBagshooter(Ctr) +
DailyBagShooter(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 3).Value =
TotalBagshooter(Ctr)
Next Ctr

Ctr = 0
For Ctr = 1 To 16
DailyDischargeBelt(Ctr) = Sheets
("MinorStops").Cells
(1 + Ctr, 4).Value
TotalDischargeBelt(Ctr) = Sheets
("MinorStops").Cells
(1 + Ctr, 5).Value
TotalDischargeBelt(Ctr) = TotalDischargeBelt(Ctr)
+
DailyDischargeBelt(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 5).Value =
TotalDischargeBelt(Ctr)
Next Ctr

Ctr = 0
For Ctr = 1 To 16
DailyFlatBeltConv(Ctr) = Sheets("MinorStops").Cells
(1
+ Ctr, 6).Value
TotalFlatBeltConv(Ctr) = Sheets("MinorStops").Cells
(1
+ Ctr, 7).Value
TotalFlatBeltConv(Ctr) = TotalFlatBeltConv(Ctr) +
DailyFlatBeltConv(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 7).Value =
TotalFlatBeltConv(Ctr)
Next Ctr

Ctr = 0
For Ctr = 1 To 16
DailyBagHeight(Ctr) = Sheets("MinorStops").Cells(1
+
Ctr, 8).Value
TotalBagHeight(Ctr) = Sheets("MinorStops").Cells(1
+
Ctr, 9).Value
TotalBagHeight(Ctr) = TotalBagHeight(Ctr) +
DailyBagHeight(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 9).Value =
TotalBagHeight(Ctr)
Next Ctr

Ctr = 0
For Ctr = 1 To 16
DailyProfileBoard(Ctr) = Sheets("MinorStops").Cells
(1
+ Ctr, 10).Value
TotalProfileBoard(Ctr) = Sheets("MinorStops").Cells
(1
+ Ctr, 11).Value
TotalProfileBoard(Ctr) = TotalProfileBoard(Ctr) +
DailyProfileBoard(Ctr)
Sheets("MinorStops").Cells(1 + Ctr, 11).Value =
TotalProfileBoard(Ctr)
Next Ctr

End Sub

.

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default Shorter Sub
Don't suppose you could give me a run down on how it
works could you, I may need to adapt it for another part
of my sheet.

Thanks

Pete

-----Original Message-----
You start MinorStops. It, in turn, calls the Cumulative
sub.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi,

Pardon my ignorance, but how would I initiate the
CumulativeSave Sub?

CumulativeSave("B2:B17")?

Pete

-----Original Message-----
{snip}
.

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,071
Default Shorter Sub
Check XL VBA help for 'Calling Sub and Function Procedures' It does a
pretty good job of explaining subroutines/functions with arguments and
how to call them.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Don't suppose you could give me a run down on how it
works could you, I may need to adapt it for another part
of my sheet.

Thanks

Pete

-----Original Message-----
You start MinorStops. It, in turn, calls the Cumulative
sub.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi,

Pardon my ignorance, but how would I initiate the
CumulativeSave Sub?

CumulativeSave("B2:B17")?

Pete

-----Original Message-----
{snip}
.


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Shorter way Oldjay Excel Discussion (Misc queries) 1 May 5th 08 05:12 PM
I wonder if you can make this shorter T Excel Worksheet Functions 0 December 23rd 04 08:47 PM
Shorter and faster? Stu[_28_] Excel Programming 5 October 17th 03 09:07 PM
Shorter code Stu[_13_] Excel Programming 13 September 29th 03 11:27 PM
shorter way? Me ![_3_] Excel Programming 0 September 15th 03 06:12 PM


All times are GMT +1. The time now is 06:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement