Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
looking to simplify a recorded macro with code
Greetings All,
This is probably very simple and I'm probalby going to over explain it but here goes: I have the following code from a macro I recorded and I'm trying to find a way to simplify. The first line pulls a date field for C1. But after that I am hoping to repeat a pattern for C2 to C121 where values are placed in Column C as such: I'm retreiving information to paste into worksheet "Sheet1" from worksheet "Schedule". The information is pulled from rows 2 & 3 then rows 8 & 9, then 14&15..etc in that pattern (+6) from the previous row. And for each set of rows (2,3) for example, it pulls data from 3 adjacent columns (C,D,E). I don's see any D or E in the macro code, but it looks like C3,C4 and C5 represent those 3 columns. Below is the pasted macro code: ActiveCell.FormulaR1C1 = "=Schedule!R1C3" // Pulls date field Range("C2").Select ActiveCell.FormulaR1C1 = "=Schedule!R2C3" Range("C3").Select ActiveCell.FormulaR1C1 = "=Schedule!R3C3" Range("C4").Select ActiveCell.FormulaR1C1 = "=Schedule!R2C4" Range("C5").Select ActiveCell.FormulaR1C1 = "=Schedule!R3C4" Range("C6").Select ActiveCell.FormulaR1C1 = "=Schedule!R2C5" Range("C7").Select ActiveCell.FormulaR1C1 = "=Schedule!R3C5" Range("C8").Select ActiveCell.FormulaR1C1 = "=Schedule!R8C3" Range("C9").Select ActiveCell.FormulaR1C1 = "=Schedule!R9C3" Range("C10").Select ActiveCell.FormulaR1C1 = "=Schedule!R8C4" Range("C11").Select ActiveCell.FormulaR1C1 = "=Schedule!R9C4" Range("C12").Select ActiveCell.FormulaR1C1 = "=Schedule!R8C5" Range("C13").Select ActiveCell.FormulaR1C1 = "=Schedule!R9C5" The macro would continue on to finish at C121 Thank you. Any help is greatly appreciated, HM |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
looking to simplify a recorded macro with code
HM
Try this Sub PullFromSchedule() Dim i As Long, j As Long Dim Rng As Range Dim Sh As Worksheet Set Sh = ThisWorkbook.Sheets("Schedule") Set Rng = ThisWorkbook.Sheets("Sheet1").Range("C1") Rng.Formula = "=Schedule!C1" For i = 2 To 121 Step 6 For j = 3 To 5 Rng.Offset(i + j - 4, 0).Formula = _ "=Schedule!" & Sh.Cells(i, j).Address(0, 0) Rng.Offset(i + j - 1, 0).Formula = _ "=Schedule!" & Sh.Cells(i + 1, j).Address(0, 0) Next j Next i End Sub -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "HM" wrote in message ... Greetings All, This is probably very simple and I'm probalby going to over explain it but here goes: I have the following code from a macro I recorded and I'm trying to find a way to simplify. The first line pulls a date field for C1. But after that I am hoping to repeat a pattern for C2 to C121 where values are placed in Column C as such: I'm retreiving information to paste into worksheet "Sheet1" from worksheet "Schedule". The information is pulled from rows 2 & 3 then rows 8 & 9, then 14&15..etc in that pattern (+6) from the previous row. And for each set of rows (2,3) for example, it pulls data from 3 adjacent columns (C,D,E). I don's see any D or E in the macro code, but it looks like C3,C4 and C5 represent those 3 columns. Below is the pasted macro code: ActiveCell.FormulaR1C1 = "=Schedule!R1C3" // Pulls date field Range("C2").Select ActiveCell.FormulaR1C1 = "=Schedule!R2C3" Range("C3").Select ActiveCell.FormulaR1C1 = "=Schedule!R3C3" Range("C4").Select ActiveCell.FormulaR1C1 = "=Schedule!R2C4" Range("C5").Select ActiveCell.FormulaR1C1 = "=Schedule!R3C4" Range("C6").Select ActiveCell.FormulaR1C1 = "=Schedule!R2C5" Range("C7").Select ActiveCell.FormulaR1C1 = "=Schedule!R3C5" Range("C8").Select ActiveCell.FormulaR1C1 = "=Schedule!R8C3" Range("C9").Select ActiveCell.FormulaR1C1 = "=Schedule!R9C3" Range("C10").Select ActiveCell.FormulaR1C1 = "=Schedule!R8C4" Range("C11").Select ActiveCell.FormulaR1C1 = "=Schedule!R9C4" Range("C12").Select ActiveCell.FormulaR1C1 = "=Schedule!R8C5" Range("C13").Select ActiveCell.FormulaR1C1 = "=Schedule!R9C5" The macro would continue on to finish at C121 Thank you. Any help is greatly appreciated, HM |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
looking to simplify a recorded macro with code
Dick,
That's exactly what I was looking for! Thanks a lot! HM "Dick Kusleika" wrote in message ... HM Try this Sub PullFromSchedule() Dim i As Long, j As Long Dim Rng As Range Dim Sh As Worksheet Set Sh = ThisWorkbook.Sheets("Schedule") Set Rng = ThisWorkbook.Sheets("Sheet1").Range("C1") Rng.Formula = "=Schedule!C1" For i = 2 To 121 Step 6 For j = 3 To 5 Rng.Offset(i + j - 4, 0).Formula = _ "=Schedule!" & Sh.Cells(i, j).Address(0, 0) Rng.Offset(i + j - 1, 0).Formula = _ "=Schedule!" & Sh.Cells(i + 1, j).Address(0, 0) Next j Next i End Sub -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "HM" wrote in message ... Greetings All, This is probably very simple and I'm probalby going to over explain it but here goes: I have the following code from a macro I recorded and I'm trying to find a way to simplify. The first line pulls a date field for C1. But after that I am hoping to repeat a pattern for C2 to C121 where values are placed in Column C as such: I'm retreiving information to paste into worksheet "Sheet1" from worksheet "Schedule". The information is pulled from rows 2 & 3 then rows 8 & 9, then 14&15..etc in that pattern (+6) from the previous row. And for each set of rows (2,3) for example, it pulls data from 3 adjacent columns (C,D,E). I don's see any D or E in the macro code, but it looks like C3,C4 and C5 represent those 3 columns. Below is the pasted macro code: ActiveCell.FormulaR1C1 = "=Schedule!R1C3" // Pulls date field Range("C2").Select ActiveCell.FormulaR1C1 = "=Schedule!R2C3" Range("C3").Select ActiveCell.FormulaR1C1 = "=Schedule!R3C3" Range("C4").Select ActiveCell.FormulaR1C1 = "=Schedule!R2C4" Range("C5").Select ActiveCell.FormulaR1C1 = "=Schedule!R3C4" Range("C6").Select ActiveCell.FormulaR1C1 = "=Schedule!R2C5" Range("C7").Select ActiveCell.FormulaR1C1 = "=Schedule!R3C5" Range("C8").Select ActiveCell.FormulaR1C1 = "=Schedule!R8C3" Range("C9").Select ActiveCell.FormulaR1C1 = "=Schedule!R9C3" Range("C10").Select ActiveCell.FormulaR1C1 = "=Schedule!R8C4" Range("C11").Select ActiveCell.FormulaR1C1 = "=Schedule!R9C4" Range("C12").Select ActiveCell.FormulaR1C1 = "=Schedule!R8C5" Range("C13").Select ActiveCell.FormulaR1C1 = "=Schedule!R9C5" The macro would continue on to finish at C121 Thank you. Any help is greatly appreciated, HM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I simplify a VB code?????? | Excel Discussion (Misc queries) | |||
Simplify code | Excel Worksheet Functions | |||
Need to simplify code | Excel Discussion (Misc queries) | |||
simplify code | Excel Discussion (Misc queries) | |||
Help to simplify code. | Excel Programming |