PDA

View Full Version : "For Each" an endless loop, any better ideas?



Foxtrot
02-28-2009, 07:58 AM
:banghead:

Hello,

When my userform enters data into my spreadsheet, it will need more than one row in some columns. The problem is, the next time I enter data, it will copy over the data in the extra rows. I've tried to remedy this with an index column that will have an "x" in Column A for every corresponding cell in Column K, but the "For Every" function is an endless loop.
There has to be a better way?


Dim Cell As Range
For Each Cell In Range("K:K")
If ActiveCell.Value > 0 Then ActiveCell.Offset(0, -10).Value = "x"
Next Cell

lucas
02-28-2009, 08:51 AM
You need to look for the last row of data and put your userform input in the next row?

Fairly easy, could you post your workbook, it would be much easier to help you. hit post reply at the bottom left of the last post, scroll down till you see "manage attachments"

best if it is a 2003 version for me, I don't have 07

Foxtrot
02-28-2009, 09:07 AM
Actually Lucas,

In preparing an answer, I figured out the issue.
I've attached a word document with the userform code.

If I just change the "NextRow" function to calculate on column K instead of A, then the issue is resolved.

lucas
02-28-2009, 09:17 AM
You might want to try something like this. The 1 denotes column A so if you want to change it to 11 to check column K for the last row use:

NextRow = Cells(Rows.Count, 11).End(xlUp).Row + 1

Sub y()
Dim NextRow As Long
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
MsgBox NextRow
End Sub