Issue with formula in VBA

Question

I have a macro which identifies the difference of value between my second visible cell in column B and all my other visible cells in column B. Then the macro returns me the difference of value in the second visible cell in column K (in this case on the screenshot 52479,85 EUR)…

I would like that the macro automatically assigns the difference of value found (in this case 52479,85) by adding it at the existing value in the last cell in column B (in this case 556,32).

In order to do that: I added this line at the end of my code: Range("B" & LastRow).Formula = Range("B" & LastRow).Value + Range("K" & secondRow).Value However it does not work, the macro does not add the 52479 to the 556,32 in my last cell in column B.

Thanks a lot in advance for your help. Xavi

Sub differencetoassign()
Dim i As Long, counter As Long
Dim LastRow As Long
Dim secondcell As Range
Dim r As Range
Set r = ActiveCell
Dim secondRow As Long
LastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
Range("B1").Activate
    For i = 2 To LastRow 'assuming a header row not to be counted
If r.Rows(i).EntireRow.Hidden = False Then counter = counter + 1
        If counter = 2 Then
            Set third secondcell= r.Cells(i, "A")
            Exit For
        End If
    Next i
Debug.Print secondcell
Debug.Print LastRow
secondRow = secondcell.Row
Debug.Print secondRow
Range("B" & LastRow).Formula = Range("B" & LastRow).Value + Range("K" & secondRow).Value
End Sub

Range("B" & LastRow) = Range("B" & LastRow) + Range("K" & secondRow) — Damian
I wrote you some code with a different approach. Check it out and if it works for you, consider marking it as the correct answer. — Damian

Damian Damian · Accepted Answer · 2019-05-24T09:45:27

This is a different approach to your problem, much more efficient and less likely to fail:

Option Explicit
Sub differencetoassign()

    Dim LastRow As Long
    Dim MainValue As Double 'second visible cell on column B
    Dim SubstractValue As Double 'will be summing the visible cells on column B after the second
    Dim AddValue As Double 'last visible value on column B
    Dim OutPutValue As Range
    Dim C As Range 'when looping through cells, For Each is the fastest option

    'Using with will allow you to reference a sheet so doesn't matter where you run the macro
    'the macro will only change that sheet
    With ThisWorkbook.Sheets("MySheet").AutoFilter.Range 'Change MySheet for the name of your working sheet
        AddValue = .Range("B" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).End(xlDown) 'now we have the value you want to add
        MainValue = .Range("B" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row) 'the main value
        Set OutPutValue = .Range("K" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row) 'where we will put the output
    End With

    'Now the substract values
    With ThisWorkbook.Sheets("MySheet")
        LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row 'Last row on filtered data
        For Each C In .Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible) 'loop only through visible cells
            If Not C = MainValue Then 'We don't want the main value to be added here, so we use this to skip it
                SubstractValue = SubstractValue + C
            End If
        Next C
    End With

    'Finally we put the value
    OutPutValue.Value = MainValue - SubstractValue + AddValue   

End Sub

Issue with formula in VBA

1 Answers