Forum Discussion
Stop data at a given line and continue in another column
This is probably simple but I can't figure it out after days of looking and playing around so thank you in advance to whomever helps.
I have a counter set up to fill in data up to whatever number I put in but I need that data to stop counting at line 55 in column A and continue the count on column G row 4.
I'm using Excel 2010
3 Replies
Hi,
This is not clear enough!
Let's say that you want to fill the series from 1 to 80, and the starting cell in column A is A1.
So you want to fill the range (A1 to A55) in numbers (1 to 55), then continue the filling in cell G4 from (56 to 80) so that you will stop in cell G28 with the value of 80.
Is this what you want?
Yes sorry I wasn't clear enough. Your example is correct.
I want the data to snake over to the G column so that I can have the full report on one excel page instead of having one long column that makes me print out more than one page.
How tricky is this!
But finally, I figure it out!
This is the code:
Sub Workbook_Open()
'Written by Haytham Amairah
'Created: 5/4/2018
'Last updated: 5/4/2018
On Error GoTo handler
Dim number As Integer
number = Application.InputBox("Please enter a number:")
If number = False Then Exit Sub
Dim scell As Range
Set scell = Application.InputBox("Please select the starting cell:", , , , , , , 8)
Dim r As Integer
For counter1 = 1 To number
If scell.Offset(counter1 - 1).Row <= 55 Then
r = counter1 - 1
scell.Offset(r).Value = counter1
Else
Exit For
End If
Next counter1
r = 0
For counter2 = counter1 To number
Range("G4").Offset(r).Value = counter2
r = r + 1
Next counter2
handler:
End SubPlease find it in the attached file.
Regards
