Assign worksheet name to function/sub argument

0
votes

Although a lot of posts explain how to assign a whole sheet to a variable, i couldn't find one that explains how to assign the name only to a sub/function argument.

I need to be able to do this because I want to easily switch between worksheets just by changing an argument in my sub/function.

I have 3 routines :

'Sub to run the code and call the other routines
Public Sub runthecode()

Dim ID As Range, Result As Range

ResultLocation = Cell_Address("Pass Rates", "Functional Text", "fra-FRA")
IDLocation = Cell_Address("Regression URLs", "Text-Based Functional", "fra-FRA")


MsgBox ResultLocation

End Sub

-------
' Sub to find, by 2 way look up, the address of a cell

Public Function Cell_Address(WorksheetName As String, Runtype As String, Language As String) As String

With Sheets(WorksheetName)

Call Find_String(Runtype)

Runtype_Row = Selection.Row

Call Find_String(Language)

Language_Column = Selection.Column

Cell_Address = Cells(Runtype_Row, Language_Column).Address(RowAbsolute:=False, ColumnAbsolute:=False)

MsgBox Cell_Address

End With

End Function

-------
' Sub to find a specific string

Public Sub Find_String(Search_String As String)

    Cells.Find(What:=Search_String, After:=ActiveCell, LookIn:=xlFormulas, 
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, 
MatchCase:=False, SearchFormat:=False).Activate

End Sub

In runthecode I call the Cell_Address Function. As you can see, I have a String variable named WorksheetName which I try to pass in the segment

With Sheets(WorksheetName)

Because I assume that it'd be the same thing as saying

With Sheets("Pass Rates")

However, it always seems to take my first worksheet (Pass Rates) and never goes to the second worksheet (Regression URLs) and gives me the error 91 "Object variable not set" (which is just because it can't find the next Runtype string (Text-Based Functional" in the first sheet since that string is only in the second sheet).

Does anyone know how I can pass the worksheet name in a function argument and use it to switch between sheets when calling that function ?

Thanks !

2
vbaexcel
In Cell_Address, you have your With Sheets(WorksheetName)...End With block - but then you immediately call Find_String - _which has an implicit reference to the ActiveSheet - i.e. ActiveSheet.Cells.Find. Cell_address and Find_String should be combined into 1 Function – BigBen
Furthermore you never use your With block. You could remove the With and End With entirely and the code would execute exactly the same. – JNevill
Your usage of activesheet and selection will cause all sorts of issues here. What is the activesheet at the time that this code executes? What is the selection? Also you say Cells(RunType_Row, Language_Column) but... Cells of what... if you want that to be the Cells() of the sheet in your With block then that should be .Cells(Runtype_Row, Language_Column).Address(RowAbsolute:=False, ColumnAbsolute:=False) (notice the . in the front) – JNevill
Worksheet..Name? – AJD
And then just as @BigBen notes you use that Cells() again in your Find_String. Cells of what range/sheet though? You are not being explicit and telling VBA what Cells is referencing so it defaults to ActiveSheet. – JNevill

2 Answers

1
votes

Here's an attempt to get this a little closer to what I think you are intending:

'Sub to run the code and call the other routines
Public Sub runthecode()

    Dim ID As Range, Result As Range

    ResultLocation = Cell_Address("Pass Rates", "Functional Text", "fra-FRA")
    IDLocation = Cell_Address("Regression URLs", "Text-Based Functional", "fra-FRA")

    MsgBox ResultLocation

End Sub

-------
' Sub to find, by 2 way look up, the address of a cell

Public Function Cell_Address(WorksheetName As String, Runtype As String, Language As String) As String

    With Sheets(WorksheetName)
        Call Find_String(Runtype, Sheets(WorksheetName))
        Runtype_Row = Selection.Row     
        Language_Column = Find_String(Language, Sheets(WorksheetName))
        Cell_Address = .Cells(Runtype_Row, Language_Column).Address(RowAbsolute:=False, ColumnAbsolute:=False)
        MsgBox Cell_Address

    End With

End Function

-------
' Function to find a specific string and return the column in which it can be found

Public Function Find_String(Search_String As String, ws as worksheet) as Range 'returns column

    Find_String = ws.Cells.Find(What:=Search_String, After:=ActiveCell, LookIn:=xlFormulas, 
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, 
MatchCase:=False, SearchFormat:=False).Column

End Sub

I've changed that Find_String to be a function that will return a range representing the Column in which the string was found. This way we aren't relying on Activating and Selection.

We are also passing the Find_String function the worksheet that we are interested in so it will know what Cells() you are referencing.

Lastly, The With block now has some use where we say .Cells() to set the Cell_Address.

I can't guarantee this does exactly what you want, but it should get you over the hurdles you are facing now and a little closer to whatever the end goal is.

0
votes

Thank you @JNevill & @BigBen for your feedback and solutions; I have it all working now :)

Here is the working code :

Public Sub runthecode()

Dim http As Object, JSON As Object
Dim ID As String, Result As String
Dim IDLocation As String, ResultLocation As String

Application.ScreenUpdating = False

IDLocation = Cell_Address("Regression URLs", "Text-Based Functional", "fra-FRA")
ResultLocation = Cell_Address("Pass Rates", "Functional Text", "fra-FRA")


ID = Sheets("Regression URLs").Range(IDLocation).Value
Result = Sheets("Pass Rates").Range(ResultLocation).Value

MsgBox Result
MsgBox ResultLocation

End Sub
______________________

Public Function Cell_Address(WorksheetName As String, Runtype As String, Language As String) As String

    Call Find_String(Runtype, WorksheetName)

    Runtype_Row = Selection.Row

    Call Find_String(Language, WorksheetName)

    Language_Column = Selection.Column

    Cell_Address = Cells(Runtype_Row, Language_Column).Address(RowAbsolute:=False, ColumnAbsolute:=False)

End Function
______________________

Public Sub Find_String(Search_String As String, WorksheetName As String)

    Sheets(WorksheetName).Select

    Cells.Find(What:=Search_String, After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate

End Sub

As you can see, I just select the sheet before looking up my string (in Find_String). It's maybe not the most elegant/efficient solution but at least it works.

Thanks !