I have a complex Find scenario. I have 2 worksheets.

In WKSHT 1 I have a list of alphanumeric values in Column C that I would
like to find in Column E of WKSHT 2. When found, I would like the value in
the same row on WKSHT 1 in Column J to be pasted into a the cell in WKSHT 2
in Column A, 4 rows down from where the alphanumeric value was found. I also
would need it to paste a second value from WKSHT 1 in Column H into WKSHT 2
Column J.

Does that make sense?

I have example worksheets that I can email.

The number of rows in WKSHT 1 is variable, usually ranging from 3-100 and
would like for it to automaically search for the entire list.

Thanks for any help that you can offer.

with Sheets("sheet1")
Lastrow = .Range("C" & Rows.count).end(xlup).row
For RowCount = 3 to Lastrow
SearchData = .Range("C" & RowCount)
DataH = .Range("H" & RowCount)
DataJ = .Range("J" & RowCount)

with Sheets("sheet2")
set c= .columns("E").find(what:=SearchData, _
lookin:=xlvalues,lookat:=xlwhole)
if c is nothing then
msgbox("Could Not find : " & SearchDate)
else
.Range("A" & c.row) = DataJ
.Range("J" & c.row) = DataH
end if
end with
next RowCount


end with


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=170784

Microsoft Office Help

a couple of tweeks to Joel's code:

msgbox("Could Not find : " & SearchData) 'changed SearchDate to SearchData
..Range("A" & c.row + 4) = DataJ 'to paste data 4 rows down

:)

"joel" wrote:


with Sheets("sheet1")
Lastrow = .Range("C" & Rows.count).end(xlup).row
For RowCount = 3 to Lastrow
SearchData = .Range("C" & RowCount)
DataH = .Range("H" & RowCount)
DataJ = .Range("J" & RowCount)

with Sheets("sheet2")
set c= .columns("E").find(what:=SearchData, _
lookin:=xlvalues,lookat:=xlwhole)
if c is nothing then
msgbox("Could Not find : " & SearchDate)
else
.Range("A" & c.row) = DataJ
.Range("J" & c.row) = DataH
end if
end with
next RowCount


end with


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=170784

Microsoft Office Help

.

I have to admit that I am pretty programming illiterate. Where do I put this
code?

"Trevor Williams" wrote:

a couple of tweeks to Joel's code:

msgbox("Could Not find : " & SearchData) 'changed SearchDate to SearchData
.Range("A" & c.row + 4) = DataJ 'to paste data 4 rows down

:)

"joel" wrote:


with Sheets("sheet1")
Lastrow = .Range("C" & Rows.count).end(xlup).row
For RowCount = 3 to Lastrow
SearchData = .Range("C" & RowCount)
DataH = .Range("H" & RowCount)
DataJ = .Range("J" & RowCount)

with Sheets("sheet2")
set c= .columns("E").find(what:=SearchData, _
lookin:=xlvalues,lookat:=xlwhole)
if c is nothing then
msgbox("Could Not find : " & SearchDate)
else
.Range("A" & c.row) = DataJ
.Range("J" & c.row) = DataH
end if
end with
next RowCount


end with


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=170784

Microsoft Office Help

.

This is the updated macro.

Subv MatchValues()

with Sheets("sheet1")
Original Source: The Code Cage Forums Lastrow = .Range("C" &
Rows.count).end(xlup).row
For RowCount = 3 to Lastrow
SearchData = .Range("C" & RowCount)
DataH = .Range("H" & RowCount)
DataJ = .Range("J" & RowCount)

with Sheets("sheet2")
set c= .columns("E").find(what:=SearchData, _
lookin:=xlvalues,lookat:=xlwhole)
if c is nothing then
msgbox("Could Not find : " & SearchData)
else
.Range("A" & (c.row + 4)) = DataJ
.Range("J" & c.row) = DataH
end if
end with
next RowCount

end with

End sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=170784

Microsoft Office Help