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
__________________________________________________________________________________________
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
__________________________________________________________________________________________
