Multiple else if loops - problem with placement of end for/if

[squarahollier]

Hi there.

I want to run a loop that sets variables depending on which worksheet it is looking at, then sets more variables depending on the column within the worksheet.

in the first instance it finds the worksheet - if the worksheet is called valve it will choose find a column and set variables. I then want it to skip to the code at the bottom. after it has run the code i want it to search for another column in the valve sheet and repeat the code for each column. After that I want it to search for the sheet 'pipe' and do the same. Eventually the loop will include 6 such sheet names each with multiple columns to set variables for.

At the moment when I run the code it exits the loop too quickly so that non of the code at the bottom is run for any of the sheets. I've been moving the end if/end for all over the show but so far haven't found a solution. Any help much appreciated. Cheers

Please Login or Register  to view this content.

[nathansav]

Try using select case it will be easier

select case worksheet name

case "Sheet1"

select case worksheet.column

case 2

case 5

case 10
end select

case "Sheet2"

select case worksheet.column

case 2

case 5

case 10
end select


End select

Have a look on help, a lot easier i think.

[squarahollier]

Thanks, I'd forgotten all about case.
However, I'm not sure how I can get the macro to run the code at the bottom for each case? should i make the code a separate function? Not working with this solution so far as it doesn't run the code for the f(X) filter. I also get the error 'out of memory' if I run it without 'call filter'

thanks againg

Please Login or Register  to view this content.

[nathansav]

Out of memory is a funny one. It comes when you are running some code on a sheet that has zoom applied, i've found, check your sheet is at 100% zoom.

[squarahollier]

It was on 100% already

[nathansav]

ok, where does it tell you you're out of memory, if you debug it.