(1) VARIABLE IN POSTING CODE (2) LOOPING
 

1.In the following code I want to incorporate a formula to read $A$8 for
€œ24€� in
€œOffset(0, 24)€� In other words 24 will be in cell A8. I want the number to
be variable to care of multiple posting for different conditions. Any
assistance.

Set CopyRng = _
Sheets("Sheet2").Range("DH29,DH30,DH31,DH32,DH33,D H34")
Set DestRng = _
Sheets("SALES").Cells(65536, "D").End(xlUp).Offset(0, 24)
i = 0
For Each cell In CopyRng
DestRng.Offset(0, i).Value = cell
i = i + 2
Next

2.I have the following code which copies 38 different cells to another 38
cells.
Assistance required for a Loop formula only if a loop will complete the task
faster than the current code.

Range("DN27").Select
Selection.Copy
Range("DF3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("DP4").Select
Application.CutCopyMode = False
Selection.Copy
Range("DF4").Select
ActiveSheet.Paste
[ code repeated another 36 times]
End Sub

I do not know any VBA but have been able to put together some processes
after perusing the many closely related questions and solutions. Next project
to learn VBA.
Thank you.

--
Robert

(1) VARIABLE IN POSTING CODE (2) LOOPING
 

"Robert" wrote in message
...
1.In the following code I want to incorporate a formula to read $A$8 for
"24" in
"Offset(0, 24)" In other words 24 will be in cell A8. I want the number to
be variable to care of multiple posting for different conditions. Any
assistance.

Set CopyRng = _
Sheets("Sheet2").Range("DH29,DH30,DH31,DH32,DH33,D H34")
Set DestRng = _
With WorkSheets("SALES")
.Cells(Rows.Count, "D").End(xlUp).Offset(0, .Range("A38").Value)
End With
i = 0
For Each cell In CopyRng
DestRng.Offset(0, i).Value = cell
i = i + 2
Next


2.I have the following code which copies 38 different cells to another 38
cells.
Assistance required for a Loop formula only if a loop will complete the
task
faster than the current code.

I am not clear as to the rule for copying

Range("DN27").Copy
Range("DF3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
For i = 4 to 40
Range("DP" & i).Copy Range("DF" & i)
Next i

(1) VARIABLE IN POSTING CODE (2) LOOPING
 

Thanks to Bob for the response. After hours of trying managed to get what I
desired but with the following changes. Had to omit "With" before
WorkSheet)"SALES"),
remove dot before "Range("A38").Value and omit "End With".
--
Robert