CODEWITHSUNDEEP
vbaexcel
runfastman
runfastman

Reputation: 947

Excel VBA row calling cell on multi cell paste (Not ActiveCell.Row)

I have a VBA script that I use the calling cell to do some processing. It works great when I only paste it into one cell but if I paste into multiple cells, ActiveCell is always the first selected cell in the range and so all the cells get the same value.

How do I get the row of the cell that the function is being called from?

Example VBA - (in one cell do =TestCallCell() then copy and paste into several cells)

        Function TestCallCell() As String
            curRow = ActiveCell.Row
            TestCallCell = curRow
        End Function

Upvotes: 0

Views: 145

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

You do not want to use ActiveCell in a UDF, as it will change as the Active Cell changes.

Use Application.Caller. It will use the cell from which the function is called and not the active one.

Function TestCallCell() As String
    curRow = Application.Caller.Row
    TestCallCell = curRow
End Function

enter image description here

Upvotes: 2

Related Questions