CODEWITHSUNDEEP
vbaexcel
Dames
Dames

Reputation: 61

VBA Countifs error

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

Answers (1)

neelsg
neelsg

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

Related Questions