PDA

View Full Version : [SOLVED] Start Looping Through Range from the Middle



Ethan
04-23-2015, 03:06 AM
The following code populates range A10 to A100 with data from range F10:F30 and repeats the range F10:F30 till it reaches A100.
However Sometimes I need to start the cyclus from lets say F21 (instead of F10) and then finish the range and repeat from the beginning again until it reaches A10 again.



Dim x, y As Integer
x = 10
Do While x < 100
For y = 10 To 30
Sheets("Sheet1").Cells(x, 1).Value = Sheets("Sheet1").Cells(y, 6).Value
x = x + 1
Next y
Loop



All help is welcome

mancubus
04-23-2015, 04:37 AM
it's not clear to me. post a manually filled worksheet as per your requirement.

Ethan
04-23-2015, 05:01 AM
Here is an example of what I need.

in Column K you'll find ther desired outcome.
The user enters the nth value in K4 and the code should populate K10:K100 starting with the nth value in range F10:F30

Paul_Hossler
04-23-2015, 06:44 AM
Try something like this -- change ranges and start to suit

It could use some error checking





Option Explicit

Sub test()
Dim rSource As Range, rDestination As Range
Dim iSourceStart As Long
Dim iSource As Long, iDestination As Long


Set rSource = ActiveSheet.Range("F10:F30")
Set rDestination = ActiveSheet.Range("K10:K100")
iSourceStart = 6

iSource = iSourceStart

For iDestination = 1 To rDestination.Rows.Count
rDestination.Cells(iDestination, 1).Value = rSource.Cells(iSource, 1).Value
iSource = iSource + 1
If iSource > rSource.Rows.Count Then iSource = 1
Next iDestination
End Sub

Ethan
04-23-2015, 12:52 PM
Thanks Paul,
works perfect