Custom VBA Row/Column Lookup Function

pdbeiu · Aug 24, 2023

To begin, what I ultimately want is to write two custom functions - described below:

Function One - FINDROOM
This function will be sent one argument a course identifier from a different place in the workbook (BUS 3010-001 as an example) and will return the room that this course is scheduled in -- in this case, BUS 3010-001 exists in cell D11 above, so the result should be the cooresponding value from column A, which is 2121

Function Two - FINDTIME
Essentially the same as above. This function will be sent one argument a course identifier from a different place in the workbook (BUS 3010-001 as an example) and will return the time that this course is scheduled -- in this case, BUS 3010-001 exists in cell D11 above, so the result should be the cooresponding value from ROW 2, which is 9:00-9:50

The argument could be any of the courses in C3:U16, or I can certainly use a named range for that.

I've tried working on a custom function called FINDCELL (below) that would just return the resulting cell and then I would extract the row/column identifiers from that to get the information, but am striking out. Any help is certainly greatly appreciated.

Function FINDCELL(Course)

Dim c As Range

For Each c In Worksheets("Rooms").Range("C3:U16")
If c.Value = Course.Value Then
'MsgBox "Found at " & c.Address
FINDCELL = c.Address
CLASSROOM.Value = Worksheets("Rooms").Range(FINDCELL).Value
'MsgBox CLASSROOM.Address
End If
Next c

End Function

Kind Regards,

Paul

JackDanIce · Aug 24, 2023

Hi there,

Your code is more like a procedure than a sub, as it's creating a msgbox with the result, rather than a function output.
Are you passing this function into another macro or used elsewhere?

Try recording a macro for the following steps:

1. Select sheet "Rooms"
2. Select range C3:U16
3. Press CTRL+F (keyboard shortcut for Find)
4. Enter the course identifier
5. Assuming it exists, hit Ok to find that cell and select it
6. Stop recording

At this point, the activecell will be the one searched for (if the course exists) and the cell's row number or column number can be used to return the classroom or time, e.g.

Msgbox "Classroom: " & cells(activecell.row, 1).value (where 1 is column A)
Msgbox "Time: " & cells(2, activecell.column).value (where 2 is row 2)

pdbeiu · Aug 24, 2023

Apologies -- the messages boxes were just in what I was trying to in the function to determine if I was "getting" to the right cell. I believe a custom function is most appropriate because in a seperate worksheet, I'm wanting to return the Room and Time for many different courses.

Kind Regards,

Paul

bebo021999 · Aug 24, 2023

Try one FUNCTION below:

Code:

=FindCourse(Cells contains Course ID, 1 or 2 )

with 2nd argument: =1 (find room, same row), 2 (find time,same column)

In this sample, with E19 = "U03"

Code:

=FindCourse($E$19,1)
=Room 8

Code:

=FindCourse($E$19,2)
=14

VBA Code:

Option Explicit
Function FindCourse(ByVal ID As Range, N As Integer) As String
Dim LookingRange As Range, f As Range
With Sheets("Sheet1")
    Set LookingRange = .Range("A2:U16")
    Set f = LookingRange.Find(ID)
    If Not f Is Nothing Then
        FindCourse = IIf(N = 1, .Cells(f.Row, 1), .Cells(1, f.Column))
    End If
End With
End Function

Book1