find question on vba
 

Hi All

I define two find
1. Find Key
2. Find Item

In find key, the cell formula is =G2&"|"&V3&"|"&J2. I check the data,
the find can not able to find a key value.
The key value is on target sheet. But the find return nothing.

What is the problem on data case return nothing ?


With GP_WK.Sheets(MES.SHT_Name).Range(MES.KEY_RANGE)
Set Rng_KEY = .Find(What:=GPD_KEY_VAL, _
After:=.Cells(1), _
LookIn:=xlValue, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

With GP_WK.Sheets(MES.SHT_Name).Range(MES.ITEM_RANGE)
Set Rng_ITEM = .Find(What:=GPD_ITEM_VAL, _
After:=.Cells(1), _
LookIn:=xlValue, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If Not Rng_ITEM Is Nothing And Rng_KEY Is Nothing Then

......

find question on vba
 

I am assuming that GPD_KEY_VAL is a defined name of a cell. If so then try
the following.

Set Rng_KEY = .Find(What:=Range("GPD_KEY_VAL").Value, _
After:=.Cells(1), _
LookIn:=xlValue, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Regards,

OssieMac


Regards,

Regards,

OssieMac


"moonhk" wrote:

Hi All

I define two find
1. Find Key
2. Find Item

In find key, the cell formula is =G2&"|"&V3&"|"&J2. I check the data,
the find can not able to find a key value.
The key value is on target sheet. But the find return nothing.

What is the problem on data case return nothing ?


With GP_WK.Sheets(MES.SHT_Name).Range(MES.KEY_RANGE)
Set Rng_KEY = .Find(What:=GPD_KEY_VAL, _
After:=.Cells(1), _
LookIn:=xlValue, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

With GP_WK.Sheets(MES.SHT_Name).Range(MES.ITEM_RANGE)
Set Rng_ITEM = .Find(What:=GPD_ITEM_VAL, _
After:=.Cells(1), _
LookIn:=xlValue, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If Not Rng_ITEM Is Nothing And Rng_KEY Is Nothing Then

......
.