Re: Dont get Array value in textbox

First thing to try: after the line "USA = test & P_Lline(9)" in UT, put
"MsgBox USA" and see if USA is currently holding the correct value.

Second, you're not explicitly declaring either the type of your parameters
or whether they're being passed by value (unchangeable) or by reference
(changeable), which may be causing problems. For example, the declaration
of UT should look like this:

Public Sub UT(ByVal Segment As String, ByVal BD As <whatever>, ByVal
P_Lline As <whatever>, ByVal Mult As <whatever>, ByRef USA As String)

Obviously, you'll have to figure out what the different data types are where
I've put "<whatever>", and any variable that you want to change the value of
within a function, you would but ByRef instead of ByVal (which I already did
for USA).

Next, if UT isn't actually returning a value, which it appears not to be,
then it should be declared as a Sub (as I did above), rather than a
Function, though there's little problem with declaring it as a Function.

Finally, in the first call to UT in the Vert Sub, you don't have enough
parameters, so USA is not being passed to or from UT. Not sure why that
doesn't simply give you an error, though.


Rob

"vbcoder2012@xxxxxxxxx" <nsaxena9@xxxxxxxxx> wrote in message
news:1171596530.160141.41680@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I am trying to assign the value of an array P_Lline(9) to USA in the
function below, and then in another procedure, i assign the value of
CityA = USA. So when i create a textbox and assign the value of CityA
to the textbox i expect to see the value of P_Lline(9) to be there,
that is Fortworth. But i dont get that.

To check my code, i declared a string test = Hello, and assigned test
along with P_Lline(9), so now i get Hello in the textbox where as i
should get Hello Fortworth. When i output this same value in an excel
*** in row 175, column 7, i get the entire value there "Hello
Fortworth". I tried switching the places (P_Lline(9) & test), hoping
to get Fortworth Hello, but i still get Hello in the textbox.

Any advise? I have been stuck on this for quite some time now.


Public Function UT(Segment, BD, 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) = "The seg is 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, BD, Pnl_Col)
Call UT(Segment, BD, P_Lline, 1)
Worksheets("expo").Select
Active***.Shapes("pct1").Select
Selection.Copy
Active***.Cells(1, 7 + Pnl_Col).Select
Active***.Paste
With Active***

' 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(n), 7 + Pnl_Col).Value = P_Lline(n)
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

Dim CntryA As String
Dim CityA As String
Dim PopA As String
Dim PopZ As String
Dim CityZ As String

CntryA = Active***.Cells(2, 7).Value
PopA = Active***.Cells(2, 4).Value
PopZ = Active***.Cells(2, 8).Value
CityZ = Active***.Cells(2, 9).Value
If CntryA = "US (US)" Then
Call UT(Segment, BD, P_Lline, Mult, USA)
CityA = USA
Else
CityA = Active***.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")
Active***.Name = "Expo"
Else

Sheets("Expo").Columns("H:Z").Delete
End If
Sheets("Expo").Activate
Sheets("Expo").Range("A1").Select
Application.CutCopyMode = False
Active***.OLEObjects("txtbPoPA").object.Value ="Pop A: " & PopA
Active***.OLEObjects("txtbPoPZ").object.Value ="Pop Z: " & PopZ
Active***.OLEObjects("txtbCityA").object.Value = "City A: " & CityA
Active***.OLEObjects("txtbCityZ").object.Value = "City Z: " & CityZ

End Sub



.

Loading