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!
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
Are the cells that the function references formatted as text?
 

JonaHuckleberry

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

Well-known Member
Joined
May 19, 2002
Messages
8,458

ADVERTISEMENT

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

JonaHuckleberry

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

JonaHuckleberry

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

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,206
Messages
5,600,317
Members
414,376
Latest member
NickYOW

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
Top