Recording a Macro to change a single cell using a range of cells.

[danilobiccari]

Hello,

I am trying to record a macro that can automate the following process:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DATE[/TD]
[TD]TOTAL[/TD]
[TD]1-Sep-16[/TD]
[TD]TOTAL (based indirectly on C1)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]2-Sep-16[/TD]
[TD]TOTAL (based indirectly on C2)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]3-Sep-16[/TD]
[TD]TOTAL (based indirectly on C3)[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD]30-Dec-17[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]

1. Change the DATE in A1 to the date in C1
2. Copy the TOTAL in B1 (a number based on the date in A1) to cell D1
3. Change the DATE in A1 to the date in C2
4. Copy the TOTAL in B1 to cell D2
5. Change the DATE in A1 to the date in C3
6. Copy the TOTAL in B1 to cell D3
... (etc.)

How do I go about doing this? I am having difficulty in keeping some cells as static and some as dynamic in this macro.

Any assistance would be greatly appreciated!

Thanks,

Danilo

 

[baitmaster]

Sub process()
Dim cl As Range
For Each cl In Range("C1:C###") ' need to set the date range here
    Range("A1") = cl
    cl.Offset(0, 1) = Range("B1")
Next cl
End Sub

 

[danilobiccari]

Thank you for the help. I should have probably mentioned that i'm not familiar with coding macros - I've only ever attempted to record macros.

So unfortunately I can't quite make sense of how to achieve the goal with your suggestion. But that's for me to find out I suppose.

Thanks again for the help anyway.

Sub process()
Dim cl As Range
For Each cl In Range("C1:C###") ' need to set the date range here
    Range("A1") = cl
    cl.Offset(0, 1) = Range("B1")
Next cl
End Sub

 

[baitmaster]

Easy. When you record a macro it gets stored in a VB module within your workbook. You can see this module by hitting Alt + F11 to open the VB Editor, and if necessary navigating to the appropriate code module using the project explorer window

This general code module is automatically created when you record a macro, but if you haven't recorded anything then it might not exist. If so, right-click on your Workbook in the VB project explorer window and select [Insert > Module]

Copy the code I wrote above and paste it there. You can then run it from the VB Editor or from your workbook