Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA equivalent to Excel Formula (2002/SP3)

    I'm trying to find out why the following VBA code does not produce the same result as its equivalent Excel formula.

    Formula:
    Cell F5 = SUM(OFFSET(C:C,MATCH(9.99E+307,C:C)-1,0,-F3))

    VBA:
    With Application.WorksheetFunction
    rng.Cells(5, 6) = .Small(.Sum(.Offset (Columns(3), .Match(9.99E+307, Columns(3)) - 1, 0, -1 * Cells(3, 6))), 1)
    End With

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA equivalent to Excel Formula (2002/SP3)

    The formula doesn't include SMALL...

  3. #3
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA equivalent to Excel Formula (2002/SP3)

    Corrected, but still the same problem.

    Formula:
    Cell F5 = SMALL(SUM(OFFSET(C:C,MATCH(9.99E+307,C:C)-1,0,-F3)),1)

    VBA:
    With Application.WorksheetFunction
    rng.Cells(9 + I - 2, 3) = .Small((.Offset (Columns(2), .Match(9.99E+307, Columns(2)) - 1, 0, -1 * Cells(3, 3))), 1)
    End With

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VBA equivalent to Excel Formula (2002/SP3)

    The VBA is now missing the Sum! I don't really understand the point of Small with Sum anyway.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA equivalent to Excel Formula (2002/SP3)

    My bad again...sorry. Here it is:

    Cell F5 = SMALL((OFFSET(C:C,MATCH(9.99E+307,C:C)-1,0,-F3)),1)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA equivalent to Excel Formula (2002/SP3)

    Columns(2) in your latest VBA version is column B, not column C, and Cells(3,3) is C3, not F3.

  7. #7
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA equivalent to Excel Formula (2002/SP3)

    And now?

    Cell F5 = SMALL((OFFSET(C:C,MATCH(9.99E+307,C:C)-1,0,-F3)),1)

    vs.

    rng.Cells(6,5) = .Small((.Offset (Columns(3), .Match(9.99E+307, Columns(3)) - 1, 0, -1 * Cells(6, 3))), 1)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA equivalent to Excel Formula (2002/SP3)

    Cells(6, 5) is cell E6, not F5.

    Cells(6, 3) is cell C6, not F3.

    Offset is not available as a member of the WorksheetFunction object; you have to use the VBA functions Offset and Resize instead.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA equivalent to Excel Formula (2002/SP3)

    Using a row offset from a column makes no sense.

    Perhaps it would be better if you explained what you want to accomplish, without using formulas or VBA.

  10. #10
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA equivalent to Excel Formula (2002/SP3)

    Hans,

    I've attached an example of what I'm looking to do. Basically, I want to find the 1st and 2nd smallest numbers in a range, starting from the last value in the range and only looking for a specific number of numbers in this range.

    I'm trying to accomplish the same thing using VBA.
    Attached Files Attached Files

  11. #11
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA equivalent to Excel Formula (2002/SP3)

    The attached file contains simpler formulae in G5:G14 to achieve the results you describe. The following code is included in the Standard Module to produce the same results in H5:H14. Be aware that neither solution will ignore duplicates in your source range. <pre>Public Sub GetLowestNumbers()
    Const SrceCol = 1
    Const TrgtCol = 8
    Const InitTrgtRow = 5
    Const Qty = 10
    Dim ctr As Long

    For ctr = 1 To Qty
    With Application.WorksheetFunction
    Cells(InitTrgtRow - 1 + ctr, TrgtCol) _
    = .Small(Columns(SrceCol), ctr)
    End With
    Next ctr

    End Sub
    </pre>

    Attached Files Attached Files
    Regards
    Don

  12. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA equivalent to Excel Formula (2002/SP3)

    The attached file is a derivative of the file provided <!post=at post 741290,741290>at post 741290<!/post>. In this example both the formulae and the code ignore duplicates in the source,
    The formulae are located in G5:I14 and the code pates the results in K5:K14.
    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black><font color=blue>Option</font color=blue> <font color=blue>Explicit</font color=blue>
    <font color=blue>Public</font color=blue> <font color=blue>Sub</font color=blue> GetLowestNumbers()
    <font color=blue>Const</font color=blue> SrceCol = 1
    <font color=blue>Const</font color=blue> TrgtCol = 11
    Const InitTrgtRow = 5
    <font color=blue>Dim</font color=blue> ctr <font color=blue>As</font color=blue> <font color=blue>Long</font color=blue>
    <font color=blue>Dim</font color=blue> nth <font color=blue>As</font color=blue> <font color=blue>Long</font color=blue>
    <font color=blue>Dim</font color=blue> prev <font color=blue>As</font color=blue> <font color=blue>Variant</font color=blue>
    <font color=blue>Dim</font color=blue> Qty <font color=blue>As</font color=blue> Long

    Qty = 10

    <font color=blue>For</font color=blue> ctr = 1 <font color=blue>To</font color=blue> Qty
    <font color=blue>Do</font color=blue>
    nth = nth + 1
    <font color=blue>With</font color=blue> Application.WorksheetFunction
    <font color=blue>On</font color=blue> <font color=blue>Error</font color=blue> <font color=blue>Resume</font color=blue> <font color=blue>Next</font color=blue>
    Cells(InitTrgtRow - 1 + ctr, TrgtCol) _
    = .Small(Columns(SrceCol), nth)
    <font color=blue>If</font color=blue> Err <font color=blue>Then</font color=blue>
    <font color=blue>On</font color=blue> <font color=blue>Error</font color=blue> <font color=blue>GoTo</font color=blue> 0
    Cells(InitTrgtRow - 1 + ctr, TrgtCol) = ""
    <font color=blue>Exit</font color=blue> <font color=blue>Sub</font color=blue>
    <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    <font color=blue>End</font color=blue> <font color=blue>With</font color=blue>
    <font color=blue>Loop</font color=blue> <font color=blue>While</font color=blue> Cells(InitTrgtRow - 1 + ctr, TrgtCol) = prev
    prev = Cells(InitTrgtRow - 1 + ctr, TrgtCol)
    <font color=blue>Next</font color=blue> ctr
    End <font color=blue>Sub</font color=blue></font color=black></code></div hiblock>
    Attached Files Attached Files
    Regards
    Don

  13. #13
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA equivalent to Excel Formula (2002/SP3)

    Thanks Don. Great help.

    The code finds non-duplicate numbers in the whole range of column A, and lists them in ascending order. What I'm looking for is to list the first two smallest numbers (non-duplicate), provided that:
    1) I only examine the last X numbers in the range, and that
    2) I start from the end of the range.

    Where X is the number of rows in the range, and can vary depending on the need of the user.

  14. #14
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA equivalent to Excel Formula (2002/SP3)

    <P ID="edit" class=small>(Edited by wdwells on 06-Nov-08 13:10. Code error corrected, and extensive code removed from message body)</P>I haven't yet figured out how to achieve your intended results using standard Excel formulae, but the attached file has code embedded which should do the trick. It is currently set to interrogate the bottom 5 rows. You will want to set the Constant Qty to 2.
    Attached Files Attached Files
    Regards
    Don

  15. #15
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA equivalent to Excel Formula (2002/SP3)

    Thanks again Don. That was what I was looking for.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •