CODEWITHSUNDEEP
vbaexcel
TooMacho BA
TooMacho BA

Reputation: 63

VBA Runtime error 1004 for selecting data in column

Hi i want to select data that are below of the found cell MEASURED VALUE but i'm getting runtime error and don't know why. 

Sub Macro1()

Dim ws As Worksheet
Dim i As Range
Dim j As Range


Set ws = Worksheets("bank")

Set i = ws.Range("A1").CurrentRegion.Find(What:="MEASURED VALUE", LookAt:=xlWhole).Offset(1, 0)


Set j = Range(i, Range(i).End(xlDown))


End Sub

Error occurs in Set j = Range(i, Range(i).End(xlDown))

p.s i know it is problably stupid question but im begginer. Thank you.

Upvotes: 1

Views: 723

Answers (2)

Vityata
Vityata

Reputation: 43585

In the case that i is found, if you try this, it works:

Set j = Range(i, Range(i.Address).End(xlDown))

The reason it works is because Range() expects a string within and i.Address gives a string. Another way to make it work is:

Set j = Range(i, i.End(xlDown))

Here you do not use Range(), but you use the i, which is already defined as range, thus it has the .End(Direction As xlDirection) property. Whenever you work with .Find, it is a good practice to check whether the value is Not Nothing (VBA does not have a better way of checking whether it is something):

If Not i Is Nothing Then

In general, it is a best practice to always refer to the parent worksheet, when you are referring to Range(), Cells(), Rows(), Columns(), because otherwise the range is assigned to the ActiveSheet and it may be painful.

However, in this specific case, j would be referred to the correct worksheet, because the i is assigned to it. Try this small code on an empty Excel file to see - it activates the first worksheet and yet the second is the parent of Range j:

Public Sub TestMe()

    Dim ws As Worksheet
    Worksheets(1).Activate
    Set ws = Worksheets(2)

    Dim i As Range
    Dim j As Range

    Set i = ws.Range("A1").CurrentRegion
    Set j = Range(i, i.End(xlDown))
    Debug.Print j.Parent.Name

End Sub

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33682

When using the Find function, always prepare yourself for a scenario that Find failed to find the match, you can do that by having the following line right after your Find:

If Not i Is Nothing Then

Modified Code 

Option Explicit

Sub Macro1()

Dim ws As Worksheet
Dim i As Range
Dim j As Range

Set ws = Worksheets("bank")

With ws
    Set i = .Range("A1").CurrentRegion.Find(What:="MEASURED VALUE", LookIn:=xlValues, LookAt:=xlWhole)

    ' make sure first find was successful
    If Not i Is Nothing Then
        Set j = .Range(i.Offset(1, 0), i.Offset(1, 0).End(xlDown))
    Else
        MsgBox "Error!, couldn't find 'MEASURED VALUE'", vbCritical
    End If
End With

End Sub

Upvotes: 1

Related Questions