1

When I am using "AND" condition an error saying "run time error 1004, method range of object global failed" occurs. My code is working fine if I remove and condition may be there is wrong use of "and" Pls any help will be great support. Thanks

Private Sub okButton_Click()
Dim i As Double, dt1 As String, dtt1 As String
Dim dt2 As String, dtt2 As String
dt1 = ComboBox1.Value
dtt1 = CDate(dt1)
dt2 = ComboBox2.Value
dtt2 = CDate(dt2)
Debug.Print dtt2
For i = 2 To 6724
If Range("A" & i).Value >= dtt1 And Range("A", i).Value <= dtt2 Then
Rows(i).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If
Next
End Sub
Community
  • 1
  • 1
Nafis
  • 113
  • 1
  • 11

3 Answers3

2

As VBA doesn't short circuit you should break your AND into two IFs so you don't process the second test if the first isn't passed.

Code also removes Select and turns off ScreenUpdating, would be faster still if you used AutoFilter rather than a range loop.

Sub Recut()
Dim i As Double, dt1 As String
Dim dtt1 As Date
Dim dt2 As String
Dim dtt2 As Date

dt1 = ComboBox1.Value
dtt1 = CDate(dt1)
dt2 = ComboBox2.Value
dtt2 = CDate(dt2)

Application.ScreenUpdating = False

For i = 2 To 6724
    If Range("A" & i).Value >= dtt1 Then
        If Range("A" & i).Value <= dtt2 Then
            With Rows(i).Interior
            .ColorIndex = 36
            .Pattern = xlSolid
            End With
         End If
    End If
Next

Application.ScreenUpdating = True
End Sub
Community
  • 1
  • 1
brettdj
  • 54,857
  • 16
  • 114
  • 177
1

You've got a minor typo in this line:

 If Range("A" & i).Value >= dtt1 And Range("A", i).Value <= dtt2 Then

The error is becauseRange("A", i).Value is not a valid range, because "A" is not a range or a cell.

Change to:

If Range("A" & i).Value >= dtt1 And Range("A" & i).Value <= dtt2 Then
Adam Vincent
  • 3,281
  • 14
  • 38
0

You are using comma (,) in your if condition please replace ',' with '&' Have corrected the code please refer below code:

Dim i As Double, dt1 As String
Dim dtt1 As Date
Dim dt2 As String
Dim dtt2 As Date

dt1 = ComboBox1.Value
dtt1 = CDate(dt1)
dt2 = ComboBox2.Value
dtt2 = CDate(dt2)
Debug.Print dtt2

For i = 2 To 6724
    If Range("A" & i).Value >= dtt1 And Range("A" & i).Value <= dtt2 Then
        Rows(i).Select
        With Selection.Interior
        .ColorIndex = 36
        .Pattern = xlSolid
        End With
    End If
Next
Dhanappa Baddole
  • 16