VBA - working with multiple variables and columns

[salman94]

Hi,

I have the following code with which I am getting error with the red text:

Dim rngTradeDates As Range
Dim dateCol As Range
Dim cl As Range


Set rngTradeDates = Range("TradeDates")
Set dateCol = Range("J1")


For Each cl In rngTradeDates.Cells


dateCol.Value = "Net Positions as of" & " " & cl.Value
dateCol.Font.Bold = True
Set dateCol = dateCol.Offset(, 3)

Dim rngfunds As Range
Dim fundr As Range

Set rngfunds = Range(Range("G2"), Range("G2").End(xlDown))
For Each fundr In rngfunds.Cells

Range("J" & fundr).Value = Application.WorksheetFunction.CountIfs(Sheets("Pending Transactions Count").Range("I:I"), cl.Value, _ ​

Sheets("Pending Transactions Count").Range("AT:AT"), "*" & fundr.Value & "*") - Application.WorksheetFunction.CountIfs(Sheets("Pending Transactions Count").Range("I:I"), _​

cl.Value, Sheets("Pending Transactions Count").Range("AU:AU"), "*" & fundr.Value & "*")​

Range("K" & fundr).Value = Range("H" & fundr).Value + Range("J" & fundr).Value​

Next fundr​

Next cl

It may be complicated so let me briefly explain. I am using the countifs function to get a number from a column from another sheet called "Pending Transactions Count" and subtracting another number from a different column from "Pending Transactions Count". Each countifs function has 2 criteria.

I think my problem is in the way I have set up the variables and dimming them. Also I would like to repeat this formula for each dateCol column. Let me know if you have any questions.

 

[Fluff]

Try

fundr.offset(,3).Value = Application.WorksheetFunction.CountIfs...

and

fundr.Offset(, 4).Value = fundr.Offset(, 1).Value + fundr.Offset(, 3).Value