Data allocation looping limited to 31 lines only.

[erkkyy]

I'm having a problem with my code that allocates the user input data. The looping works fine but it is limited to 31 lines only.

Here's the code:

Sub HYD_HYDMNL_TIX()

Dim MNLCounter As Double, HYDCounter As Double, MNL As Double, HYD As Double
Dim nextrow As Double


MNL = ActiveSheet.Range("B4").Value + 6
HYD = ActiveSheet.Range("F4").Value + 6
nextrow = ActiveSheet.Range("O4").Value + 4
MNLCounter = 4
HYDCounter = 4


For HYDCounter = 4 To HYD

 ActiveSheet.Range("Q" & nextrow).Value = ActiveSheet.Range("H" & HYDCounter).Value
 nextrow = ActiveSheet.Range("O4").Value + 4
 ActiveSheet.Range("Q" & nextrow).Value = ActiveSheet.Range("D" & MNLCounter).Value

 MNLCounter = MNLCounter + 1

 nextrow = ActiveSheet.Range("O4").Value + 4
 
Next HYDCounter

End Sub

Have tried changing the value of

For HYDCounter = 4 To HYD

to

For HYDCounter = 4 To 1000

but still does not work properly and it still limited to 31 lines only.

Appreciate your help.

 

[JoeMo]

Does cell O4 on the active sheet have a formula in it? If yes, what's the formula? If no, then your value of nextrow is static - is that what you intended?

 

[erkkyy]

It has a formula that counts the number of generated data.

 

[JoeMo]

It has a formula that counts the number of generated data.

Good start, but what is the formula (i.e. "= .....")?

 

[erkkyy]

=counta(q4:q1000)

 

[erkkyy]

=counta(q4:q1000)

Same formula with cells

B4 & F4

 

[JoeMo]

Same formula with cells

B4 & F4

The value of HYD is fixed when you start looping. nextrow changes during the loop only if the cells in col Q you are writing to are empty before the write. Otherwise nextrow is static. I'm not clear on what you want your macro to accomplish, but if you explain what you want to happen, maybe there's a better way.