Applying 2 formulae to columns of varying length

I'm posting this as a separate thread because it is different than my previous question, although it will indirectly solve my previous problem!

30 worksheets each have the same 2 columns of data that get downloaded/updated daily. The columns have a title row. Column A contains the date. Column B contains the price on that date.

Column C is not downloaded, but currently contains a formula contingent on the price change between each row in B. The formula begins in C3 and is intended until the end of the column, reading as follows: =B3-B2, =B4-B3, etc.

Column D is also not downloaded, but contains a formula contingent on Column C. The formula returns a +1 if the price change is positive, -1 if the price change is negative. The formula begins in D3 and is intended until the column, reading as follows: =if(C3>0,1,-1)

Problem: The length of the columns for each worksheet will be the same on all 17 worksheets, but can vary enormously each day once data is downloaded.

2 Questions:

1) Is there a way to apply the formulas in Columns C and D in an automated manner, so only rows with dates and prices are calculated and delineated?

2) On each row for which there is a date and price for all the worksheets, I wish to sum all the -1s and +1s in Column D for the particular date on a separate, collective worksheet. So, if worksheet ABC has a price in B3, I wish to sum the 1 or -1 in Column D with worksheet DEF's column D, etc., all the way to end of the columns with data. Is there a way to perform this function in an automated manner, so that only the columns with numbers are calculated and delineated?

Regards question 1, perhaps along the lines of:

Sub Example()
    Dim ws As Worksheet
    Dim xlCalc As XlCalculation
    On Error GoTo Handler
    With Application
        xlCalc = .Calculation
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name Like "*.de" Then
            With ws
                .Range("C:D").Clear
                With .Range(.Cells(3, "A"), .Cells(.Rows.Count, "A").End(xlUp)).Offset(, 2).Resize(, 2)
                    .Columns(1).FormulaR1C1 = "=RC2-R[-1]C2"
                    .Columns(2).FormulaR1C1 = "=IF(RC3>0,1,-1)"
                End With
            End With
        End If
    Next ws    
ExitPoint:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalc
    End With
    Exit Sub
    
Handler:
    MsgBox "Error Has Occurred" & vbLf & vbLf & _
                "Error Number: " & Err.Number & vbLf & vbLf & _
                "Error Desc.: " & Err.Description, _
                    vbCritical, _
                        "Fatal Error"
    Resume ExitPoint
    
End Sub

Regards question 2 - if it's simply a 3D sum why not just use a 3D sum ?

Put a sheet "START" before first *.de sheet and another "END" after last *.de sheet and then just use:

=SUM(START:END!D3)

You enter 3D formulae like any other "normal" function - if you follow the steps outlined you should be ok.

There are fairly significant limitations regards what you can do with 3D functions - ie SUM, AVERAGE etc... and they are non-conditional.

I suggested the 3D SUM given your sample is summing the same cell across all sheets - there is seemingly no condition governing the cells to be selected (other than their location which is consistent).

I successfully placed the second formula:

=SUM(START:END!D3)

after placing the START and END sheets as instructed.

But when I place the first formula (I am confused about where to put it) using an = sign preceding the formula, I get an error message. Is the first code designed to be functional exactly as written or must I alter it in some way?

Please help!

The first "formula" is not a formula - it's a VBA sub routine.

OK, in case anyone else is as clueless as me (not likely).

To enter the VBA sub routine, I pressed ALT-F11 to get into VBE editor. Then I inserted the code in the first worksheet ("START"). Then I Hit ALT-F11 again. Pressing "run", the VBA sub routine performed exactly as intended.

However, when I update with the latest prices once again via query, all the rows in column D are transformed to '-1'. The VBA sub routine seems to need to be run manually each time. Is there a way to run the VBA sub routine automatically once the latest batch of prices is downloaded?

It might be an idea to outline the process by which the "batches" are downloaded - presumably external query of some sort - but are all queries refreshed simultaneously or is each refreshed separately ?

Thanks for your response.

A macro drives the downloading of all 30 issues and worksheets simultaneously.

I say "issues and worksheets" because for each issue on the list for query (a list of the 30 issues is on a single worksheet), the macro creates a separate worksheet with the two columns of dates and prices for each of these issues.

The update for all issues happens at once, and covers 800 days. Today's update replaces the 800-1 previous days with the previous 800 days of closing prices.