excel vba range.find doesn't return a range

Posted on 2012-09-07
Last Modified: 2012-09-07
Dear experts,

I have this little piece of code and i can't get the find function to return the address or range of the cell.

i don't understand what i have to do. here's my code.

sub testFind()

Dim GSRNsp As String
Dim PlantnameSP As String
Dim Skownership As Integer
Dim MeasuringRfact As Integer
Dim rangeFind As Range
With Workbooks(WorkbookName).Sheets("DividedPD's")

    x = .Cells(Rows.Count, "J").End(xlUp).Row

For i = 2 To x

    GSRNsp = .Range("J" & i).Value 'this is a unique id nr like(703213102 stored as text)
    PlantnameSP = .Range("K" & i).Value
    Skownership = .Range("L" & i).Value
    MeasuringRfact = .Range("M" & i).Value

Set rangeFind = Workbooks(targetSheetName).Sheets(tabName).Range("3:3").Find(What:=GSRNsp, After:=Range("A3"), SearchOrder:=xlByColumns)

'rangefind will now return the unique id like (703213102) while it should return the range 'or something?

next i

end with 

Open in new window

Thank you!

Question by:MarkVrenken
    LVL 1

    Author Comment

    ps this is just a piece of the code. The other workbook is first opened
    LVL 1

    Accepted Solution

    added this and it works:)

    If Not rangeFind Is Nothing Then

    ColumnNumber = rangeFind.Column

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now