Coding Multiple Cases in VBA

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

ArrRng = Array("K28", "J38", "J50", "J61", "J73", "J93")

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

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

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

Sub Test()
Dim ArrRng As Variant
Dim ArrPrint As Variant
Dim i As Long
    ArrRng = Array("J28", "J38", "J50", "J61", "J73", "J93")
    ArrPrint = Array("S100:U161", "S164:U188", "S192:U218", "S224:U269", "S276:U313", "S321:U382")
For i = 0 To UBound(ArrRng)
    With Range(ArrRng(i))
        If IsNumeric(.Value) Then
            If .Value > 50 Then
                With ActiveSheet
                    .Range(ArrPrint(i)).PrintOut Copies:=1
                End With
            End If
        End If
    End With
    
Next i
End Sub

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

Range("Chart").PrintOut Copies:=1

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