Coding Multiple Cases in VBA

**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

Sub Test()
Dim ArrRng As Variant
Dim ArrPrint As Variant
Dim i As Long
    ArrRng = Array("A28", "B38", "C50", "D61", "E73", "F93")
    ArrPrint = Array("S100:U161", "S164:U188", "S192:U218", "S224:U269", "S276:U313", "S321:U382")
For i = 0 To UBound(ArrRng)
    If Range(ArrRng(i)) > 50 Then
        With ActiveSheet
            .PageSetup.PrintArea = ArrPrint(i)
            .PrintOut Copies:=1
        End With
    End If
    
    Call cler_print
Next i
End Sub

Sub cler_print()
     ActiveSheet.PageSetup.PrintArea = ""
End Sub

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:

Sub cler_print()
     ActiveSheet.PageSetup.PrintArea = ""
End Sub

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