Solved: 2 sheet search

[Gil]

Hello
Trying to adapt and use a previously supplied solution for this search I have got to the following stage and am stumped.The search should look for data in 2 sheets and supply a result before moving to the next item.
The Dim c is giving me the first problem

[VBA]
Option Explicit
Private Sub GetMapping_Click()

Sheet1.Select
Columns("B:M").ClearContents

Columns("A:A").Select
Selection.Replace What:="1/", Replacement:="1/ ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Dim lngLastRow As Long
lngLastRow = Range("A" & Rows.Count).End(xlUp).Row
Cells(lngLastRow - 0, 1).Select

Dim Sh
Dim Fnd As Range
Dim c As Range
Dim FirstAddress As String
Dim SecAdd As String
Dim x As Long
Dim arr, a


arr = (Array("Sheet2", "Sheet3"))
Set c = Cells.Find((Split(c)(1)), LookAt:=xlWhole)

If Not Fnd Is Nothing Then FirstAddress = c.Address

Do

For Each a In arr
Set Sh = Sheets(a)

Set Fnd = Sh.Cells(3, Fnd.Column - 0) & "-" & Fnd.Offset(, -1) & "-" & Fnd.Offset(, -2)
ActiveCell.Offset(, 2).Font.Bold = True
ActiveCell.Offset(, 2).Font.Color = -16776961
If Not Fnd Is Nothing Then
SecAdd = Fnd.Address
'Inner loop ***************
Do
Set tgt = Cells(c.Row, Columns.Count).End(xlToLeft).Offset(, 1)
If tgt.Column < 6 Then Set tgt = Cells(c.Row, 5)
With tgt
.Offset(1) = Sh.Name
.Offset(, 2).Font.Bold = True
.Offset(, 2).Font.Color = -16776961
.Value = Sh.Cells(3, Fnd.Column - 0) & "-" & Fnd.Offset(, -1) & "-" & Fnd.Offset(, -2)
End With
Set Fnd = Sh.Cells.FindNext(Fnd)
Loop While Not Fnd Is Nothing And Fnd.Address <> SecAdd
'********************
Else
Set tgt = Cells(c.Row, Columns.Count).End(xlToLeft).Offset(, 1)
If tgt.Column < 6 Then Set tgt = Cells(c.Row, 8)

With tgt
.Value = "Not found"
.Font.Bold = True
.Font.Color = -16776961
End With
End If
Set c = Cells(c.Row, Columns.Count).End(xlToLeft).Offset(, 1)
Next
ActiveCell.Offset(-8, 0).Select
Loop While Not c Is Nothing And c.Address <> FirstAddress
Columns("A:A").Select
Selection.Replace What:="1/ ", Replacement:="1/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub


Private Sub Reset()
Columns("A:A").Select
Selection.Replace What:="1/ ", Replacement:="1/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
[/VBA]
Points noted and hopefully corrected. Now compile errror Variable not defined (tgt)

[lucas]

You have some other problems that you would already know about if you were using option explicit at the top of your module.

put it there and run the macro and the first thing you are told is that ingLastRow is not defined. Can you tell why from looking at your code?
[VBA]
Dim lngLastRow As Long
ingLastRow = Range("A" & Rows.Count).End(xlUp).Row
[/VBA]

[lucas]

You are working with multiple sheets yet you don't qualify statements like this that refer to a sheet:

[VBA]Columns("B:M").ClearContents[/VBA]

Which sheet?

Probably not a huge issue as long as the sheet you want is current. What if someone else runs it and that sheet is not current?

[Gil]

Many thanks
Points noted and code above ammended (correctly I hope).
The
Columns("B:M").ClearContentsis just to clear results if I ever get any.
Gil

[GTO]

Greetings Gil,

Trying to adapt and use a previously supplied solution for this search...

Maybe it would help if you provided a link to the thread you adapted the code from.

I think that most of us are always happy to see someone making efforts (vs. the "could you write all the code for me?"), so please understand that this is meant in a positive manner. I think you may have altered the code in at least a few areas, with less than desired results.

For instance, unless I am really missing something,
[vba]Set c = Cells.Find((Split(c)(1)), LookAt:=xlWhole)[/vba]
would not work, as 'c' has not been Set before this line, and thus, does not exist for the Split.

Mark

[Gil]

Hello
The original that I was trying to adapt came from mdmackillop and worked perfectly for that situation.
http://www.vbaexpress.com/forum/showthread.php?t=31504
Many thanks
Gil