Run-Time error “1004” Method “Range” of Object' _Global Failed."

Question

'I am just trying to select all cells that fall within a certain range (>= 751 and <=1600) for 5 days. But I keep getting this Run-Time error "1004" Method "Range" of Object' _Global Failed. Any help is appreciated.

Public Sub tank_2()

Dim t As String
Dim var As Range


Set var = Application.InputBox("enter range", , , , , , , 8)
t = var.Address


For Each cell In Range(t)
    If myrng = Empty And cell >= 751 And cell <= 1600 Then

        myrng = cell.Address(0, 0)
    ElseIf cell >= 751 And cell <= 1600 Then
        myrng = myrng & "," & cell.Address(0, 0)
    End If
    
Next cell

For Each cell In Range(t).Offset(0, 18)
    If myrng = Empty And cell >= 751 And cell <= 1600 Then

        myrng = cell.Address(0, 0)
    ElseIf cell >= 751 And cell <= 1600 Then
        myrng = myrng & "," & cell.Address(0, 0)
    End If
    
Next cell

For Each cell In Range(t).Offset(0, 36)
    If myrng = Empty And cell >= 751 And cell <= 1600 Then

        myrng = cell.Address(0, 0)
    ElseIf cell >= 751 And cell <= 1600 Then
        myrng = myrng & "," & cell.Address(0, 0)
    End If
    
Next cell

For Each cell In Range(t).Offset(0, 54)

    If myrng = Empty And cell >= 751 And cell <= 1600 Then
        myrng = cell.Address(0, 0)
    ElseIf cell >= 751 And cell <= 1600 Then
        myrng = myrng & "," & cell.Address(0, 0)
    End If
    
Next cell

For Each cell In Range(t).Offset(0, 72)

    If myrng = Empty And cell >= 751 And cell <= 1600 Then.   
        myrng = cell.Address(0, 0)
    ElseIf cell >= 751 And cell <= 1600 Then
        myrng = myrng & "," & cell.Address(0, 0)
    End If
    
Next cell
   
Range(myrng).Select

End Sub

For a starter, myrng is neither declared nor has it anything assigned. It's not neccessary to read the address of a range and then convert it back to a range, you can use For Each cell in var. — FunThomas

Davesexcel Davesexcel · Accepted Answer · 2020-09-22T12:42:56

You could dim myrng as a string and we need to determine if len(myrng)=0 to place a "," or not.

If you wanted to select the cells that match that criteria, the myrng needs to be correct.

This is what happens when you have incorrect myrng

If myrng=empty , I'm not sure what that is doing.

Here is the beginning of the code fixed up a bit, you will have to correct the rest of the code.

Dim t As String, myrng As String
Dim var As Range, x

Set var = Application.InputBox("enter range", , , , , , , 8)
t = var.Address

For Each cell In Range(t)
    x = IIf(Len(myrng) = 0, "", ",")
    If myrng= Empty And cell >= 751 And cell <= 1600 Then
        
        myrng = cell.Address(0, 0)
    ElseIf cell >= 751 And cell <= 1600 Then
        
        myrng = myrng & x & cell.Address(0, 0)
    End If
    
Next cell

Run-Time error “1004” Method “Range” of Object' _Global Failed."

1 Answers