Microsoft Excel - VBA Arrays !! Please Help - Asked By Rajender Prasad on 27-Aug-13 12:30 PM

Dear All,

I have the below data with me

PCP	Start Date	End Date
12347	1/1/2013	--/--/----
12346	8/1/2012	12/31/2012
12347	7/1/2011	7/30/2012
12348	1/1/2011	6/30/2011

I want to check below validations:

1. 12346 is my PCP, my Request Date is 8/1/2012, I have to search this in below data using array.
 If this is matched any where, then I want to store the above values of this. Ex: I need the 12345 1/1/2013 --/--/----

Then I will Pass my values & stored values to a function one by one.

2. 12347 is my pcp, my Request Date is 7/1/2011, in this case My PCP value is matching for First and third record.
 but I want the latest date which is 1/1/2013, then I will pass this value to a function

I would request you to help me in writing the code for this in VBA

Regards,
Raj

Hi Raj,
Just to be clear,.
You would like to enter a PCP and a Start Date, find if a match exists and return all three values from the matching row?
Is it possible that there is more than one matching row (I know that the PCP can occur more than once)?
Regards
Harry

Hi Prasad,

Try this....
---------------------------------------------------------------------
Sub findPCP()

reqPCp = Range("I2")
startDt = 0
For Each pcp In Range("A2:A" & Range("A1000000").End(xlUp).Row)
If pcp = reqPCp Then
    If startDt < pcp.Offset(0, 1) Then
    startDt = pcp.Offset(0, 1)
    End If
End If
Next pcp
MsgBox "Your match Start Date value = " & startDt

End Sub
-----------------------------------------------------------------------------

Also see more detail in this sample---> FindPCP-ForPrasad.zip

Hope this help.

Pichart Y.