Pass variables from sheet to a Sub/Function

[stanleytp]

Fairly new to all this but I seem to making my way with the assist from Google. Stuck on this one:

Trying to pass the value in a cell on sheet to a Function but get a compile error on the 'Set rng" line.

Sub Q1_button()

Dim rowstart As Integer
Dim rowend As Integer
Dim colstart As Integer
Dim colend As Integer
Dim activesheetstr As String

Dim rng As Range

activesheetstr = Worksheets("FY22Q2").Cells(2, 19)
rowstart = Worksheets("FY22Q2").Cells(3, 19)
rowend = Worksheets("FY22Q2").Cells(4, 19)
colstart = Worksheets("FY22Q2").Cells(5, 19)
colend = Worksheets("FY22Q2").Cells(6, 19)

'activesheetstr = "FY22Q2"
'rowstart = 2
'rowend = 90
'colstart = 6
'colend = 6

Set rng = getData activesheetstr, rowstart, rowend, colstart, colend

rng.Select
Call Update_Rev(rng)

End Sub


Function getData(currentWorksheet As Worksheet, dataStartRow As Integer, dataEndRow As Integer, DataStartCol As Integer, dataEndCol As Integer)

Dim dataTable As Range
Set dataTable = currentWorksheet.Range(currentWorksheet.Cells(dataStartRow, DataStartCol), currentWorksheet.Cells(dataEndRow, dataEndCol))

Set getData = dataTable

End Function

 

[Joe4]

Welcome to the Board!

This line:

Set rng = getData activesheetstr, rowstart, rowend, colstart, colend

should look like this:

Set rng = getData(activesheetstr, rowstart, rowend, colstart, colend)

But noe that your first argument is problematic.
You declared "activesheetstr" as string like this:

Dim activesheetstr As String

but your function is looking for a worksheet object, not a string:

Function getData(currentWorksheet As Worksheet, dataStartRow As Integer, dataEndRow As Integer, DataStartCol As Integer, dataEndCol As Integer)

 

[stanleytp]

Thank you. Fixed both mistakes. All good now.

 

[Joe4]

Excellent!
Glad I was able to help.