CODEWITHSUNDEEP
vbaexcel
Alex N.
Alex N.

Reputation: 15935

Why updating value in a range does not update corresponding cells?

I am running into an issue that I can't quite figure out (I am a total n00b in VBA). I have a function that populates a set of cells based on values in another table. Basically I provided with a parameter(state name), it goes it another Sheet, looks up that State and returns all rows with population values for that state. Here is the function:

Public Function MyFunction2(parVal As String)
    myRange = Worksheets("Dashboard").Range("A20:A3000")
    rangeCounter = 1
    For Each cell In Worksheets("StateList").Range("A2:A60").Cells
        If cell.Value = parVal Then
            stateRow = cell.EntireRow
            statePopulation = stateRow(1, 8)
            myRange(rangeCounter, 1) = statePopulation

            rangeCounter = rangeCounter + 1
        End If
    Next cell
    MyFunction2 = rangeCounter
End Function

It looks up values correctly and it plugs them into the myRange correctly, but they never appear in the actual Sheet (Dashboard).

Would really appreciate any pointers as to why...

Upvotes: 1

Views: 1253

Answers (2)

jgridley
jgridley

Reputation: 131

There is an issue with how you are getting the range. Try it like this:

Dim myRange As Range
Set myRange = Worksheets("Dashboard").Range("A20:A3000")

And change this:

myRange(rangeCounter, 1) = statePopulation

to this:

myRange.Cells(rangeCounter, 1) = statePopulation

Also, if this is or is getting called from a UDF, you will get an error because you can't update other cells from a UDF.

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96753

Your UDF will only return a single integer value to the cell in which the function resides. UDF s can't change other worksheet cells.

Upvotes: 2

Related Questions