Fill Handle of VBA functions

[JonaHuckleberry]

Hi there,

I wrote a vba function, which works fine. I want it to be applied at each row all the way down an entire column.

I typed the formula into the top cell and I clicked the little box to drag it all the way down to the last row, which drags the formula down but doesn't update the values. (It keeps the value from the top cell, where it was dragged down from.)

If I go through cell by cell (like clicking F2 on each cell), it updates to the correct values, but not otherwise.

Things I've tried that haven't changed anything:

-F9 to recalculate the sheet
-Saved it and closed it and reopened it
-"Calculation Options" is set to automatic
... all to no avail.

I'm bewildered. Any suggestions?
Thanks!

 

[texasalynn]

is the cell formatted as text?

 

[shg]

Are the cells that the function references formatted as text?

 

[JonaHuckleberry]

Nope, it was formatted as 'General', but even when I change that to 'Number', say, they all stay the same.

So if the calculated value from the top cell was 724, then all the cells below say 724, and when I switch them to Number format, they all change to 724.00.

 

[texasalynn]

what is the formula? Do you have the reference anchored that maybe sound not be?

 

[JonaHuckleberry]

The formula is a VBA function I wrote myself. It takes no arguments but iterates through the rows. The only thing I think that might be a problem is the ActiveCell.Row - does that not update when I just drag the formula down? And if that is indeed the problem, is there anything I can do about it?

Function addtosoa()

    targdist = "BA"
    targdist1 = "BD"
    targdist2 = "BG"
    targdist3 = "BJ"
    targdist4 = "BM"
    targdist5 = "BP"
    targdist6 = "BS"
    targdist7 = "BV"

    r = ActiveCell.Row
    c = ActiveCell.Column

    'sums up all the possible RT columns to make sure there's a non-zero RT
    RTsum = Application.WorksheetFunction.Sum(Range("BA" & r), Range("BD" & r), Range("BG" & r), Range("BJ" & r), Range("BM" & r), _
                                              Range("BP" & r), Range("BS" & r), Range("BV" & r))
    
    soa = 0
    
    zero = 0
    
    Select Case zero
        Case RTsum         'current RT is 0, means it was a mis-trigger so don't add any time
            soa = 0
            
        Case Cells(r, targdist1).Value
            soa = Cells(r, targdist2) + 50
            
        Case Cells(r, targdist3).Value
            soa = Cells(r, targdist4) + 200
   
        Case Cells(r, targdist5).Value
            soa = Cells(r, targdist7) + 150
            If Cells(r, targdist6).Value = 0 Then
                soa = soa + 200
            End If
        
        Case Else
            soa = Cells(r, targdist).Value
                        
    End Select
            
    addtosoa = soa
    
    ActiveSheet.Calculate

End Function

 

[JonaHuckleberry]

Ok everyone, I've answered my own question. The problem is indeed the ActiveCell.Row bit, because when you just fill down, it's still that top cell that's the active one. So just changing the function so it takes in the next-door cell and then getting the row number off of that cell means that changes for each row as I fill down and all is fine and dandy.

Thanks for the prompt replies and the moral support!

 

[shg]

If you are invoking this function from the worksheet, you should pass all of the data it needs to compute a result. Otherwise, Excel sees no dependency, and the result will not update unless you force it to recalculate.