Which Excel Data Type Allows me to Select a Cell as a Reference?

[KhanofTarkir]

Hello,

In the following function, is it possible to change the "valRow As Long" and "valCol As Long" argument placeholders in the function to a data type that allows me to select a specific cell (such as C16)? I realize that the current code allows me to select a cell, but I would like to apply dollaring so that I can have relative references when I copy and paste.

Function ADDACROSSSHEETS(valRow As Long, valCol As Long, sheetStart As Integer, sheetEnd As Integer) As Variant
 
 
    For x = sheetStart To sheetEnd
        ADDACROSSSHEETS = Sheets(x).Cells(valRow, valCol).Value + ADDACROSSSHEETS

    Next x
 

End Function

Example:

I want to write in the function that would show up as ADDACROSSSHEETS(C16, 1, 2) instead of ADDACROSSSHEETS(16, 3, 1, 2). This is because I would like to apply dollaring so that I can copy/paste relative references so that ADDACROSSSHEETS(C16, 1, 2) would turn into ADDACROSSSHEETS(D16, 1, 2) in the right adjacent cell and so on. Is this possible?

Thank you!

 

[RoryA]

You would pass a Range object. You can then retrieve its address to use in the code itself. Note that you will have to make this function volatile since it uses indirect references to cells.

 

[KhanofTarkir]

Thanks for the response - would you or someone show me explicitly how to do that in the code? I only just started learning VBA and I haven't worked with Range objects.

Thanks!

 

[NeonRedSharpie]

Function ADDACROSSSHEETS(rng As Range) As Variant

    valRow = rng.Row
    valCol = rng.Column


    For x = 1 To Sheets.Count
        ADDACROSSSHEETS = Sheets(x).Cells(valRow, valCol).Value + ADDACROSSSHEETS
    Next x


End Function

That's the original code I wrote that passes rng as a range.....

Function ADDACROSSSHEETS(rng As Range, sheetStart as integer, sheetEnd as integer) As Variant

    valRow = rng.Row
    valCol = rng.Column


    For x = sheetStart To sheetEnd
        ADDACROSSSHEETS = Sheets(x).Cells(valRow, valCol).Value + ADDACROSSSHEETS
    Next x


End Function

 

[RoryA]

You would add this line (assuming that you're using this in a cell):

application.volatile true

 

[KhanofTarkir]

Ah I see you already anticipated that I would have to use a range object NeonRedSharpie..

Thanks a lot for your help and the original code