macro to make excel formulas work

[Pasha81]

Hi , I am trying to add something to a macro, to make it calculate the formulas on sheet (Fund1) when new data gets imported there.

look at row 658, The latest price was copied there, but columns C to F are empty, I need those formulas to be recalculated for that row as well.
All columns have dynamic range names, is there a way to put that in a macro and make it calculate formulas?





would appreciate any help.
thanks

[royUK]

They are empty because there is no formuals in them , you need to add the formulas from the row above.

[JBeaucaire]

Try this:

Please Login or Register  to view this content.

=============

You could also highlight your table of data, press CTRL-L and turn on the "LIST" feature for this dataset. That would cause the formulas to be added automatically for you any time you add a new row of data at the bottom of your "list".

[royUK]

I think your code is trying to opy the formulas, but the offsets aren't right. See if thios is better

Please Login or Register  to view this content.

[Pasha81]

JBeau your formula works but its weird because I have to run the macro twice, it doesnt calculate the rows the first time......maybe I havent placed your part of the code in the right spot

Public Sub Example() 
Dim wsSource As Worksheet, wsA As Worksheet, wsB As Worksheet, wsOutput As Worksheet 
Dim rngCell As Range, rngData As Range 
[COLOR="DarkRed"]Dim LR As Long: LR = Range("A" & Rows.Count).End(xlUp).Row[/COLOR] 
On Error GoTo ExitPoint 
Set wsSource = Sheets("Import") 
Set wsA = Sheets("Fund1") 
Set wsB = Sheets("Filter") 
With wsSource 
    Set rngData = .Range(.Cells(2, "C"), .Cells(.Rows.Count, "C").End(xlUp)) 
End With 
For Each rngCell In rngData.SpecialCells(xlCellTypeConstants, xlNumbers) 
    If UCase(rngCell.Offset(, 4)) = "USD" Then 
        Select Case rngCell.Offset(, -2) 
            Case 323 
                Set wsOutput = wsA 
            Case 540 
                Set wsOutput = wsB 
        End Select 
        [COLOR="DarkRed"]Sheets("Fund1").Range("C3:F3").Copy Range("C4:F" & LR)[/COLOR] 
        If Not wsOutput Is Nothing Then 
            With wsOutput.Cells(wsOutput.Rows.Count, "A").End(xlUp).Offset(1) 
                .Value = rngCell.Value 
                .Offset(, 1).Value = rngCell.Offset(, 2).Value 
                .Offset(-2, 2).Resize(2, 4).Formula = .Offset(-3, 2).Resize(2, 4).Formula 
                [COLOR="DarkRed"]Sheets("Fund1").Range("C3:F3").Copy Range("C4:F" & LR)[/COLOR] 
            End With 
        End If 
    End If 
    Set wsOutput = Nothing 
Next rngCell 
ExitPoint: 
Set wsA = Nothing 
Set wsB = Nothing 
Set wsSource = Nothing 
End Sub  

should i place your code somewhere else? thanks

And Roy, thanks for that code but that simply replicates the previous cell in the column, doesnt give the right value

really appreciate ur quick response and help guys, thanks a lot

[royUK]

It copies the formulas from the row above. The results depend on the data feeding the formulas. Perhaps if you explained what your code is supposed to you might get better help.

JB's code copies the range C3 to F3 to the whole the tables Columns C to F.

[Pasha81]

hey Roy,

what my code does is basically copy 2 cells from the sheet 'Import' - One is Date, the other 'Price' ....so everyday there will be a new row and columns C:F must be calculated for these new rows. The formulas for these columns are in the worksheet, I just need them to work on a new row once new information arrives in Column A

thanks

[royUK]

hey Roy,

what my code does is basically copy 2 cells from the sheet 'Import' - One is Date, the other 'Price' ....so everyday there will be a new row and columns C:F must be calculated for these new rows. The formulas for these columns are in the worksheet, I just need them to work on a new row once new information arrives in Column A

thanks

The code that i amended will copy the formulas from the row above, these formulas should then calculate

[royUK]

I think this is what yo want

Please Login or Register  to view this content.

[Pasha81]

getting closer mate,

this time the price was copied in the right spot. But the formulas are wrong -- its copying the previous cell's formula
if if follow the sequence of formula in each column you'll see its not exactly the same, its referring to the cell in the next row.

example -- In column C your macro is copying the formula " =100*B657/$B$2" but it should be "=100*B658/$B$2"

i hope that clears it, JBeau's code was adjusting the formula correctly but for some reason the macro had to be run twice for it to work, the first time it just copied the date and price leaving everything else empty.

thanks for spending time on this, i'm sure its just a little tweak away from being done

[Pasha81]

just bumping this up, i wasn't able to come up with the right adjustment.....its eating my brain haha

[royUK]

Try this

Please Login or Register  to view this content.

[Pasha81]

Dude! excellent! that worked exactly the way i needed
thanks a lot mate