Issue with function returning a range, and using that range

  • Hey, in my Macro I am trying to use a function to set a range. It is giving me errors when I try to run the code, but I feel like I'm doing mostly correct things. I've done a lot of looking online to try to solve this, and some people are sure that they can resolve the issue, but nothing so far has worked. Pseudocode below:


    [VBA]
    Public rng As Range
    Set rng = Report(trackWkbk.Sheets(strFY), 1) 'Report is a function I will show below. trackWkbk and strFY are variables that I am 99.9% sure are working properly, and the error isn't there.
    rng.Value = 10 'Where I get an error. Run time error '91' Object variable or With block variable not set
    [/VBA]



    [VBA]
    Function Report (ByVal poParentSheet As Excel.Worksheet, ByVal a As Integer) As Range
    Select Case a
    Case 1
    Set Report = poParentWorksheet.Range("B3")
    End Select
    End Function
    [/VBA]


    Does anyone know how to circumvent this? The sheets are in different workbooks

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!