Coding Multiple Cases in VBA

**billofsoo** · 03-24-2009, 04:18 PM

I am at a loss. I am writing VBA code to do the following:
IF A1 > 50 then print array C1:E7

I have this code and it works.

Please Login or Register  to view this content.

Now, I want to be able to do the same for the following within the same module:
If A10>50 then print C10:E17
If A21>50 then print C21:E30 and so on for three more 'tests' of variables

Question: can a series of such commands be included in one code activated by one MACRO button? If so, how does one write a series of cases testing the values in a series of cells to deterine if an associated array should be printed?

I have tried to do this repeatedly and cannot find the right syntax that will trigger a series of tests with one macro button click.

In the end, I want to user to complete a checklist - then click a MACRO button that will print arrays of information if the values in specific cells meet the criterion >50. I want the code to 'test' the values in 6 different cells and to print, based on compliance with the criterion, 6 different arrays of information.

I'm under the gun and am stalled. People are waiting on my solution and I can't seem to move forward as I am not too familiar with VBA.

Can and will anyone HELP???

Best regards...I know you are all busy!!

Bill (e-mail address removed by mod)

**VBA Noob** · 03-24-2009, 04:35 PM

billofsoo,

e-mail address removed and code wrapped as per forum rules below.

Is that a pattern to the cells and ranges to print

VBA Noob

**billofsoo** · 03-24-2009, 04:56 PM

Sorry first timer I guess.

No it is not a pattern. There are six sections to the checklist we are using. Teachers complete the checklist and percentages are calculated based on the teacher's ratings of items. Each section has a cell with a percentage entered.

Asssociated with each section is an array of cells containing text.

What I would like the program to do is to 'test' the percentage value in the six cells - one for each section. If the number in the cell is above 50, I want it to print the associated array of cells that hold the text.

I can do it for one cell and the associated array but don't know how to write code so that once the macro button is clicked each of the six cell values are tested and the appropriate information is printed.

I don't know if this is possible. I just posted the cell designations in the previous post as examples; however, I do have the specific cell locations to to be tested and the associated arrays that would be needed.

A28 S100:U161
B38 S164:U188
C50 S192:U218
D61 S224:U269
E73 S276:U313
F93 S321:U382

I don't know if this is clear to you or not. I was just hoping there would be a way to write a code that would keep things really simple for those with whom I work.
Thanks for your time & interest.
Bill

**VBA Noob** · 03-24-2009, 05:08 PM

Maybe

Please Login or Register  to view this content.

Please Login or Register  to view this content.

VBA Noob

**billofsoo** · 03-24-2009, 06:03 PM

Man you are something else....I will try this.

Where does this second peice fit in? Is it part of the larger code you wrote?

Code:

Please Login or Register  to view this content.

I'm sorry, I highlighted this code and click the # sign above but I can't seem to make this work as per rules!!

Not sure where to put this second peice of code you wrote.

BillofSoo

**VBA Noob** · 03-24-2009, 06:07 PM

billofsoo,

It's the code you posted earlier. It resets the PrintArea to blank

Place code in same module

Also I've wrapped you're code for you this time. Next time type in [code] at the start and end of the code and add a / to the last one between the [ and the c

e.g

[/code

Can't add the last bracket but you get the idea.

If you're happy with the solution please mark it as solved

VBA Noob

**VBA Noob** · 03-24-2009, 06:16 PM

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

**billofsoo** · 03-24-2009, 06:42 PM

Thanks for the tip on wrapping the code - I made notes.

Also, I misinterpreted the rules. I took it to mean posting the same question on the same site under different threads...sorry again...I'm really in the black books and won't do that again!!! Like I say, I'm pretty new to this world!

I copied the code exactly as your produced it (I did figure out that I had to put the last bit with the first module).

I copied it into a window that I got by choosing Macro>Visual Basic Editor. Then I went to Insert>Module.

Then I assigned that module to the button I designed.

I'm sorry but it doesn't work...it just flickers once and nothing happens.

What should I do now? I will check my work again.

BTW, when I try to close the VB Editor, it wants me to save it...where do I save it?

Thanks, BillofSoo

**VBA Noob** · 03-24-2009, 06:44 PM

Still need to add the link to the other forum

VBA Noob

**billofsoo** · 03-24-2009, 06:54 PM

Sorryl my Question was cross posted to these forums:

http://www.mrexcel.com/forum/showthread.php?t=378477
http://bytes.com/topic/visual-basic/...67106-vb-excel

VBA Noob, is this how to do this?

**VBA Noob** · 03-24-2009, 06:56 PM

Thanks

Maybe those cells are all less than 50. Here is my example but instead of printing I've used printpreview to save trees

VBA Noob

**billofsoo** · 03-24-2009, 07:36 PM

I ran your module and it worked.
The values in the testing cells are all at 100 - ah...but the value is being transferred to the cell using a formula - could that be a problem?

I tried the module changing J28 to K28 with a value of 100 and without a formula. It should have printed the array associated with K28 but nothing happened.

Could it be that I am not setting up the macro to the button properly?
Actually when I use the RUN button at the top right of the MACRO window there is still no action.
Any suggestions?

**JBeaucaire** · 03-24-2009, 08:04 PM

Just another tack, from a 101 approach, better for my easily addled brain. The only real bonus to this approach is that it reads more 1-to-1 and there is no "print area" to set/clear.

Please Login or Register  to view this content.

**billofsoo** · 03-24-2009, 08:58 PM

It works...thanks so much. Seems like simpler was better in a sense.

I will mark this thread as solved.

Thanks again.

Billofsoo

**billofsoo** · 03-24-2009, 09:10 PM

VBA NOOD

I think I spoke too soon.

It seems that if the first two cells are >50 then only their associated arrays print - like it is supposed to work.
If the first cell is less than 50 then the first associate arrays doesn't print - like it is supposed to work..
BUT
If the first third and sixth cells are less than 50 then the first array does not print but ALL the others do.

Don't know why....can you consider why this might be happeing?

Thanks

**billofsoo** · 03-24-2009, 09:35 PM

VBA Nood

Actually if all cells are less than 50 except the first cell then ALL arrays are printed. Can't see any problem with the code at all. In fact if only the third cell is greater than 50 then all pages still print. Weird!

Let me know what you think - it is late here..need to go home. Will check for a response in the morning.

Thanks a lot for your patience.

BillofSoo

**shg** · 03-25-2009, 12:36 AM

Threads merged.

**VBA Noob** · 03-25-2009, 03:27 AM

Using my example again it all seem to work.

Post your file (remove any info) and I'll take a look or go with the other option you got

VBA Noob

**billofsoo** · 03-25-2009, 05:09 PM

Thanks VBA Noob

I have stripped out the info from the worksheet - left some in as well along with some notes so you don't have to work hard to figure out how I set this up. I included three modules you have seen and adjusted them to match my exact cell/array locations.

Best of luck...hope you get this.

Best regards,

BillofSoo

**VBA Noob** · 03-25-2009, 05:16 PM

The missed changing one range address

Please Login or Register  to view this content.

Is that you're problem?

VBA Noob

**billofsoo** · 03-25-2009, 05:42 PM

VBA Noob

I know what the problem is....in the cells that are tested >50, I have formulas!!! you have just numbers. When I put straight numbers in column K and adjust the module to accommodate, this code works as it is supposed to.

How can I get the value obtained through the formula to appear in another cell as a value only?

That will solve the problem.

BillofSoo

**JBeaucaire** · 03-25-2009, 06:00 PM

Did the remedial version I posted not work at all?

**VBA Noob** · 03-25-2009, 06:04 PM

Maybe this amend code which won't require the cler_print module

Please Login or Register  to view this content.

VBA Noob

**billofsoo** · 03-25-2009, 06:05 PM

VBA Noob: not sure what you mean by remedial version. However, I do know that your code works if there is a way to take the formula produced number and paste it as a value.

Can we write a macro to begin the module that copies the numbers from J that result from a formula and Paste Special into column K as a value?

I think that would do it...just not sure how to insert into your module.

Is it possible?

BillofSoo

**VBA Noob** · 03-25-2009, 06:16 PM

One way

Please Login or Register  to view this content.

VBA Noob

**billofsoo** · 03-25-2009, 07:15 PM

VBA Noob:

This works beautifully. Is it possible to add one more thing?

I would like the same module to print a worksheet in the same workbook named "Chart". The worksheet is already i formatted for Print Area....

The cells I want to print in "Chart" are B3:H40

If you get a minute, this would be the "icing on the cake"!!

Thanks VBA Noob...you have been most helpful. If you don't mind, on your next post, can you tell me where (country/state) you are located??? I am suspecting England; but I may well be out to lunch on that.

Anyway, will await your reply.

Best regards and thanks again!

BillofSoo

**VBA Noob** · 03-25-2009, 07:26 PM

Just add a line like at the end

Please Login or Register  to view this content.

Uses a name range called Chart

http://www.contextures.com/xlNames01.html

As my profile says....London

VBA Noob

**billofsoo** · 03-26-2009, 08:31 AM

I am using the code from your post #23

When you say put it at the end, where exactly do you mean?

I put it at the very end...went to debugger
I put it just before End Sub ... went to debugger

Not real sure where to insert the PRINT command for "Charts"

Thanks

BillofSoo

**VBA Noob** · 03-26-2009, 09:12 AM

Do you name the range to print?

VBA Noob

**billofsoo** · 03-26-2009, 03:24 PM

VBA Noob
This is the code I am using to print the various arrays based on the criterion >50

Please Login or Register  to view this content.

The code below is the code you wrote me last night and told me to put it at the "end" of the above code.(it is the module code you wrote in post #23 on this forum and it works fine).

Code:

Please Login or Register  to view this content.

I tried to just tag it to the end of the code you previsously wrote but it just causes errors.

The range of the data on the Worksheet called "CHART" is B3:H40.

Where exactly do I insert this line into the code?

I feel a bit stupid but I'm new to this.

Would you mind telling me exactly where to insert the code to print the range B3:H40 from the worksheet "Chart"?

Thanks,

BillofSoo

**VBA Noob** · 03-26-2009, 06:54 PM

Code can go before or after the loop code. So let's put it at the end

e.g

Please Login or Register  to view this content.

Note it won't work unless you have named the ranges as I've mentioned 3 times now. Link explains how to do that

VBA Noob

**billofsoo** · 03-27-2009, 04:23 PM

Hi VBA Noob

Are you there?

BillofSoo

**VBA Noob** · 03-27-2009, 05:09 PM

Hi VBA Noob

Are you there?

BillofSoo

How did you get on?

VBA Noob

Coding Multiple Cases in VBA

LinkBack

Thread Tools

Rate This Thread

Display

Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Re: Coding Multiple Cases in VBA

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions