Need Help with macro code modification
 

Greetings All,

Thanks alot Dick for the initial help. I thought I had it, but i was unable
to modify this to exactly work for me.


I have the following macro code which retrieves data from a second
worksheet:

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 19 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 - 3, 0).Formula = _
"=Schedule!" & Sh.Cells(i + 1, j).Address(0, 0)

Next j
Next i

End Sub

The above code returns the following locations

points to: retreived from:
(2,0) (2,3)
(3.0) (3,3)

(3,0) (2,4)
(4,0) (3,4)

(4,0) (2,5)
(5,0) (3,5)


(5,0) (8,3)
(6,0) (9,3)....etc..

What I need it to return is the following:

points to: retreived from:
(2,0) (2,3)
(3.0) (3,3)

(4,0) (2,4)
(5,0) (3,4)

(6,0) (2,5)
(7,0) (3,5)


(8,0) (8,3)
(9,0) (9,3)....etc


Hope that makes sense? Is there a simple change I can make to this code?

Thank you,

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

Need Help with macro code modification
 

HM

If I read this correctly, then the sequence in sheet 1 is
C1 = scheduleC1
C2 = scheduleC2
C3 = scheduleC3
C4 = scheduleD2
C5 = scheduleD3...

If this is correct, then try

Sub ccc()
Dim cnta As Boolean
cnta = True
Range("c1").Select
ActiveCell.Formula = "=schedule!c1"
ActiveCell.Offset(1, 0).Select
While ActiveCell.Row < 26
ccol = Int(ActiveCell.Row / 2 + 2)
ActiveCell.Formula = "=schedule!r" & cnta + 3 & "c" & ccol
cnta = Not (cnta)
ActiveCell.Offset(1, 0).Select
Wend
End Sub

It assumes that you are already in sheet1.

Tony

----- HM wrote: -----

Greetings All,

Thanks alot Dick for the initial help. I thought I had it, but i was unable
to modify this to exactly work for me.


I have the following macro code which retrieves data from a second
worksheet:

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 19 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 - 3, 0).Formula = _
"=Schedule!" & Sh.Cells(i + 1, j).Address(0, 0)

Next j
Next i

End Sub

The above code returns the following locations

points to: retreived from:
(2,0) (2,3)
(3.0) (3,3)

(3,0) (2,4)
(4,0) (3,4)

(4,0) (2,5)
(5,0) (3,5)


(5,0) (8,3)
(6,0) (9,3)....etc..

What I need it to return is the following:

points to: retreived from:
(2,0) (2,3)
(3.0) (3,3)

(4,0) (2,4)
(5,0) (3,4)

(6,0) (2,5)
(7,0) (3,5)


(8,0) (8,3)
(9,0) (9,3)....etc


Hope that makes sense? Is there a simple change I can make to this code?

Thank you,

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

Need Help with macro code modification
 

Hi Tony

Thanks a lot,
What I'm trying to do is the following:

C1=ScheduleC1 This one I keep seperate since it is out of the "loop" so
to speak.

C2=Schedule!C2
C3=Schedule!C3
C4=Schedule!D2
C5=Schedule!D3
C6=Schedule!E3
C7=Schedule!E4
C8=Schedule!C8
C9=Schedule!C9
C10=Schedule!D8
C11=Schedule!D9
C12=Schedule!E8
C13=Schedule!E9
C14=Schedule!C14 ...etc..etc after 14, and 15CD&E.. it needs to jump 6
rows to 20and 21C,D&E ..etc.. until I fill 121 rows.

I plugged in your code and it works great for the first 3, but I'm unsure of
how to modify it to only give me columns C,D,and E before skipping down 6
rows and starting over again..

Thanks,

HM


"acw" wrote in message
...
HM

If I read this correctly, then the sequence in sheet 1 is
C1 = scheduleC1
C2 = scheduleC2
C3 = scheduleC3
C4 = scheduleD2
C5 = scheduleD3...

If this is correct, then try

Sub ccc()
Dim cnta As Boolean
cnta = True
Range("c1").Select
ActiveCell.Formula = "=schedule!c1"
ActiveCell.Offset(1, 0).Select
While ActiveCell.Row < 26
ccol = Int(ActiveCell.Row / 2 + 2)
ActiveCell.Formula = "=schedule!r" & cnta + 3 & "c" & ccol
cnta = Not (cnta)
ActiveCell.Offset(1, 0).Select
Wend
End Sub

It assumes that you are already in sheet1.

Tony

----- HM wrote: -----

Greetings All,

Thanks alot Dick for the initial help. I thought I had it, but i was
unable
to modify this to exactly work for me.


I have the following macro code which retrieves data from a second
worksheet:

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 19 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 - 3, 0).Formula = _
"=Schedule!" & Sh.Cells(i + 1, j).Address(0, 0)

Next j
Next i

End Sub

The above code returns the following locations

points to: retreived from:
(2,0) (2,3)
(3.0) (3,3)

(3,0) (2,4)
(4,0) (3,4)

(4,0) (2,5)
(5,0) (3,5)


(5,0) (8,3)
(6,0) (9,3)....etc..

What I need it to return is the following:

points to: retreived from:
(2,0) (2,3)
(3.0) (3,3)

(4,0) (2,4)
(5,0) (3,4)

(6,0) (2,5)
(7,0) (3,5)


(8,0) (8,3)
(9,0) (9,3)....etc


Hope that makes sense? Is there a simple change I can make to this
code?

Thank you,

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

Need Help with macro code modification
 

H

Ok think I have it this time. If you need further mods, then email me at . I don't regularly come back to this group for past items

Ton

Sub ccc(
Dim cnta As Boolea
cnta = Tru
Range("c1").Selec
ActiveCell.Formula = "=schedule!c1
ActiveCell.Offset(1, 0).Selec
rowoff =
ccol =
While ActiveCell.Row < 12
'ccol = Int(ActiveCell.Row / 2 + 2
ActiveCell.Formula = "=schedule!r" & cnta + 3 + rowoff * 6 & "c" & cco
cnta = Not (cnta
If cnta Then ccol = ccol +
If ccol = 6 The
ccol =
rowoff = rowoff +
End I
ActiveCell.Offset(1, 0).Selec
Wen
End Su

----- HM wrote: ----

Hi Ton

Thanks a lot
What I'm trying to do is the following

C1=ScheduleC1 This one I keep seperate since it is out of the "loop" s
to speak

C2=Schedule!C
C3=Schedule!C
C4=Schedule!D
C5=Schedule!D
C6=Schedule!E
C7=Schedule!E
C8=Schedule!C
C9=Schedule!C
C10=Schedule!D
C11=Schedule!D
C12=Schedule!E
C13=Schedule!E
C14=Schedule!C14 ...etc..etc after 14, and 15CD&E.. it needs to jump
rows to 20and 21C,D&E ..etc.. until I fill 121 rows

I plugged in your code and it works great for the first 3, but I'm unsure o
how to modify it to only give me columns C,D,and E before skipping down
rows and starting over again.

Thanks

H


"acw" wrote in messag
..
H
If I read this correctly, then the sequence in sheet 1 i
C1 = scheduleC
C2 = scheduleC
C3 = scheduleC
C4 = scheduleD
C5 = scheduleD3..
If this is correct, then tr
Sub ccc(
Dim cnta As Boolea
cnta = Tru
Range("c1").Selec
ActiveCell.Formula = "=schedule!c1
ActiveCell.Offset(1, 0).Selec
While ActiveCell.Row < 2
ccol = Int(ActiveCell.Row / 2 + 2
ActiveCell.Formula = "=schedule!r" & cnta + 3 & "c" & cco
cnta = Not (cnta
ActiveCell.Offset(1, 0).Selec
Wen
End Su
It assumes that you are already in sheet1
Ton
----- HM wrote: ----
Greetings All
Thanks alot Dick for the initial help. I thought I had it, but i wa
unabl
to modify this to exactly work for me
I have the following macro code which retrieves data from a secon
worksheet
Dim i As Long, j As Lon
Dim Rng As Rang
Dim Sh As Workshee
Set Sh = ThisWorkbook.Sheets("Schedule"
Set Rng = ThisWorkbook.Sheets("Sheet1").Range("C1"
Rng.Formula = "=Schedule!C1
For i = 2 To 19 Step
For j = 3 To
Rng.Offset(i + j - 4, 0).Formula =
"=Schedule!" & Sh.Cells(i, j).Address(0, 0
Rng.Offset(i + j - 3, 0).Formula =
"=Schedule!" & Sh.Cells(i + 1, j).Address(0, 0
Next
Next
End Su
The above code returns the following location
points to: retreived from
(2,0) (2,3
(3.0) (3,3
(3,0) (2,4
(4,0) (3,4
(4,0) (2,5
(5,0) (3,5
(5,0) (8,3
(6,0) (9,3)....etc.
What I need it to return is the following
points to: retreived from
(2,0) (2,3
(3.0) (3,3
(4,0) (2,4
(5,0) (3,4
(6,0) (2,5
(7,0) (3,5
(8,0) (8,3
(9,0) (9,3)....et
Hope that makes sense? Is there a simple change I can make to this
code?
Thank you,
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