VBA equivalent to Excel Formula (2002/SP3)

Topic

New Reply

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

Reply | Quote

Replies

The formula doesn’t include SMALL…

Reply | Quote

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

Reply | Quote

The VBA is now missing the Sum! I don’t really understand the point of Small with Sum anyway.

Reply | Quote

My bad again…sorry. Here it is:

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

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

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

Reply | Quote

The attached file is a derivative of the file provided at post 741290. 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.

Option Explicit
Public Sub GetLowestNumbers()
Const SrceCol = 1
Const TrgtCol = 11
Const InitTrgtRow = 5
Dim ctr As Long
Dim nth As Long
Dim prev As Variant
Dim Qty As Long

Qty = 10

For ctr = 1 To Qty
Do
nth = nth + 1
With Application.WorksheetFunction
On Error Resume Next
Cells(InitTrgtRow - 1 + ctr, TrgtCol) _
= .Small(Columns(SrceCol), nth)
If Err Then
On Error GoTo 0
Cells(InitTrgtRow - 1 + ctr, TrgtCol) = ""
Exit Sub
End If
End With
Loop While Cells(InitTrgtRow - 1 + ctr, TrgtCol) = prev
prev = Cells(InitTrgtRow - 1 + ctr, TrgtCol)
Next ctr
End Sub

Reply | Quote

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.

Reply | Quote

(Edited by wdwells on 06-Nov-08 13:10. Code error corrected, and extensive code removed from message body)

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.

Reply | Quote

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

Reply | Quote