Posts by Ranger

Re: Autofilter Mode On

Hi Kris,

You're right, I was assuming that Autofilters were turned off when the workbook was closed.

Bill

Re: Sumif Function

Hi cgviscar,

Like ByTheCringe2, I am not too sure what you are looking for, but if it is a running total for each year then:

=IF(YEAR(B2)=YEAR(A2),A9+B5,B5)

in Cell B9 and copied along will do that.

Bill

Re: Hyperlink Update When Adding Rows

Hi gillrajb,

Give each Section header a Defined Name, e.g. Section1, Section2 etc, then Edit your Hyperlinks and when the dialog box comes up, change the Select a place in this document to Defined Names, then the Hyperlink will move with the Range Name when you add or delete rows.

Bill

Re: Autofilter Mode On

Hi marc,

Private Sub Workbook_Open()
For Each ws In wbDest.Sheets
    If ws.Name <> "Homepage" Then
        ws.Range("A1").AutoFilter
    End If
Next
    
End Sub

Assuming all of your filters are in row 1

Bill

Re: Find The Last Date Of The Week

Hi Reafiddy,

If your dates start in A2, then in B2;

=1-WEEKDAY(A2)+7+A2

copied down should do it.

Bill

Re: Listbox Refresh With Combobox Selection

Hi excelnewbie,

Try the attached.

Bill

Re: Placing Input Box In User Form

Hi Peter,

I'm maybe not explaining it properly, but the attached is a very simple example of what I mean. When you click on an item in the ListBox, the label and TextBox show. If you enter a Project Number, it is allocated to a Dim String and can be used in the rest of your code. In the example, the Project Number is placed into cell A1 of Sheet1.
I have added the Public String sProjectNo in Module1 as a Public variable, so it can be used anywhere in the code.

Bill

Re: Placing Input Box In User Form

Hi Peter,

No, the textbox would appear for the User to input the project number and then, just like the answer to the InputBox, can be used in your code. After entering the project number in the textbox, the textbox and label properties can be set to Visible=False again, so it is basically acting like an InputBox.

Bill

Re: Placing Input Box In User Form

Hi Peter,

Why don't you use a TextBox and a Label with their visible property set to false and in the click event of the ListBox, make them visible

Bill

Re: Find Adjacent Cells On Double Click

Hi Mike,

Quite a change in the requirements there.

Try the attached. I have added a Right-Click event procedure, so that after double clicking the Period, you can right click any of the cells D3:D20 to get back to the original.

Bill

Re: Macro Stops On Top Line

I have tidied the code up a little, removed redundant code etc.

Private Sub CommandButton1_Click()
    ActiveWindow.ActivateNext
    ActiveSheet.Rows("1:4").Delete
    Range("A1").Select
End Sub

Bill

Re: Switching From 1 Spreadsheet To Another Then Deleating Rows With A Macro

Hi Simon,

From the code given, it looks as though he wants to go from one Workbook to another, or am I reading it wrong?

Bill

Re: Macro Stops On Top Line

Hi Bumfield007

There are 2 code start lines in your code. You also need to tell it that it is the ActiveSheet that you want to select the 4 rows in

Private Sub CommandButton1_Click()
 '
         ' Macro1 Macro
         ' Macro recorded 19/01/2007
 '
         
 '
        ActiveWindow.ActivateNext
        ActiveSheet.Rows("1:4").Select
        Selection.Delete Shift:=xlUp
        Range("A1").Select
End Sub

Bill

Re: Round up or down the number in a cell

Hi ystrad,

If you want to do it by code, then:

Sub RoundIt()
    Dim iNum As Integer
    For i = 4 To 8000
        If Cells(i, 2).Value <> "" Then
            On Error Resume Next
            iNum = WorksheetFunction.Find(".", Cells(i, 2)) + 1
            If iNum > 0 Then
                If Mid(Range("B" & i), iNum, 1) >= 2 Then
                    Sheet1.Range("B" & i).Value = WorksheetFunction.Ceiling(Sheet1.Range("B" & i), 1)
                Else
                    
                    Sheet1.Range("B" & i).Value = WorksheetFunction.Floor(Sheet1.Range("B" & i), 1)
                End If
            End If
        End If
    Next i
End Sub

should do it.

Bill

Re: Find Adjacent Cells On Double Click

Hi Mike,

The code in the previous post should obviously be in the Double_Click() event of the Worksheet. Sorry for not mentioning it.

Bill

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Address = "$D$3" Then
        Range("A1:B14").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
            "D2:D3"), CopyToRange:=Range("E2"), Unique:=False
    End If
End Sub

Re: Find Adjacent Cells On Double Click

Hi Mike,

Surely all it is is a filter. If you put a heading in D2 'Category 2' and in E2 'Category 1', then make the text white so that the headers can't be seen. The following code would then do it:

Range("A1:B14").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "D2:D3"), CopyToRange:=Range("E2"), Unique:=False

Bill

Re: Suppress Pivot Table Subtotals

Hi Far,

Go back to the example I posted and try adding a few new regions. Look at the Pivot Table. Do you see any detail rows missing, or any subtotals showing?

Bill

Re: Suppress Pivot Table Subtotals

Hi Far,

It is the Subtotal fields you are hiding, not the particular rows. As the table expands, so the Subtotal fields move with it, but the fields are still hidden, no matter what rows they are in.

Bill

Re: Find Maximum Value From A pre-filtered Range

Hi Upside,

Forgot to put the Command Button on to transfer the data.

Bill

Re: Find Maximum Value From A pre-filtered Range

Hi Upside,

Without seeing your layout, it is difficult to know what cells are available to us, but the attached gives an idea of how it could be done.

Bill