Posts by Craig Ottley

Re: Force Page Breaks In Vba?

Ok try this

Sub SetPageBreaks()
        
                ' Change Columns to which ones are actually hidden
    If Columns("E:F").EntireColumn.Hidden = True Then
    
        ActiveWindow.View = xlPageBreakPreview
            Set ActiveSheet.VPageBreaks(1).Location = Range("I1")
        ActiveWindow.View = xlNormalView
        
    Else
        ActiveWindow.View = xlPageBreakPreview
            Set ActiveSheet.VPageBreaks(1).Location = Range("I1")
            Set ActiveSheet.VPageBreaks(2).Location = Range("P1")
        ActiveWindow.View = xlNormalView
    End If
    
End Sub

should help you get there

Re: Force Page Breaks In Vba?

For some reason in excel the FitToPageWide and tall doesn't work when put into a macro so there is not point in setting FitToPageWide = 2

try doing something along the lines of this

With ActiveSheet.PageSetup
        .CenterHorizontally = True
        .Orientation = xlLandscape
       
    End With
    
    ActiveWindow.View = xlPageBreakPreview
    ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
    ActiveWindow.View = xlNormalView

Re: Matrix Combinations And Permutations

With a bit of Mod i've got the finished article!

Thanks you very much Ger Plante

Public Sub permutations()
     
    
    Dim iRow As Long
    
     iL1Value$ = Cells(1, 6)
     iL2Value$ = Cells(1, 7)
     iL3Value$ = Cells(1, 7)
'
    With ActiveSheet
         
        .Range("A:D").ClearContents
        .Cells(1, 1).Value = "Adults"
        .Cells(1, 2).Value = "Children"
        .Cells(1, 3).Value = "Infants"
'        .Cells(1, 4).Value = "Total"
         
        For iLoop1% = 0 To iL1Value$
            For iLoop2% = 0 To iL2Value$
                For iLoop3% = 0 To iL3Value$
                    If (iLoop1% + iLoop2% + iLoop3% <= iL2Value$) And _
                    (iLoop1% + iLoop2% + iLoop3% > 0) Then
                    
                    iRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    
                        .Cells(iRow, 1) = iLoop1%
                        .Cells(iRow, 2) = iLoop2%
                        .Cells(iRow, 3) = iLoop3%
'                    .Cells(.Range("D65536").End(xlUp).Row + 1, 4) = iLoop3 + iLoop2 + iLoop1
                End If
            Next iLoop3%
        Next iLoop2%
    Next iLoop1%
     
End With
 
End Sub

Re: Matrix Combinations And Permutations

Quote from Ger Plante

here is the worksheet just in case....

Press Alt+F11 to see the code...

Ger

Thanks very much! some how i couldn't figure out the loop...

Top Notch! :music:
: D

Re: Matrix Combinations And Permutations

Quote from Ger Plante

OK, I thought that 2 children might = 1 Adult, which gets complicated, but your saying basically that a person is a person, regardless of size

So you need the permutations as follows:
up to 5 infants,
up to 5 children,
up to 3 adults,

but total occupancy never exceeds 5

So you could in theory have

3 Adults, zero children and zero infants.
3 Adults, 1 child and 1 infants.
zero Adults, 4 child and 1 infant.
zero adults, 1 child and 4 infants.

Is this right?

Ger

Display More

yeah thats right. it doesn't matter about children/infants being in there own room... i can get adults and children working fine its the in-between bits i can't get to work

IE 3 adults 1 child and 1 infant

Re: Matrix Combinations And Permutations

Quote from Ger Plante

I think I know what youre trying to do and I might be able to help...

Couple of questions...

How many Infants = 1 Child?
How many Children = 1 Adult?
Whats the maximum number of Infants a room can hold, assuming no one else in the room?
Whats the maximum number of Children a room can hold, assuming no one else in the room?
Whats the maximum number of Adults a room can hold, assuming no one else in the room?

Ger

Display More

Infants is the same as Children but they have there own seperate field as they are counted in the occupancy...

so maximum number of Infants in a room is 5 (no adults)
maximum number of Children in a room is 5 (no adults)

maximum number of Adults in a room is 3

the room can hold a maximum of 5 occupancies

therefore if there are 3 adults in the room only 2 children or two infants are allowed in the room...

i've attached a sheet to hopefully show what i mean

zimitry

Re: Run Macro From An "if" Formula In A Cell

Hmmm strange...

Copy and paste the below code into the worksheet Object

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Trim(Cells(1, 2)) = Empty Or Trim(Cells(1, 3)) = Empty Then
   
    Exit Sub
   
    ElseIf Trim(Cells(1, 2).Value) > Trim(Cells(1, 3)) = True Then 'Optional '>=
            Load frmPanic
            frmPanic.Show
        Else
            'Do Nothing
        End If
       


    End If
    


End Sub

This works fine on mine, please see attachment for details..

zimitry

This isn't an easy one for me... so here it goes on trying to explain...

i've got 2 numeric numbers one in each cell. These numbers indicate Maximum occupancies for a hotel room...

Maximum Adults Maxmium Occ
3 5

The Occupancy table looks like this

Adults Children Infants... so taking the above numbers the table should be built up like this

Adults Children Infants
1 0 0
2 0 0
3 0 0
1 1 0
1 2 0
1 3 0
1 4 0
1 1 1
1 1 2
1 1 3

3 2 0
3 0 2
3 1 1

etc...

so basically to room can have a maxium of 3 adults and 2 children equaling the maxium room occupancy.

i'm trying to create a function that will automatically do this and workout subsquent numbers below the maxmium but i can't get anywhere near the result i am looking for...

any help is much appreciated

Cheers

zimitry

Re: Run Macro From An "if" Formula In A Cell

There is a way of doing this but not directly from an if formula..

in Visual Basis editor double click on your worksheet then in the main window on the left hand mousedown select Worksheet. then on the Right hand mousedown select Change. Now you can insert code to test the cell that has the IF formula

Private Sub Worksheet_Change(ByVal Target As Range)


    If Cells(1, 1).Value = 1 Then
    
        UserForm1.Show
    else
        'Do Nothing


    End If
    


End Sub

Make your if formula return a value to test IE

=IF(H14>J14,1,"")

Hope this helps

zimitry: D