Public Function UT(Segment, Bandwidth, P_Lline, Mult, USA)
Dim r1 As Range, r2 As Range, r3 As Range, myMultiAreaRange As Range
Worksheets("NP").Activate
Selection.AutoFilter
Set r1 = Range("a1:a50000")
Set r2 = Range("j1:j50000")
Set r3 = Range("s1:s50000")
Set myMultiAreaRange = Union(r1, r2, r3)
ReDim P_Lline(9)
PointA = Mid(Segment, 9, 7)
pointZ = Right(Segment, 7)
With myMultiAreaRange
Set FC = .Find(what:=PointA, LookIn:=xlValues)
If FC Is Nothing Then
MsgBox "alert", vbCritical, "CH Lookup"
Exit Function
End If
End With
FC.Select
adrsX = FC.Row
adrsY = FC.Column
USPoPA_NpaNxx = Cells(adrsX, adrsY)
USPoPA_street = Cells(adrsX, adrsY + 4)
USPoPA_city = Cells(adrsX, adrsY + 5)
With myMultiAreaRange
Set FC = .Find(what:=pointZ, LookIn:=xlValues)
If FC Is Nothing Then
MsgBox "alert", vbCritical, "CH Lookup"
Exit Function
End If
End With
FC.Select
adrsX = FC.Row
adrsY = FC.Column
USPoPZ_NpaNxx = Cells(adrsX, adrsY)
USPoPZ_street = Cells(adrsX, adrsY + 4)
P_Lline(0) = "US segment between " & USPoPA_street & " and " & USPoPZ_street
P_Lline(1) = Segment
Dim test As String
P_Lline(9) = USPoPA_city
test = "Hello"
USA = test & P_Lline(9)
' i cant ptovide all the arrays here, its a very long code
End Function
--------------------------------------------------------
Public Sub Vert(Segment, Bandwidth, Pnl_Col)
Call UT(Segment, Bandwidth, P_Lline, 1)
Worksheets("expo").Select
ActiveSheet.Shapes("pct1").Select
Selection.Copy
ActiveSheet.Cells(1, 7 + Pnl_Col).Select
ActiveSheet.Paste
With ActiveSheet
' this is where all the arrays values are exported on the excel cells and P_Lline(9) comes out on row 175 column 7.
MyArray = Array(2, 7, 9, 13, 35, 42, 47, 67, 74, 175)
For n = 0 To 9
.Cells(MyArray

, 7 + Pnl_Col).Value = P_Lline

Next n
End Select
End With
End Sub
-----------------------------------------------------------
'in another sub, i assign these values, the code is to big to fit here. CntryA ad CityA etc are all variables, they change everytime the model is run
CntryA = ActiveSheet.Cells(2, 7).Value
PopA = ActiveSheet.Cells(2, 4).Value
PopZ = ActiveSheet.Cells(2, 8).Value
CityZ = ActiveSheet.Cells(2, 9).Value
If CntryA = "US (US)" Then
Call UT(Segment, BandWidth, P_Lline, USA, Mult)
CityA = USA
Else
CityA = ActiveSheet.Cells(2, 5).Value
End If
----------------------------------------------------------
Public Sub Design(PopA, PopZ, CityA, CityZ)
Application.CutCopyMode = False
If Not WorksheetExists("Expo") Then
Sheets("Template").Copy Before:=Worksheets("Title")
ActiveSheet.Name = "Expo"
Else
Sheets("Expo").Columns("H:Z").Delete
End If
Sheets("Expo").Activate
Sheets("Expo").Range("A1").Select
Application.CutCopyMode = False
ActiveSheet.OLEObjects("txtbPoPA").object.Value ="Pop A: " & PopA
ActiveSheet.OLEObjects("txtbPoPZ").object.Value ="Pop Z: " & PopZ)
ActiveSheet.OLEObjects("txtbCityA").object.Value = "City A: " & CityA
ActiveSheet.OLEObjects("txtbCityZ").object.Value = "City Z: " & CityZ
End Sub