Search for value and map data

[jjj2k]

This is how my table looks like, this is an extract runs for 9000 rows:


ID ChargeCat_ID Call_Type Filter1 Filter2 Data Plan Mbps Up Mbps Down
458 31 Business DSL Access 1024k Metro 1 1 NULL 1 1
867 31 1024K SHDSL Recurring in advance 1 1 NULL 1 1
868 31 10Mbps ETHERNET Recurring in advance 1 1 NULL 10 10
871 31 2048K SHDSL Recurring in advance (pro rata 0.064516 months) 1 1 NULL 2 2
955 31 100Mbps ETHERNET Recurring in advance 1 1 NULL 100 100
1203 31 6Mbps ETHERNET Recurring in advance 1 1 NULL 6 6
1204 31 8Mbps ETHERNET Recurring in advance 1 1 NULL 8 8
3890 31 10Mbps DDFE Recurring in arrears 1 1 NULL 10 10
4076 31 1Mbps ETHERNET Recurring in arrears 1 1 NULL 1 1
4684 31 10Mbps ETHERNET Recurring in advance (pro rata 4.516129 months) 1 1 NULL 10 10
5736 31 10 mbps 1 1 NULL 10 10
5737 31 20 mbps 1 1 NULL 20 20
5866 31 600Mbps DDG Recurring Fee Advance 1 1 NULL 600 600
5921 31 11Mbps ETHERNET Recurring in advance 1 1 NULL 10 10
5927 31 10Mbps ETHERNET Recurring in advance (pro rata 2.387097 months) 1 1 NULL 10 10
5935 31 352Mbps ETHERNET Recurring in advance 1 1 NULL 300 300
6025 31 50 mbps 1 1 NULL 50 50
6095 31 0.512mbps 1 1 NULL 0.5 0.5
6096 31 4 mbps 1 1 NULL 4 4
6100 31 2 mbps 1 1 NULL 2 2
6102 31 2Mbps DDFE Recurring in advance 1 1 NULL 2 2
6103 31 50Mbps ETHERNET Recurring in advance 1 1 NULL 50 50
6104 31 200 mbps 1 1 NULL 200 200
6105 31 1 mbps 1 1 NULL 1 1


I am trying to create a function that finds for specific values within a cell and gives me an output that I have specified in the vba function. Here is the code:



Function updatetable(takein As Range) As String

'Data Plan Mapping
' DSL -> DSL
' ETHERNET -> ETHERNET
' DDF, DDG, DDX, DDFE -> DSL
' Rest UNKNOWN

Dim x As String
Dim y1 As Integer
Dim y2 As Integer
Dim y1n As String
Dim y2n As String
Dim y3n As String, y4n As String, y5n As String
Dim data_plan As String


' Identify Row Number
x = Mid(takein.Address, 4, 100)

'-------------------
'Find Data Plan type
'-------------------
y1n = "DSL"
y2n = "ETHERNET"
y3n = "DDF"
y4n = "DDG"
y5n = "DDX"
y6n = "DDFE"
y7n = "UNKNOWN"

'Find Data_Plan
y1 = Application.WorksheetFunction.Find(y1n, Range("c" & x).Value, 1)
If Application.WorksheetFunction.IsNumber(y1) = True Then
data_plan = y1n
ElseIf Application.WorksheetFunction.IsNumber(y1) = False Then
y2 = Application.WorksheetFunction.Search(y2n, Range("c" & x).Value, 1)
If Application.WorksheetFunction.IsNumber(y2) = True Then
data_plan = y2n
End If
End If


updatetable = data_plan


End Function


But It only works the first time, I can't seem to do it the second time?

[Bob Phillips]

It just returns #VALUE for me. What is supposed to happen?

[jjj2k]

If Call_Type column contains DSL then return DSL,
Eg. Business DSL Access 1024k Metro--> DSL

If, 11Mbps ETHERNET Recurring in advance then return ETHERNET
if, 20 mbps --> UNKNOWN

and so on. (basically, look at the data mapping above)

[Bob Phillips]

But what am I supposed to do? Is it a UDF, called from another procedure? I just don't know how to achieve the results you describe.

[jjj2k]

Yes it is a UDF.

I will put the formula =updatetable(a2) (say on Column titled [Data Plan New] on column I2) on my worksheet where the [Data_Plan] table is currently NULL.

[jjj2k]

OKAY Solved it myself but if there is an elegant solution out there please let me know as this looks very hard to read.




Function updatetable(takein As Range) As String

'Data Plan Mapping
' DSL -> DSL
' ETHERNET -> ETHERNET
' DDF, DDG, DDX, DDFE, DDE -> DSL
' Rest UNKNOWN

Dim x As String
Dim y1 As Integer
Dim y2 As Integer
Dim y3 As Integer
Dim y4 As Integer
Dim y5 As Integer
Dim y1n As String
Dim y2n As String
Dim y3n As String, y4n As String, y5n As String
Dim data_plan As String


' Identify Row Number
x = Mid(takein.Address, 4, 100)

'-------------------
'Find Data Plan type
'-------------------
y1n = "DSL"
y2n = "ETHERNET"
y3n = "DDF"
y4n = "DDG"
y5n = "DDX"
y6n = "DDFE"
y7n = "DDE"
y8n = "DDX"
y9n = "UNKNOWN"

'Find Data_Plan
'BEGIN y1
On Error Resume Next
y1 = Application.WorksheetFunction.Find(y1n, Range("c" & x).Value, 1)
On Error GoTo 0
If y1 = 0 Then
'BEGIN y2
On Error Resume Next
y2 = Application.WorksheetFunction.Find(y2n, Range("c" & x).Value, 1)
On Error GoTo 0
If y2 = 0 Then
'BEGIN y3
On Error Resume Next
y3 = Application.WorksheetFunction.Find(y3n, Range("c" & x).Value, 1)
On Error GoTo 0
If y3 = 0 Then
'BEGIn y4
On Error Resume Next
y4 = Application.WorksheetFunction.Find(y4n, Range("c" & x).Value, 1)
On Error GoTo 0
If y4 = 0 Then
'data_plan = "n/a"
'BEGIN y5
On Error Resume Next
y5 = Application.WorksheetFunction.Find(y4n, Range("c" & x).Value, 1)
On Error GoTo 0
If y5 = 0 Then
'do something
'begin y6
On Error Resume Next
y6 = Application.WorksheetFunction.Find(y6n, Range("c" & x).Value, 1)
On Error GoTo 0
If y6 = 0 Then
'do something
'begin y7
On Error Resume Next
y7 = Application.WorksheetFunction.Find(y7n, Range("c" & x).Value, 1)
On Error GoTo 0
If y7 = 0 Then
'begin y8
On Error Resume Next
y8 = Application.WorksheetFunction.Find(y8n, Range("c" & x).Value, 1)
On Error GoTo 0
If y8 = 0 Then
data_plan = y9n
Else
data_plan = y1n
End If
'end y8
Else
data_plan = y1n
End If
'end y7
Else
data_plan = y1n
End If
'end y6
Else
'return its value
data_plan = y1n
End If
'END y5
Else
data_plan = y1n
End If
'END y4
Else
data_plan = y1n
End If
'END y3
Else
data_plan = y2n
End If
'END y2
Else
data_plan = y1n
End If
'END y1

updatetable = data_plan


End Function