modify existing macro
 

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

modify existing macro
 

The code below will step every four rows based on the values of your text
boxes. If
the values are not exactly divisible by four then the If statement will kick
out of the loop.

For iRow = CLng(Me.TextBox1.value) To CLng(Me.TextBox2.value) Step 4
If iRow Clng(Me.TextBox2.Value) Then Exit For

Cells(iRow, 1) = iRow.Value 'This line for demo only, delete if adapted
MsgBox Cells(iRow, 1) 'This line for demo only, delete if adapted

'Place code to execute here.

Next iRow


"SteveDB1" wrote:

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

modify existing macro
 

Hi JLG,
Thank you for the response.
Sorry it took me so long to respond, we've been busy today.
I tried the code as you posted it, and the first thing it does is to kick
out a compile error, stating that for irow.value, irow isn't a valid
qualifier.

Then, when I comment it out, nothing occurs.
I did try changing irow.value to activecell.value, and that performed
nothing as well. I also tried changing it to startcell.value. Still - nada.

From what I can gather, it appears that my initial offset command isn't
working with merged rows.

Everything just stays in the first merged row, and only places a 1.

Which to me makes no sense, because when the rows aren't merged, this macro
works exactly as spec'd.

Again, thank you for your help.
Best,
SteveB.


"JLGWhiz" wrote:

The code below will step every four rows based on the values of your text
boxes. If
the values are not exactly divisible by four then the If statement will kick
out of the loop.

For iRow = CLng(Me.TextBox1.value) To CLng(Me.TextBox2.value) Step 4
If iRow Clng(Me.TextBox2.Value) Then Exit For

Cells(iRow, 1) = iRow.Value 'This line for demo only, delete if adapted
MsgBox Cells(iRow, 1) 'This line for demo only, delete if adapted

'Place code to execute here.

Next iRow


"SteveDB1" wrote:

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