Tidy Up

[Pete]

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

 

[Bernie Deitrick]

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

 

[bj]

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]

Brilliant, thanks

Pete

 

[JE McGimpsey]

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