Search:

Type: Posts; User: Jonmo1; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.94 seconds.

  1. Replies
    7
    Views
    103

    Re: Specifying a Worksheet with VBA

    Mark May be right about needing to specify the book as well as the sheet



    With wb.Worksheets("IIP")
  2. Replies
    7
    Views
    103

    Re: Specifying a Worksheet with VBA

    Try



    With Worksheets("IIP")
    Set rgSort = .Range(.Cells(2, 2), .Cells(count, 7))
    Set rgCol = .Range(.Cells(2, 7), .Cells(count, 7))
    End With
  3. Replies
    6
    Views
    173

    Re: Sumproduct Average

    If all else fails, remember that average = sum / count

    so
    =sumproduct(--(A:A="Supplier A"),--(Month(B:B)=2),C:C)/sumproduct(--(A:A="Supplier A"),--(Month(B:B)=2))
  4. Replies
    6
    Views
    160

    Re: adding date using date() function

    Try

    =DATE(YEAR(A1)+1,MONTH(A1)+2,DAY(A1)+3)
  5. Replies
    4
    Views
    165

    Re: Sum column using NOT equal to criteria

    Try this

    =SUM(B5:B30)-SUMPRODUCT(SUMIF(A5:A30,E7:E11,B5:B30))
  6. Replies
    11
    Views
    310

    Re: why giving me N/A?

    You’re welcome
  7. Replies
    11
    Views
    310

    Re: why giving me N/A?

    #N/A error is still NOT a 0.
    You have to test the cell for error

    Try

    =IF(ISNA(G8),0,IF(OR(G8=0,AB8>=0),0,AB8))
  8. Replies
    8
    Views
    273

    Re: Why 1004 Run Time error at Red Text?

    If the message box produced a range, then the range isn't the problem (it's OK that it's 'larger than' your actual data. Smaller may have been an issue).

    So it must be the ctiteria that is the...
  9. Replies
    8
    Views
    273

    Re: Why 1004 Run Time error at Red Text?

    Maybe the 'Current Region' isn't quite what you think it is..

    Add this line in there, and what does the message box say?


    With Sheets("DataDump").Range("A1").CurrentRegion
    MsgBox .Address...
  10. Re: Delete Rows If Contains Certain Multiple Values

    Try


    Sub delete_exempt()
    Dim c As Range, MyVals As Range, SrchRng As Range
    Dim i As Long, lr1 As Long, lr2 As long, x

    'This is a range containing all the criteria to search for
    lr1 =...
  11. Re: Delete Rows If Contains Certain Multiple Values

    Welcome to the board.

    I think a different approach should be used here.
    The code in this thread worked because it was assumed the value would only exist once.

    You probably need to go row by...
  12. Re: Formula - return bottom non-blank value in a range

    Cutting ranges in half...

    original list of numbers
    1 2 3 4 5 6 7 8 9 10

    Lookup cuts it in half, and looks at the last number of each half..
    1 2 3 4 5 (Is 5 greater than the lookup value?)
    If...
  13. Re: Formula - return bottom non-blank value in a range

    You're welcome..
    9.99999999999999E+307 is just the scientific notation for the largest number allowed in a cell, also called Bignum.

    Lookup assumes your data is sorted in ascending order (it...
  14. Re: Formula - return bottom non-blank value in a range

    Finding the furthest down 'numeric' value, try

    LOOKUP(9.99999999999999E+307,$N$27:$N49)
  15. Replies
    3
    Views
    236

    Re: Question regarding looking up values

    You're welcome.
  16. Re: Simple SUMIFS going wrong - I'm doing something dumb

    Welcome to the board.

    Try

    =SUMIFS('DATA'!C:C, 'DATA'!A:A, 'Results'!A2,'DATA'!B:B,'RESULTS'!B3)
  17. Replies
    4
    Views
    320

    Re: Count ALL days in a month, except Sunday

    You're welcome.
  18. Replies
    4
    Views
    320

    Re: Count ALL days in a month, except Sunday

    Which version of Excel are you using?
    This is for XL2010+
    Assuming A1 is the FIRST day of a given month

    =NETWORKDAYS.INTL(A1,EOMONTH(A1,0),11)
  19. Replies
    3
    Views
    170

    Re: Code Works, but only sometimes?

    You're welcome.
  20. Replies
    3
    Views
    170

    Re: Code Works, but only sometimes?

    When nesting ranges, and you specify the book/sheet on the outside range, you must also specify it on the inside ranges too.
    Also, you can't select a sheet that is not currently active. (you don't...
  21. Replies
    3
    Views
    236

    Re: Question regarding looking up values

    Try

    MATCH(""" & intPmtFreq & """,MMQSAA,0),
  22. Thread: Adding Zeroes

    by Jonmo1
    Replies
    3
    Views
    217

    Re: Adding Zeroes

    Welcome to the board.

    In File - Options - Advanced.
    UNcheck "Automatically insert a decimal point"
  23. Replies
    3
    Views
    68

    Re: complex if formula

    You're welcome.
  24. Replies
    3
    Views
    68

    Re: complex if formula

    Try

    =IFERROR(INDEX('different tab'!$S:$S,MATCH(A9,'different tab'!$B:$B,0))/J9,"")
  25. Thread: Countif problem

    by Jonmo1
    Replies
    1
    Views
    134

    Re: Countif problem

    Yes, the * is being treated as a wildcard.

    Try
    =countif($A$1:$A$2000;SUBSTITUTE(EG43;"*";"~*"))
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4