Coding Multiple Cases in VBA

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.

Sub Test()
If Range("A1") > 50 Then
ActiveSheet.PageSetup.PrintArea = "C1:E7"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
Call cler_print
End Sub
Sub cler_print()
ActiveSheet.PageSetup.PrintArea = ""
End Sub

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)

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

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,


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

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

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.

Sub PrintArrays()
    If Range("A28").Value > 50 Then Range("S100:U161").PrintOut Copies:=1
    If Range("B38").Value > 50 Then Range("S164:U188").PrintOut Copies:=1
    If Range("C50").Value > 50 Then Range("S192:U218").PrintOut Copies:=1
    If Range("D61").Value > 50 Then Range("S224:U269").PrintOut Copies:=1
    If Range("E73").Value > 50 Then Range("S276:U313").PrintOut Copies:=1
    If Range("F93").Value > 50 Then Range("S321:U382").PrintOut Copies:=1
End Sub

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

Threads merged.

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

Did the remedial version I posted not work at all?

One way

Sub Test()
Dim ArrRng As Variant
Dim ArrPrint As Variant
Dim cl As Range
Dim i As Long
    For Each cl In Columns("J:J").SpecialCells(xlCellTypeFormulas, 23)
        With cl
            If IsNumeric(.Value) Then
                cl.Offset(0, 1).Value = .Value
            End If
        End With
    Next cl
    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