For each cell error - excel freezes

[strud]

A bit of code I have come up with seems to freeze, and in some cases crash, excel and I don't understand why.

It is essentially designed to check if any cells in column A of Sheet1 contain the value "Error - Not Inputted" and if so copy across some data and move the user to a sheet, or alternatively if that valuue is not present display a msgbox and move the user to a different sheet.

Any ideas why it is not working....

Please Login or Register  to view this content.

Thanks in advance

[AB33]

You are looping through 1m rows.

Please Login or Register  to view this content.

[AB33]

I do not like to loop through collection, rather prefer to loop through range. I am not entirely sure if this is slightly faster than collection.
Try

Please Login or Register  to view this content.

[strud]

Hi AB33 thanks for the swift response.

I have just tried the code you gave but the problem is (a goal post shift I know) I need it to be part of a larger formula where 'i' has already been declared- so when the code then gives the compile error: 'duplicate declaration in current scope'.

Any ideas of a away round this.

(oh and i was celared as 'Long')

Thanks again

[AB33]

'duplicate declaration in current scope'.

Means they are duplicate variable names declared on the dim statement.
If your incorporate my code in to large code, please remove the two variables from my code.
It might be you have I declared as long on your dim statement

[strud]

Thanks I tried that but unfortunately the msgbox just stays there when I click ok and I have to Ctrl-BREAK to end the macro.

Also, it is not recognising when the 'error-not inputted' message is there,

I have attached a sample workbook that incorporated your last bit of code (in module 1 'Sub Copy_AboveBellow_Error) and was wandering whether you might take a look at it?

Essentially, (sorry is this is reiteration) on Sheet 1 the message in column A is determined the count in column C.

When the message is 'error-not inputted' it should copy that row and take the user to the 'discrepancies sheet', or alternatively when the message is anything else (entered/ not entered but 0 on the system) then it should just take the user to the 'Upload Sheet'

Massive thanks again, it is much appreciated

[AB33]

I have tested the code it works as intended.
The code has to loop until the last non empty row in column A.
Since you only have 1 row (Row 19) which is true, the rest of the loop will shows you the message

Please Login or Register  to view this content.

Why are you selecting the sheets? What is your intention? The code will loop through until the last row, unless you tell the code to exit the loop under certain condition.
Selecting a sheet is not a condition, calling another macro will also take you to the called macro-finish this code and then comes back to the loop.

[strud]

I am not sure I know enough about how code loops and am getting confused a bit here so apologies.

I took out the msgbox and sheet selection (that you quoted in the previous message) and it works perfectly.

It did recognise the 'error - not inputted' and responded properly.

What I am trying to achieve with the other bit is when it does not find that message it simply takes the user to the upload sheet...thats the bit that won't work.

Thanks again for your continued help ( I am a vba newbie so the help is much appreciated)

[nilem]

Hi Strud,
as an option

Please Login or Register  to view this content.

[AB33]

"What I am trying to achieve with the other bit is when it does not find that message it simply takes the user to the upload sheet...thats the bit that won't work"

When the user goes to that sheet what is he/she is going to do? Are they going to perform some action?

[strud]

Fantastic!!!

Thanks nilem that works perfectly.

Cheers guys for all the help- much appreciated!

Hi Strud,
as an option

Please Login or Register  to view this content.

[strud]

Yes, essentially the aim is to remove worksheet tabs so the user moves through the workbook via vba.

That's why I wanted to combine both in one code- so it just keeps things simpler for the user.

nilem's code is working so I can stop pestering you lol

Thanks though for the help

"What I am trying to achieve with the other bit is when it does not find that message it simply takes the user to the upload sheet...thats the bit that won't work"

When the user goes to that sheet what is he/she is going to do? Are they going to perform some action?