Fill Handle of VBA functions

JonaHuckleberry

New Member
Joined
Mar 9, 2011
Messages
5
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!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Are the cells that the function references formatted as text?
 
Upvote 0
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.
 
Upvote 0
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?

Code:
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
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top