Amend code in VBA function?

[dgt]

Hi all

I have found the following custom function very useful.

Function Nth_Occurrence(range_look As Range, find_it As String, _
Occurrence As Long, offset_row As Long, Offset_col As Long)
Dim lCount As Long
Dim rFound As Range
Set rFound = range_look.Cells(1, 1)
For lCount = 1 To Occurrence
Set rFound = range_look.Find(find_it, rFound, xlValues, xlWhole)
Next lCount
Nth_Occurrence = rFound.Offset(offset_row, Offset_col)
End Function

However it would be handy if the code could be amended to accept a variable input rather than a fixed input.

For example, in the following list

A
0
0
5
0
9
3
0
0
7

could the function be adapted to find the 1st/2nd/3rd instance etc of a positive value greater than 0. i.e. 1st = 5; 2nd = 9; 3rd = 3 and so on.

Not sure if I am expecting too much from a custom function but likewise, could a formula be used within the function to replace the fixed value. I would like all of the other variants to stay the same; so I guess this just affects the part of the code - "find_it As String,".

I have looked at a number of similar functions but they all require a fixed value as their input (find_it) rather than a variable value.

Hope this is possible ...David

[IBihy]

Hello dgt,

Well, yes, you could, of course.
First of all, the function does allow "variable" input, it is the responsibility of the caller of the function to determine the range_look. It can be anything between one cell to the limit of rows in Excel (65thou something).
For the other part of the question, you can iterate through the range checking for unwanted values, zero, for example.

Regards,
Isabella

[IBihy]

As for formulas, check out VLOOKUP in Excel help.

[dgt]

Hi Isabella

Thanks for your reply but I think we are at crossed purposes.

I already use the function with ranges and tables as the "range_look" but the "find_it As String" requires a fixed input (either text or value); whereas I need this find the 1st/2nd/3rd Instance etc of the positive values in a range.

A
0
0
5
0
9
3
0
0
7

In other words the 1st positive value in the above list would be 5, the 2nd would be 9, the 3rd would be 3 and the 4th would be 7. This is because the contents and values of a column will vary considerably.


For the other part of the question, you can iterate through the range checking for unwanted values, zero, for example.
Due to my very limited knowledge of VBA, I don't really understand what you are saying here.

Regards ...David

[mikerickson]

It sounds as if you want the UDF's find_it argument to act in the same way that SUMIF's criteria argument.
Is that the case?

If that is the case, then try this. Unlike the OP function, this can be called from a worksheet.

Function Nth_Occurence_Crit(range_look As Range, find_criteria As String, Occurrence As Long, offset_row As Long, Offset_col As Long)
Dim lCount As Long
Dim oneCell As Range

For Each oneCell In range_look
lCount = lCount + Application.CountIf(oneCell, find_criteria)
If lCount >= Occurrence Then Exit For
Next oneCell

If oneCell Is Nothing Then
Nth_Occurence_Crit = CVErr(xlErrNA)
Else
If oneCell.Row + offset_row <= 0 Or oneCell.Column + Offset_col <= 0 Then
Nth_Occurence_Crit = CVErr(xlErrRef)
Else
Nth_Occurence_Crit = oneCell.Offset(offset_row, Offset_col)
End If
End If
End Function

[dgt]

Hi Mike

That sounds like it should work but all I'm getting is #N/A errors whether I test it on the single column example as in my first post or if I use an extended range and use criteria based upon another column (like SUMIF).

e.g. =Nth_Occurence_Crit($J$5:$W$50,$J$5:$J$50<>"TOTALS",1,0,0)

Any ideas ...David

[mikerickson]

If your column of numbers is in J5:J50, the formula would be
=Nth_Occurence_Crit($J$5:$J$50, "<>TOTALS",1, 0, 0)

Which would return the first entry in J5:J50 that is not "TOTALS".

[dgt]

Hi Mike

Just about to switch off when I saw your reply and realised that I had not made correct use of the "quotes" for single column entry, which now works fine.

However, still can't get it to work where the range_look is a different column or range to the criteria.

e.g. =Nth_Occurence_Crit($W$5:$W$50,"$J$5:$J$50<>TOTALS",1,0,0)
or
Nth_Occurence_Crit($J$5:$W$50,"AND($J$5:$J$50<>TOTALS,$W$5:$W$50>0",1,0,0)

As I said earlier, not sure if this is possible but at least part resolved.

Thanks ...David

[mikerickson]

The arguments of the function work as:

range_look is the range to search

find_criteria is a criteria to be matched. This function uses criteria like those use in COUNTIF

occurrence is the number of occurence that you specify

offset_row and offset_columns are the offset from the cell in range_look which to return a value.

"still can't get it to work where the range_look is a different column or range to the criteria."
The criteria argument does not have a range associated with it, so I'm not sure what the problem is. Perhaps changing the offset_row or offset_column arguments will fix the problem.

I've attached a workbook that demonstrates Nth_Occurence_Crit.

Also, the offset arguments make it such that Nth_Occurence_Crit should be a volatile function. (add Application.Volatile as the first line of the function after "Function Nth_..." ). This makes for a very slow spreadsheet.

Perhaps if you described what you want to do, rather than the "change this UDF" in the OP, a non-volatile UDF could be crafted.

[dgt]

Hi Mike

Thanks for taking the trouble to create the test workbook. Not been around much today, so not had a chance to do much experimenting with your code, although it does work fine on a single column.

My original intention was to hopefully obtain a way of incorporating formulas/functions in the find_it part of the function; which has now developed into your find_criteria; so that I could use it in various ways rather than with a specific application.

Perhaps the best way to demonstrate what I am trying to achieve with a specific project is in the attached workbook, although despite the time I have spent on it, I have not yet achieved the desired results.

I am trying to create a summary worksheet that contains only the rows where there is a positive balance for a particular week. In the attached workbook. Summary (1) worksheet depicts the actual end result for the week indicated in B2.

Summary (2) now includes your formula in K6:K15 but I have yet to set up the Index/Match formula in the Collections worksheet to reflect the week selected in B2. I have also found that using various code or formulas in columns to the right of the main data in the Collections worksheet cause circular references, especially if they are array formulas, as with the Chips Pearson 'NonBlanks' function.

In this instance, I was hoping to combine the formula selecting the correct column in the Collections worksheet, within your revised function, so as to reduce the need for helper columns.

I already have code which I should be able to amend to use with the Summary worksheet that will insert the required number of rows for the data, as this will vary on a week to week basis.

Hope this does not put you off now ...David

NB: In the interests of keeping things straight, this has now developed in to a cross-post as I had posted this overall requirement on the Excel Help Forum.