Need Help with macro code modification

[HM]

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

 

[Guest]

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

 

[HM]

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

 

[Guest]

H

Ok think I have it this time. If you need further mods, then email me at (e-mail address removed). 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