VBA lookup reference

[jakobt]

Want to develop a piece of VBA code.

Basically:

I have a table of data from a3:H1000

In cell B1 I want a reference to sum the cells in column B, based on a criteria in column F.

If the row in column F equals "VAT Return" the sum of column B for the same row should be added to Cell B1.

It is important that the formula in B1 is updated with format like: +B5+ B7 + B12.

 

[Fluff]

Have a look at the SUMIF function https://exceljet.net/excel-functions/excel-sumif-function

 

[Yongle]

if you want to see the references in the cell, try something like this

Sub SumVATItems()
    Dim cel As Range, rng As Range, ws As Worksheet, fStr As String, addr As String, B2 As Range
    Set ws = ActiveSheet
    Set B2 = ws.Range("B2")
    Set rng = ws.Range("A3:H1000").CurrentRegion
    On Error Resume Next
    ws.ShowAllData
    rng.AutoFilter Field:=6, Criteria1:="VAT Return"
    For Each cel In rng.Columns(6).SpecialCells(xlCellTypeVisible)
        addr = Cells(cel.Row, "B").Address(0, 0)
        If cel.Row > 3 Then
            If fStr = vbNullString Then fStr = "=" & addr Else fStr = fStr & "+" & addr
        End If
    Next
    B2.ClearContents
    If Not fStr = vbNullString Then B2.Formula = fStr
    ws.ShowAllData
End Sub

Should yield same result as this formula
=SUMIF(F4:F1000,"VAT Return",B4:B1000)

 

[jakobt]

Thanks something like this I need.

However, the word VAT return will be included as part of a string. For example Q4 2018 VAT return.

 

[Yongle]

 rng.AutoFilter Field:=6, Criteria1:="*VAT Return*"

formula equivalent
=SUMIF(F4:F1000,"*VAT Return*",B4:B1000)