Using an array in a case statment

**nods** · 11-11-2010, 12:34 AM

Hi i have this code which is supose to populate a user form with data depending on the values the user enters.

I originaly used this code but it was nearly 5000 rows of code because the form has 30 rows so i thought i would use an array to make things easier. The code worked fine but was a tad memory intensive.

Original code x 30

Please Login or Register  to view this content.

Here is my new code - it complies fine but doesn't seem to work.

Can some tell me where I am going wrong?

Please Login or Register  to view this content.

example of the table the code is looking at

rotaexample.xlsx

**Bob Phillips** · 11-11-2010, 04:10 AM

Doesn't work is not very helpful, doesn't give us much steer.

**nods** · 11-11-2010, 04:26 AM

i'm not sure whats wrong to be honest. It compiles fine. But the code doesnt seem to write and data to the spread sheet.

even if i reverse engineer the code

Please Login or Register  to view this content.

it doesnt find data in the spread sheet. I think it is a problem with my case statment.

**nods** · 11-11-2010, 04:29 AM

If have this similar code using an array to write the data to the same table. and i get a syntax error on the

Please Login or Register  to view this content.

Any help would be really apreciated. I have been up all night looking at this,

Please Login or Register  to view this content.

**romperstomper** · 11-11-2010, 06:54 AM

You need a space between the And and the underscore:

Please Login or Register  to view this content.

**nods** · 11-17-2010, 08:43 AM

Hi thanks for that but it didn't solve the problem.

I have solved the syntax error in the above code though, But i am still having problems with first snippet of code.
It compiles fone with out errors. But it doesn't work and I cant see what the problem is. Is there a way of testing my case statment? Or can anyone see where I am going wrong?

Please Login or Register  to view this content.

Many thanks

Nods

**romperstomper** · 11-17-2010, 09:01 AM

That was to solve your syntax issue, nothing else.
Are you adding numbers together or concatenating text? If the latter, use '&' not '+'

**snb** · 11-17-2010, 09:06 AM

It's no use using Select case if there is only one condition to test.
Did you explore the differences between the operators + and & ?

Please Login or Register  to view this content.

**nods** · 11-17-2010, 09:19 AM

Many thanks. I am concatenating text. I made the change you sugested and the code still doesn't work. My case statment now looks like:

Please Login or Register  to view this content.

I have attached an example of the table the code is looking at. It should find a match in column G and populate the form with columns L,M and N.

Many thanks

RotainputExample.xls

**romperstomper** · 11-17-2010, 09:31 AM

That workbook might have been quite useful if it had your form in it...

**nods** · 11-17-2010, 09:54 AM

Sorry here is the work book with form. The only button I am concerned with is the 'View Historical Rota' button.

RotainputExample.zip

**romperstomper** · 11-17-2010, 10:18 AM

Your Case statement seems fine to me other than that this:

Please Login or Register  to view this content.

should be:

Please Login or Register  to view this content.

For future reference, most people find it easier to debug code if the project isn't password protected, and all the required sheets and forms are present for the code to run.

**nods** · 11-17-2010, 10:51 AM

Oops my bad. I was trying to upload the entire document but it kept failing.

The password is donkey.

I added the "" as you sugested and it made no difference.

**romperstomper** · 11-17-2010, 11:09 AM

I've already unlocked and reviewed it, as I said, and your case statement works for me. What specifically do you mean when you say it doesn't work?

**nods** · 11-17-2010, 11:12 AM

So for example if the user selects Week 19 , Talpore and Cleaner then clicks View Historical rota the form should populate with that weeks data.And it doesn't.

**romperstomper** · 11-17-2010, 11:18 AM

That's because this line stops it:

Please Login or Register  to view this content.

unless you have already altered the control values to something other than the defaults.

**nods** · 11-17-2010, 11:45 AM

Many thanks. That was obvious once you had pointed it out.

The form seems to poulate fine now but only upto line 94. Fore example if was to select week 19, Talpore , cleaner it will populate perfectlty upto line 94(Employee N, On Friday). I was to select Restaurant it doesnt populate at all.

I cant see how this could be possible?

**romperstomper** · 11-17-2010, 11:50 AM

You only have data in column A for rows 1 to 93, and that's the column you use to determine the last row of data.

**nods** · 11-17-2010, 12:25 PM

Many thanks. Problem solved.

Using an array in a case statment

LinkBack

Thread Tools

Rate This Thread

Display

Using an array in a case statment

Re: Using an array in a case statment

Re: Using an array in a case statment

Re: Using an array in a case statment

Re: Using an array in a case statment

Re: Using an array in a case statment

Re: Using an array in a case statment

Re: Using an array in a case statment

Re: Using an array in a case statment

Re: Using an array in a case statment

Re: Using an array in a case statment

Re: Using an array in a case statment

Re: Using an array in a case statment

Re: Using an array in a case statment

Re: Using an array in a case statment

Re: Using an array in a case statment

Re: Using an array in a case statment

Re: Using an array in a case statment

Re: Using an array in a case statment

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions