Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.
Function getSheetName(rng As Range) As String
getSheetName = rng.Parent.Name
End Function
Function getGDPReferTo(ReferTo As String, RangeToLook As Range, instGDP As Integer) As Long
'gets the n-th instance of Practitioner who has Referred to this practice
Dim mySheet As Worksheet
Dim myRow As Long, myCol As Long
Dim firstrow As Long, lastRow As Long, colFrom As Long, colTo As Long
Dim foundOnRow As Boolean, foundInst As Long
Set mySheet = RangeToLook.Parent 'Sheets(ReferFrom)
'get rectange for RangeToLook range
firstrow = RangeToLook.Cells(1, 1).Row
lastRow = RangeToLook.Cells(RangeToLook.Rows.Count, 1).Row
colFrom = RangeToLook.Cells(1, 1).Column
colTo = RangeToLook.Cells(1, RangeToLook.Columns.Count).Column
'search each row in RangeLook from left to right column. If found on a row, then increment counter only once, and if that matches the search
'instance, then exit function
foundInst = 0
For myRow = RangeToLook.Cells(1, 1).Row To lastRow
foundOnRow = False
For myCol = colFrom To colTo
If mySheet.Cells(myRow, myCol).Value = ReferTo And Not foundOnRow Then
foundInst = foundInst + 1
foundOnRow = True
If foundInst = instGDP Then
getGDPReferTo = myRow
Exit Function
End If
Else
'do nothing
End If
Next myCol
Next myRow
getGDPReferTo = 0
End Function
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
Formula not working in excel =SUMPRODUCT | 4 | 41 | |
excel 2016 program to loop through scripts | 6 | 33 | |
Excel Formula | 5 | 43 | |
Count Unique Values in column if cell matches | 3 | 24 |
Join the community of 500,000 technology professionals and ask your questions.