difficult parse and search question

[nl05369]

hi experts,
I have a parse / lookup / search problem that I have no idea how to start on.

What I want to do is check the value of the cell I'm standing on.
If it contains value "DVDCNLxx7*" (the xx could be EA, ET, DA ot DT.. possible others, and the * can be and letter or number), THEN
check the next cell right. Parse the value of that cell for numbers in one of 2 formats: either 07/08, or 7/08 or 07/8 or 7/8
Then search through a table (or a set list in a VBA script) to return the value in the 4th field/cell of the searched data.
so for example:
Cell my cursor is on contains DVDCNLEA78.
Cell 1 to the right contains 7/28
Routine should search the values underneath; find the DVDCNLEA78; seperate the "7" from the "28".
Find the 7 (listed in the table under as "07"
Find the value 25-48 (28 falls between 25 and 48) and return CG51 to a variable that I can put into a cell 2 left from the cell the cursor is on.
DVDCNLEA72 02 01-24 BJ70
DVDCNLEA72 02 25-48 BI66
DVDCNLEA72 03 01-24 AW70
DVDCNLEA72 03 25-48 AZ70
DVDCNLEA72 04 01-24 BA70
DVDCNLEA72 04 25-48 BC70
DVDCNLEA72 05 01-24 AN66
DVDCNLEA72 05 25-48 AQ70
DVDCNLEA72 06 01-24 AZ66
DVDCNLEA72 06 25-48 AK70
DVDCNLEA72 07 01-24 BY70
DVDCNLEA72 07 25-48 BY66
DVDCNLEA72 08 01-24 BB51
DVDCNLEA72 08 25-48 AZ56
DVDCNLEA73 02 01-24 BY66
DVDCNLEA73 02 25-48 BY70
DVDCNLEA73 03 01-24 AZ70
DVDCNLEA73 03 25-48 AW70
DVDCNLEA73 04 01-24 BC70
DVDCNLEA73 04 25-48 BA70
DVDCNLEA73 05 01-24 AK70
DVDCNLEA73 05 25-48 AZ66
DVDCNLEA73 06 01-24 AQ70
DVDCNLEA73 06 25-48 AN66
DVDCNLEA73 07 01-24 BI66
DVDCNLEA73 07 25-48 BJ70
DVDCNLEA73 08 01-24 AZ56
DVDCNLEA73 08 25-48 BB51
DVDCNLEA77 02 01-24 BW70
DVDCNLEA77 02 25-48 BX66
DVDCNLEA77 03 01-24 CC66
DVDCNLEA77 03 25-48 CD70
DVDCNLEA77 04 01-24 CB66
DVDCNLEA77 04 25-48 CE70
DVDCNLEA77 05 01-24 CN56
DVDCNLEA77 05 25-48 CL51
DVDCNLEA77 06 01-24 CL51
DVDCNLEA77 06 25-48 CN56
DVDCNLEA77 07 01-24 CG56
DVDCNLEA77 07 25-48 CF51
DVDCNLEA77 08 01-24 BX51
DVDCNLEA77 08 25-48 BY56
DVDCNLEA78 02 01-24 BX66
DVDCNLEA78 02 25-48 BW70
DVDCNLEA78 03 01-24 CD70
DVDCNLEA78 03 25-48 CC66
DVDCNLEA78 04 01-24 CE70
DVDCNLEA78 04 25-48 CB66
DVDCNLEA78 05 01-24 CL51
DVDCNLEA78 05 25-48 CN56
DVDCNLEA78 06 01-24 CN56
DVDCNLEA78 06 25-48 CL51
DVDCNLEA78 07 01-24 CF51
DVDCNLEA78 07 25-48 CG56
DVDCNLEA78 08 01-24 BY56
DVDCNLEA78 08 25-48 BX51
DVDCNLEA7A 02 01-24 AV56
DVDCNLEA7A 02 25-48 AX51
DVDCNLEA7B 02 01-24 AX51
DVDCNLEA7B 02 25-48 AV56

Any ideas?
nb: I already have a routine that can perform normal searches,a dn I want to incorperate a solution into this current VBA (under the header VIOLET (halfway through the VBA))
__________________________________________________________________________________________
Sub FETCH_SRV_DETAILS()
' This searches a sheet in PERSONAL.XLSB for a value of the cell you are standing on when invoking the macro.
On Error GoTo MyErrorHandler:
Dim E_id As String
E_id = ActiveCell.Value

GoTo Find_Server:

MsgBox E_id
If E_id = "DVDCNLEA78" Then GoTo VIOLET:
GoTo Find_Server:
VIOLET:
'enter DVDCNLxx7* routine here

Find_Server:
' Find Server and Extract the needed info
Grid = Application.WorksheetFunction.VLookup(E_id, Workbooks("Personal.xlsb").Sheets("All_D").Range("A1:K19345"), 7, False)
U_Height = Application.WorksheetFunction.VLookup(E_id, Workbooks("Personal.xlsb").Sheets("All_D").Range("A1:K19345"), 6, False)
Serial = Application.WorksheetFunction.VLookup(E_id, Workbooks("Personal.xlsb").Sheets("All_D").Range("A1:K19345"), 4, False)
Make = Application.WorksheetFunction.VLookup(E_id, Workbooks("Personal.xlsb").Sheets("All_D").Range("A1:K19345"), 2, False)
Model = Application.WorksheetFunction.VLookup(E_id, Workbooks("Personal.xlsb").Sheets("All_D").Range("A1:K19345"), 3, False)

' Check is current column = 5 (server name). If so, fill server details
ncol = ActiveCell.Column
If ncol = 5 Then GoTo Fill_Server:

' Otherwise fill switch details
Fill_Switch:
ActiveCell(1, -1) = Grid
ActiveCell.Offset(1, 0).Select
GoTo MyErrorHandler:

Fill_Server:
ActiveCell(1, 0) = Serial
ActiveCell(1, -1) = Make & " " & Model
ActiveCell(1, -2) = U_Height
ActiveCell(1, -3) = "BA0 - " & Grid
Worksheets("Cable Plan").Columns("C:C").AutoFit
ActiveCell.Offset(1, 0).Select
GoTo MyErrorHandler:


Exit Sub
MyErrorHandler:
If Err.Number = 1004 Then
ActiveCell(1, -1) = "Device not in current dump"
ActiveCell.Offset(1, 0).Select
ElseIf Err.Number = 13 Then
MsgBox "You have entered an invalid value."
End If
End Sub

__________________________________________________________________________________________

 

[oldbrewer]

														01	01-24
								lookuptable>>>>>>>>>>>>>>>>	25	25-48
														48	25-48
DVDCNLEA77	08	25-48	BY56	DVDCNLEA77825-48
DVDCNLEA78	02	01-24	BX66	DVDCNLEA78201-24
DVDCNLEA78	02	25-48	BW70	DVDCNLEA78225-48
DVDCNLEA78	03	01-24	CD70	DVDCNLEA78301-24							@@@@@	########	::::::::::::
DVDCNLEA78	03	25-48	CC66	DVDCNLEA78325-48			DVDCNLEA78	7/28	2	1	07	28	25-48
DVDCNLEA78	04	01-24	CE70	DVDCNLEA78401-24			07
DVDCNLEA78	04	25-48	CB66	DVDCNLEA78425-48			25-48
DVDCNLEA78	05	01-24	CL51	DVDCNLEA78501-24
DVDCNLEA78	05	01-24	CN56	DVDCNLEA78501-24
DVDCNLEA78	06	01-24	CN56	DVDCNLEA78601-24			DVDCNLEA78725-48
DVDCNLEA78	06	25-48	CL51	DVDCNLEA78625-48		answer>>>	CG56
DVDCNLEA78	07	01-24	CF51	DVDCNLEA78701-24
DVDCNLEA78	07	25-48	CG56	DVDCNLEA78725-48
DVDCNLEA78	08	01-24	BY56	DVDCNLEA78801-24
DVDCNLEA78	08	25-48	BX51	DVDCNLEA78825-48
		by concatenating cols a,b,c
		stripping out the 7 from 7/28
		and matching the 28 to 25/48
		CG56 is found
		by using offset match to find
		DVDCNLEA78725-48
		in col E
		and stepping one to the left
		answer is found

<tbody>
</tbody>

 

[nl05369]

Thanks OldBrewer,
using the concatenation (and applying a few reformatting tricks) did the trick.
Great help.
Solved!