# Thread: VBA equivalent to Excel Formula (2002/SP3)

1. ## 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. ## Re: VBA equivalent to Excel Formula (2002/SP3)

The formula doesn't include SMALL...

3. ## 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. ## 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.

5. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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.

11. ## 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>

12. ## 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>

13. ## 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. ## 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.

15. ## 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
•