jjj2k
05-04-2011, 01:30 AM
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?
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?