-2

I’ve created a few macros to analyze rain data from an airport. The macros work fine when I type them manually into each cell.

When I copy the macros down the columns, the new rows correctly populate with the macro formula and reference cells. However, what displays and is set as the value for the cell is the value from the first cell that I copied the formula down from.

For example, C5 has the formula: “=twoday(B5)” and has a value of 0.2. When copied down, I want C6, C7, etc. to show “=twoday(B6)”, “=twoday(B7)”, etc. And, they do, but they also show the value 0.2, but should show a different value. When I click the cell then the formula bar and hit enter, it correctly shows the value I want.

Rather than hitting enter on 4000 cells, how can I correctly copy the macros down columns? I've tried checking the calculation settings for automatic/manual (and using F9), and tried both to no avail. Anything else I can try?

Here is the code:

Public Function twoday(rain_current_day)

'Allow x to be the range of cells that we check for the rain data
'Offset = row, column

Dim x As Range
Set x = Range(ActiveCell.Offset(-1, -1), ActiveCell.Offset(, -1)) 'adjacent 
cell (left) and 1 cell up (2 data points total)

'find out if any of the important rows are blank or text data
If IsNumeric(x.Cells(1, 1)) And Not IsEmpty(x.Cells(1, 1)) Then
    twoday = WorksheetFunction.Sum(x)
    Else
    twoday = ""
End If

End Function
Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
Robert Townsley
  • 1
  • 2
  • @urdearboy sorry, I've added the code now. Any thoughts? I know the code is somewhat useless as I can get the same results from adding cells within Excel and not use VBA at all, but this is a first test with me with VBA, – Robert Townsley Feb 26 '19 at 03:37
  • Try pasting the formula, then select a different cell, then re-calculate your sheet. Then, select a different cell and re-calculate. If you repeat this enough, you will see that the function is driven by the selected cell and not the input. Notice the input for this function is never even used in your code. This result (issue) you are seeing is by design - again, for us to help we would need to know what you *meant* to do. – urdearboy Feb 26 '19 at 03:41
  • @urdearboy sorry, I'm new here and unfamiliar with how to upload Excel files. So I'll try to explain: I have a column of daily rain data for the whole year. I am trying to find the 2-day, 7-day, 14-day, etc. totals for determining accumulation rates. The above code would be for determining the 2-day accumulation. The rain data is in column H. In column I, I am using the above code. For example, in I26 I have "=twoday(H26)", etc. The cells function fine, and give me the correct answers. However, when I copy down the formula, it displays the copied cells' value and not a new value – Robert Townsley Feb 26 '19 at 03:45
  • @urdearboy I can't include an image in my original post because I don't have enough reputation. Does this link work? - https://i.stack.imgur.com/1XaDe.png That's correct. The range that I26 should be summating is H25:H26. Similarly, I27 should be "=twoday(H27)" and summating H26:H27 However, I26 should be left blank because I25 has text or is blank. The data in column I should begin populating in I27. – Robert Townsley Feb 26 '19 at 03:52

1 Answers1

1

The main issue is that your function is not really taking a input. Thus, your output is independent of the cell you select to put into your formula.

Try something like the below function instead. Notice this function is driven by the input you give it. No matter what cell you put into the formula, it will always sum the input cell and the cell above it. 

Public Function TWODAY(Target As Range) As Double

    TWODAY = Target + Target.Offset(-1)

End Function
urdearboy
  • 14,439
  • 5
  • 28
  • 58