Hi,
I have the following code with which I am getting error with the red text:
Code
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
[COLOR=#800000]Range("J" & fundr).Value = Application.WorksheetFunction.CountIfs(Sheets("Pending Transactions Count").Range("I:I"), cl.Value, _[/COLOR]
[COLOR=#800000]Sheets("Pending Transactions Count").Range("AT:AT"), "*" & fundr.Value & "*") - Application.WorksheetFunction.CountIfs(Sheets("Pending Transactions Count").Range("I:I"), _[/COLOR]
[COLOR=#800000]cl.Value, Sheets("Pending Transactions Count").Range("AU:AU"), "*" & fundr.Value & "*")[/COLOR]
Range("K" & fundr).Value = Range("H" & fundr).Value + Range("J" & fundr).Value
Next fundr
Next clIt 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.