0

i have the following method to set to define a ranga:

Set RegJan = Sheets("BD").Range("T3:T50000")

the range is smaller than this, but in order to make it work for some months, i set it to 50000 rows.

i then use this code in the following line of vba:

ws.Cells(8, 2).Value = Application.WorksheetFunction.SumIfs(RegJan, 
EquipaBd, EquipaForm, AgenteBd, AgenteForm) 'Soma dos Registos

This last line i already transformed in a loop so that it calculates in every line i need it,the problem is that i have to do the first line of code for every month of the year an for 6 more variables

is there a better way of doing this? possibly a dynamic way?

0m3r
  • 12,286
  • 15
  • 35
  • 71
P.Costa
  • 89
  • 1
  • 1
  • 10
  • `set r=Sheets("BD").Range("T3").resize(Sheets("BD").Range("T3:T50000").end(xlUp).row,1)` – Nathan_Sav Nov 07 '18 at 16:50
  • 1
    There is a growing consensus that this is a duplicate of a question about finding the last used row in a column. Is that what your question is about? If not, you'll have to clarify. Maybe [edit] your post and include some more code. – FreeMan Nov 07 '18 at 17:10

1 Answers1

0

This might give you some ideas:

With Sheets("BD")

    ' Since T is column #20 and goes to 6 more
    For col = 20 To 26

        ' get last row
        lastRow = .Cells(.Rows.Count, col).End(xlUp).Row

        ' set range
        Set yourRng = .Range(.Cells(3, col), .Cells(lastRow, col))

        ' you might want to change 8,2 to make dynamic
        ws.Cells(8, 2).Value = Application.WorksheetFunction.SumIfs(yourRng , EquipaBd, EquipaForm, AgenteBd, AgenteForm)

    Next
End With
Kubie
  • 1,551
  • 3
  • 12
  • 23