Reputation: 61
I have a bit of code I've written and I'm having trouble with a certain line (Countifs statement). I haven't ever used this in VBA before so I think it might be something to do with Syntax? Please could someone take a look and let me know?
Thanks very much!
Sub TradeCopy()
'Declare Variables
Dim x As Worksheet
Dim y As Worksheet
Dim z As Range
Dim FirstRow As Integer
Dim LastRow As Long
Dim i As Long
Dim j As Long
Dim s As String
Dim t As String
Dim count As Long
Dim startdate As Long
On Error GoTo ERROREND
Application.DisplayAlerts = False
Application.EnableEvents = False
'Setting Values
s = ActiveWorkbook.Sheets("Name Creator").Range("B4")
Set x = ActiveWorkbook.Sheets(s)
t = ActiveWorkbook.Sheets("Name Creator").Range("B5")
Set y = ActiveWorkbook.Sheets(t)
startdate = ActiveWorkbook.Sheets("Name Creator").Range("B3")
'Find Cell where name occurs
Set z = x.Columns("A").Find(what:="trade id", LookIn:=xlValues, Lookat:=xlWhole)
'Return Start Row number
FirstRow = z.Row + 1
'Return Last Row number
LastRow = x.Range("A" & Rows.count).End(xlUp).Row
'Clear Existing Range of Values
y.Rows(2 & ":" & Rows.count).ClearContents
Below is the code giving problems, specifically the "count = " line when running debugger.
'Loop to highlight cells based on conditions
For i = FirstRow To LastRow
count = Application.WorksheetFunction.CountIfs(x.Range("B:B"), x.Range(i, 2), x.Range("L:L"), "<" & startdate)
Rest of code:
If (x.Cells(i, 21) = "Fra" Or x.Cells(i, 21) = "Swap" Or x.Cells(i, 21) = "Swaption" Or x.Cells(i, 21) = "BondOption" Or x.Cells(i, 21) = "CapFloor") And DateValue(x.Cells(i, 12).Value) > startdate And count <= 0 Then
x.Rows.Range("A" & i).Value.Interior.Color = vbRed
End If
Next i
'Loop to check for all 0 Cells and paste values
For j = FirstRow To LastRow
If x.Cells(j, 1).Interior.Color = vbRed Then
x.Rows.Range("A" & j).Value = y.Rows.Range("A" & j).Value
End If
Next j
'Remove Duplicates
y.Columns(2).RemoveDuplicates Columns:=Array(1)
Application.DisplayAlerts = True
Application.EnableEvents = True
MsgBox ("All Done!")
Exit Sub
ERROREND:
MsgBox ("Unexpected Error - Please Seek Assistance or Debug Code")
End Sub
Upvotes: 0
Views: 715
Reputation: 4842
I think you need to change .Range
to .Cells
in below:
count = Application.WorksheetFunction.CountIfs(x.Range("B:B"), x.Range(i, 2), x.Range("L:L"), "<" & startdate)
To:
count = Application.WorksheetFunction.CountIfs(x.Range("B:B"), x.Cells(i, 2), x.Range("L:L"), "<" & startdate)
Upvotes: 2