Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Tidy Up
Can anyone tidy up the Sub below for me please, all it does is takes
one value from formula in "Sheet1", and stores it as a value in "Usage". As you can see the Weekday command is used to decide which column is used to store the values. This Sub works, but my lack of VBA experience makes it very untidy. Thanks Pete Private Sub UsageSheet() Dim DailyPallets(17) As Long, DailyRejectBags(17) As Long, Ctr Ctr = 0 For Ctr = 1 To 17 DailyPallets(Ctr) = Sheets("Sheet1").Cells(61 + Ctr, 21).Value Sheets("Usage").Cells(7 + Ctr, Weekday(Sheets("Sheet1").Range("N2")) * 2).Value = DailyPallets(Ctr) Next Ctr Ctr = 0 For Ctr = 1 To 17 DailyRejectBags(Ctr) = Sheets("Sheet1").Cells(61 + Ctr, 23).Value Sheets("Usage").Cells(7 + Ctr, (Weekday(Sheets("Sheet1").Range("N2")) * 2) + 1).Value = DailyRejectBags(Ctr) Next Ctr End Sub |
#2
|
|||
|
|||
Pete,
No need to store your values or loop: you can take out the continuation characters and extra returns (put in to prevent damaging line wraps). Private Sub UsageSheet2() Sheets("Usage").Cells(8, Weekday(Sheets("Sheet1").Range("N2")) _ * 2).Resize(17, 1).Value = _ Sheets("Sheet1").Cells(62, 21).Resize(17, 1).Value Sheets("Usage").Cells(8, (Weekday(Sheets("Sheet1").Range("N2")) _ * 2) + 1).Resize(17, 1).Value = _ Sheets("Sheet1").Cells(62, 23).Resize(17, 1).Value End Sub HTH, Bernie MS Excel MVP "Pete" wrote in message ups.com... Can anyone tidy up the Sub below for me please, all it does is takes one value from formula in "Sheet1", and stores it as a value in "Usage". As you can see the Weekday command is used to decide which column is used to store the values. This Sub works, but my lack of VBA experience makes it very untidy. Thanks Pete Private Sub UsageSheet() Dim DailyPallets(17) As Long, DailyRejectBags(17) As Long, Ctr Ctr = 0 For Ctr = 1 To 17 DailyPallets(Ctr) = Sheets("Sheet1").Cells(61 + Ctr, 21).Value Sheets("Usage").Cells(7 + Ctr, Weekday(Sheets("Sheet1").Range("N2")) * 2).Value = DailyPallets(Ctr) Next Ctr Ctr = 0 For Ctr = 1 To 17 DailyRejectBags(Ctr) = Sheets("Sheet1").Cells(61 + Ctr, 23).Value Sheets("Usage").Cells(7 + Ctr, (Weekday(Sheets("Sheet1").Range("N2")) * 2) + 1).Value = DailyRejectBags(Ctr) Next Ctr End Sub |
#3
|
|||
|
|||
try
Private Sub UsageSheet() Dim DailyPallets(17) As Long, DailyRejectBags(17) As Long, Ctr dim Ctr as integer For Ctr = 1 To 17 tgtcol = Weekday(Sheets("Sheet1").Range("N2")) * 2) tgtrow = 7 +Ctr Sheets("Usage").Cells(tgtrow,tgtcol) = Sheets("Sheet1").Cells(61 + Ctr, 21) Sheets("Usage").Cells(tgtrow,tgtcol + 1) =Sheets("Sheet1").Cells(61 + Ctr,23) Next Ctr End Sub "Pete" wrote: Can anyone tidy up the Sub below for me please, all it does is takes one value from formula in "Sheet1", and stores it as a value in "Usage". As you can see the Weekday command is used to decide which column is used to store the values. This Sub works, but my lack of VBA experience makes it very untidy. Thanks Pete Private Sub UsageSheet() Dim DailyPallets(17) As Long, DailyRejectBags(17) As Long, Ctr Ctr = 0 For Ctr = 1 To 17 DailyPallets(Ctr) = Sheets("Sheet1").Cells(61 + Ctr, 21).Value Sheets("Usage").Cells(7 + Ctr, Weekday(Sheets("Sheet1").Range("N2")) * 2).Value = DailyPallets(Ctr) Next Ctr Ctr = 0 For Ctr = 1 To 17 DailyRejectBags(Ctr) = Sheets("Sheet1").Cells(61 + Ctr, 23).Value Sheets("Usage").Cells(7 + Ctr, (Weekday(Sheets("Sheet1").Range("N2")) * 2) + 1).Value = DailyRejectBags(Ctr) Next Ctr End Sub |
#4
|
|||
|
|||
Brilliant, thanks
Pete |
#5
|
|||
|
|||
This will be a bit more efficient:
Private Sub UsageSheet() Dim rDest As Range With Sheets("Sheet1") Set rDest = Sheets("Usage").Cells(8, _ WeekDay(.Range("N2").Value) * 2).Resize(17, 1) With .Cells(62, 21).Resize(17, 1) rDest.Value = .Value rDest.Offset(0, 1).Value = .Offset(0, 2).Value End With End With End Sub In article . com, "Pete" wrote: Can anyone tidy up the Sub below for me please, all it does is takes one value from formula in "Sheet1", and stores it as a value in "Usage". As you can see the Weekday command is used to decide which column is used to store the values. This Sub works, but my lack of VBA experience makes it very untidy. Thanks Pete Private Sub UsageSheet() Dim DailyPallets(17) As Long, DailyRejectBags(17) As Long, Ctr Ctr = 0 For Ctr = 1 To 17 DailyPallets(Ctr) = Sheets("Sheet1").Cells(61 + Ctr, 21).Value Sheets("Usage").Cells(7 + Ctr, Weekday(Sheets("Sheet1").Range("N2")) * 2).Value = DailyPallets(Ctr) Next Ctr Ctr = 0 For Ctr = 1 To 17 DailyRejectBags(Ctr) = Sheets("Sheet1").Cells(61 + Ctr, 23).Value Sheets("Usage").Cells(7 + Ctr, (Weekday(Sheets("Sheet1").Range("N2")) * 2) + 1).Value = DailyRejectBags(Ctr) Next Ctr End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|