search for text in range returns "1004" in msgbox

[tomleitch]

Hi all,

I'm trying to put some code together that looks for a text match in the top row of titles of a sheet...

What I want to do is find the column that contains the text "Description" and copy the whole column into a specified column on another sheet.


I have some code that looks at the last row in the A column to find the bottom of the range but what I'm struggling with is to find the other part - I set a msgbox to tell me what the value is but it's just giving me 1004


This is an excerpt from the code: (I've got the lcol bit all sorted to give me the end column of the data)

Dim rng As Range
Dim Description As Variant
Dim rowNum As Long

    Set rng = Sheets("UPDATE TOOL").Range("A1" & "A" & lcol)
    
    Description = "Description"
    If Not IsError(Application.Match(aNumber, rng, 0)) Then
        rowNum = Application.Match(aNumber, rng, 0)
        MsgBox rowNum
    Else
        If MsgBox("Description not found", vbOKCancel) = vbCancel Then GoTo 100
            
        
    End If

I'm sure it's something fairly simple! Any help appreciated.

Thanks
Tom

 

[Fluff]

Are you trying to find the word in row1?
If so use

Set rng = Sheets("UPDATE TOOL").Range("A1", Sheets("UPDATE TOOL").Cells(1, lcol))

Also what is "aNumber"? Should that be Description?

 

[tomleitch]

Yes, and yes!

Once again done the business!.... many thanks

 

[Fluff]

Glad to help & thanks for the feedback