Solved: Find Method not working properly

[cavemonkey]

I have a problem with this code.

Mind taking a look and tell me where's wrong? Thanks.


Sub find_status1()
Dim lrow, r, aa, tag_no As Long

Sheets("Report").Select
lrow = ActiveSheet.Range("A65536").End(xlUp).Row
For r = 8 To lrow
aa = Cells(r, 1)
If (aa = "U3100" Or aa = "U3200" Or aa = "U3300" Or aa = "U4200" Or aa = "U4400" Or aa = "U5100" Or aa = "U5400" Or aa = "U5500" Or aa = "U5600" Or aa = "U5700" Or aa = "U5900" Or aa = "U6300" Or aa = "U6400" Or aa = "U6500" Or aa = "U7100") Then

Cells(r, 2) = tag_no

Sheets("Sheet4").Select
Cells.Find(What:="tag_no", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Select

Selection.End(xlDown).Column
End If
Next r
MsgBox lrow
End Sub

[JimmyTheHand]

Hi Cavemonkey :hi:

There are several "wrong" parts in the code. I list here those code lines I don't like:
Dim lrow, r, aa, tag_no As Long

lrow = ActiveSheet.Range("A65536").End(xlUp).Row

Cells(r, 2) = tag_no

Cells.Find(What:="tag_no", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Select

Selection.End(xlDown).Column
I think I could give you some advice as to how to proceed, however, it would be easier if you first explained, what your goal is.

Jimmy

[Bob Phillips]

BTW



Dim lrow, r, aa, tag_no As Long


does not dimension all of those variables as Longs, just tag_no, the rest are variants. You have to declare each explicitly.

[cavemonkey]

Ok here goes.

The attached excel file is part of what my code is about.

So basically, in 'Report' sheet there are a few tag no. that are listed there. I would like to find these tag no. in 'Sheet4' tag using the Find Method.

Then subsequently when I found the tag no. in 'Sheet4', I would be able to find the no. of hours activated. Copy the no. of hours activated and pasted in 'Report' sheet under 'Duration Activated' section.

For the latter part, I shld be able to fig how to do it. Its just that my Find Method is not working, that's all.

Hope I'm able to clear some doubts. Thanks.

[Bob Phillips]

Nothing attached.

[JimmyTheHand]

Well, then, I tell what I didn't like in your code.
1.Dim lrow, r, aa, tag_no As Long This has already been addressed by XLD.
2. lrow = ActiveSheet.Range("A65536").End(xlUp).Row THis works, but a more versatile version would be lrow = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
3. Cells(r, 2) = tag_no tag_no had been dimensioned as a local Long variable, but has no value assigned. At this point of the code tag_no is always 0. Why do you use it so?
4. Cells.Find(What:="tag_no", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Select The above method, with the What parameter as it is, looks for the string "tag_no", instead of tag numbers or unit codes or such. Also, it goes to error, if string "tag_no" is not found. What I would use is something like this:
Dim Hit As Range
Set Hit = Cells.Find(What:=Cstr(tag_no), After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not Hit Is Nothing Then
'proceed with found tag no
Else
MsgBox "Tag no is not found"
End If

5.Selection.End(xlDown).Column This line makes no sense in it's present form. It could be part of an equation, like Dim abc as Long
abc = Selection.End(xlDown).Column but End(xlDown) method doesn't change Column index, so it would be the same as
abc = Selection.Column and still I'm sot sure it is really what you want. But it's only a guess.

Jimmy