S
SteveDB1
Howdie all.
A colleague of mine came in two weeks ago to obtain a macro that input
numbers that iterated, and offset rows.
I want something comparable, but instead the rows for my workbook are
merged. I've tried a few things to modify his code but Im either missing
something or have just missed it entirely. It works great on single, unmerged
rows.
Code for existing macro below. My problem/comments beneath that.
-------------------------------------
Private Sub OKbtn1_Click()
'Dim startno As Long
Dim iRow As Long
Dim StartCell As Range
'some validation???
If IsNumeric(Me.TextBox1.value) = False _
Or IsNumeric(Me.TextBox2.value) = False Then
MsgBox "Non-numbers in textboxes!"
Exit Sub
End If
If CLng(Me.TextBox1.value) >= CLng(Me.TextBox2.value) Then
MsgBox "Numbers not in right order!"
Exit Sub
End If
Set StartCell = ActiveCell
For iRow = CLng(Me.TextBox1.value) To CLng(Me.TextBox2.value)
StartCell.value = iRow
Set StartCell = StartCell.Offset(4, 0)
'iRow = StartCell.Offset(4, 0)
' it's my opinion that the problem exists within this for loop.
' If I place the irow=startcell.offset() it causes a semi-infinite loop
until it reaches
' the end of the worksheet- 1048576 rows down.
'I also tried changing startcell to activecell. That did not work.
Next iRow
' ActiveCell.Offset(1, 0).Activate
' Selection.ClearContents
' ActiveCell.Offset(4, 0).Activate
' Selection.ClearContents
' ActiveCell.Offset(4, 0).Activate
' Selection.ClearContents
' ActiveCell.Offset(4, 0).Activate
' Selection.ClearContents
' ActiveCell.Offset(4, 0).Activate
' Selection.ClearContents
' ActiveCell.Offset(4, 0).Activate
' Selection.ClearContents
' ActiveCell.Offset(4, 0).Activate
' Selection.ClearContents
' ActiveCell.Offset(4, 0).Activate
' Selection.ClearContents
'
'Range("N2").Activate
End
End Sub
----------------------------------------------
When I go to use this on a four merged row cell it only inputs the value for
the first merged row set. It's like there's no offset activity to continue
inputting numeric values to the last number from my second spinbox.
If I uncomment the clearcontents functions, it offsets, but just to clear
the values that would've been placed there-- if it was working correctly.
My goal is to have it input the values to my predetermined end point from my
user form.
If this isn't cleear enough, I apologize, please let me know.
Thank you.
SteveB
A colleague of mine came in two weeks ago to obtain a macro that input
numbers that iterated, and offset rows.
I want something comparable, but instead the rows for my workbook are
merged. I've tried a few things to modify his code but Im either missing
something or have just missed it entirely. It works great on single, unmerged
rows.
Code for existing macro below. My problem/comments beneath that.
-------------------------------------
Private Sub OKbtn1_Click()
'Dim startno As Long
Dim iRow As Long
Dim StartCell As Range
'some validation???
If IsNumeric(Me.TextBox1.value) = False _
Or IsNumeric(Me.TextBox2.value) = False Then
MsgBox "Non-numbers in textboxes!"
Exit Sub
End If
If CLng(Me.TextBox1.value) >= CLng(Me.TextBox2.value) Then
MsgBox "Numbers not in right order!"
Exit Sub
End If
Set StartCell = ActiveCell
For iRow = CLng(Me.TextBox1.value) To CLng(Me.TextBox2.value)
StartCell.value = iRow
Set StartCell = StartCell.Offset(4, 0)
'iRow = StartCell.Offset(4, 0)
' it's my opinion that the problem exists within this for loop.
' If I place the irow=startcell.offset() it causes a semi-infinite loop
until it reaches
' the end of the worksheet- 1048576 rows down.
'I also tried changing startcell to activecell. That did not work.
Next iRow
' ActiveCell.Offset(1, 0).Activate
' Selection.ClearContents
' ActiveCell.Offset(4, 0).Activate
' Selection.ClearContents
' ActiveCell.Offset(4, 0).Activate
' Selection.ClearContents
' ActiveCell.Offset(4, 0).Activate
' Selection.ClearContents
' ActiveCell.Offset(4, 0).Activate
' Selection.ClearContents
' ActiveCell.Offset(4, 0).Activate
' Selection.ClearContents
' ActiveCell.Offset(4, 0).Activate
' Selection.ClearContents
' ActiveCell.Offset(4, 0).Activate
' Selection.ClearContents
'
'Range("N2").Activate
End
End Sub
----------------------------------------------
When I go to use this on a four merged row cell it only inputs the value for
the first merged row set. It's like there's no offset activity to continue
inputting numeric values to the last number from my second spinbox.
If I uncomment the clearcontents functions, it offsets, but just to clear
the values that would've been placed there-- if it was working correctly.
My goal is to have it input the values to my predetermined end point from my
user form.
If this isn't cleear enough, I apologize, please let me know.
Thank you.
SteveB