Posts by Ranger

Re: Sumproduct With Criteria

Hi Bluebells,

You don't need the ABS if you want to do it your way. If you want to change the values that you are looking for, then:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A20,E1:E5,0))),--(ISNUMBER(MATCH(C1:C20,F1:F3,0))),--(B1:B20>=$G$1),--(B1:B20<=$G$2),B1:B20)

allows you to change the Min and Max values in cells G1 and G2 at any time.

Bill

Re: Highlight Every Other Row

Hi ZeroMan001,

If you want to run code from a Command Button each time you hide or unhide rows, then:

Sub ColorRows()
    Dim lRnge As Long
    lRnge = Sheet2.Range("A65536").End(xlUp).Row
    Rows("7:" & lRnge).Interior.ColorIndex = vbnone
    For i = 7 To lRnge
        If i - 2 * Int(i / 2) > 0 Then
            If Rows(i).Hidden = False Then Rows(i).EntireRow.Interior.ColorIndex = 6
        End If
    
    Next i
End Sub

should do it.

Bill

Re: Select Range Of Cells In A Form

Hi Jon,

Roy is correct. Add a RefEdit control onto your form, run your form as normal and click the collapse button to the right of the RefEdit control. This will allow you to select a Range, including the Sheet Name. You can replace the TextBox with the RefEdit control.

Bill

Re: Select Range Of Cells In A Form

Hi Jon,

The following code can be put in the Enter or double click event of the textbox.

Me.Hide
    Dim UserRange As Range
    
    On Error Resume Next
    Set UserRange = Application.InputBox(Prompt:="Select a Range", Title:="YOUR RANGE", Type:=8)
    UserRange.Select
    Me.TextBox1.Value = UserRange.Address
    On Error GoTo 0
    Me.Show

I have used TextBox1 and UserForm1, you may have to adjust

Bill

Re: Conditional Formatting With Formulas

Hi Canadian diva,

Try:
=AND($K2="",$J2+2<TODAY())

Bill

Re: Combine Cells Containing Partial Dates

Hi KJ,

The only thing I was thinking was that if the values are being used elsewhere, the Date formula will give 1900 values for 00 in column H, whereas using the built up formula will give 2000 dates.

Bill

Re: Min Row And Max Row Of A Selection

Hi Blibo,

Sub SelectRange()
    Dim UserRange As Range
    Dim iMin As Integer
    Dim iMax As Integer
    On Error Resume Next
    Set UserRange = Application.InputBox(Prompt:="Select a Range", Title:="YOUR RANGE", Type:=8)
    UserRange.Select
    iMin = UserRange.Cells(1, 1).Row
    iMax = UserRange.Rows.Count - 1 + UserRange.Cells(1, 1).Row
    Range("A1").Value = iMin
    Range("A2").Value = iMax
    On Error GoTo 0


    
End Sub

Bill

Re: Combine Cells Containing Partial Dates

Hi Timbo,

In J3:

=VALUE(G3&"/"&H3&"/"&TEXT(I3,"00"))

and format J3 as Custom mmm-yyyy

Bill

Re: Arrays With A Date Stipulation

Hi Billyj,

The attached shows how you can filter out 9 months from the first of any Month and Year that you want to start with. Enter the Start Date (1st of any month) in the format dd/mm/yy and the 9 months from that date will be filtered. You could add something like this to your code and then copy just the visible cells across, rather than the full range as you do now.

I hope this can be of some help to you.

Bill

Re: Gutted - Conditional List

Hi airpa,

The attached will list the available staff by shifts. I am not sure if this is what you are looking for or not.

Bill

Re: Summing By Month From Weeks

Hi Tesa,

Have attached a workbook with the Sumproduct formula used to sum the amounts by month.

I hope this is the information that you are looking to get from your spreadsheet.

Bill

Re: Summing By Month From Weeks

Hi Tessa,

=SUMPRODUCT((MONTH(Weeks)=1)*(Widget_Qty))

As long as the Weeks and Widget_Qty are over the same number of columns e.g. B2:L2 and B3:L3, then this should give you the answer you're looking for. Just change the 1 to a 2 for February etc.

Bill

Re: &quot;Case Else&quot; - Formula is not calculating the correct averages

Hi DMES 22,

I have attached a spreadsheet using Average formulas on the COVER sheet (I don't know which sheet your summaries are on. As you can see, the amount of code is much less and easier to read and I believe it gives the Average results you are looking for. The formulas include all sheets between Sheet2 and Sheet3 (Any sheets between those 2 will be included). If you want to exclude a sheet, just drag it over to the right of Sheet3. At the moment, the Average figures include OPPORTUNITY MAP. Just drag the OPPORTUNITY MAP tab over to the right of Sheet3 and then click the button on the COVER sheet and the Averages will be recalculated excluding OPPORTUNITY MAP. I have included code to remove the formulas after entering the Averages, but there is no reason why they could not be left as formulas, then you have a dynamic update without the need for the Command Button and code.

Bill

Re: Disabling Calculations During Macro

Hi leper,

Application.Calculation = xlCalculationManual
    Application.Calculation = xlCalculationAutomatic

are the lines to turn Calculation on and off in VBA code

Bill

Re: Formula to Select Text Length

Hi kramer 42,

IF(ISNUMBER($b1), in front of your formula and ,"") at the end should fix it.

Bill

EDIT; The comma after ($B1) is included in the formula

Re: &quot;Case Else&quot; - Formula is not calculating the correct averages

Hi DMES 22,

You have to zero 'n' before the second set of code runs, it is starting at 3 at the moment.

Bill

Re: Vba To Sum With Condition

Hi mikeburg,

Can you change the line:

lng1099sTotal = WorksheetFunction.SumIf(Range("F7:H70"), ">600", Range("F7:H70"))

to

lng1099sTotal = WorksheetFunction.SumIf(Range("F7:H70"), ">=600", Range("F7:H70"))

so that it takes in values exactly equal to 600.

Bill

Re: &quot;Case Else&quot; - Formula is not calculating the correct averages

Hi DMES 22,

Your code is averaging at 17.5 in both M & N over 3 sheets with me.

Bill

Re: 9 Digit If Formula

Hi Kramer42,

The formula would have to be:

=IF(LEN(A1)<>9,"",A1)

so that it would be "" if greater than 9 digits

Bill

Re: Populate Userform Combobox From Worksheet

Hi bancey,

If your unique list is in Sheet1 range A2:A100, then the Row Source for the ComboBox should be Sheet1!A2:A100.

Bill