More Array Noobness! Please Help!

A

Arbiter

New Member
Joined
Jul 22, 2009
Messages
20
Hi Folks,
Thanks for helping so quick the last time I had questions! I have a spreadsheet that I am using arrays to organize a set of variables into that I did some calculations on. When the calculations are done, I store them in an array, then I paste them into another sheet as what I thought were values (If I look at the sheet the values I need are there as values and not the string variable names). However, when I use the following piece of code to "re-select" the values stored on the spreadsheet for further use, the array shows up as the array of variable names I used in the past. What I would like to have are the values show up and not just the variable names. Is there somethign else I am missing? Thanks!

-Chris

Code: 
INBOARD_CHORD_ARRAY = Range("A" & COUNT1 & ":M" & COUNT1).Value

I Thought that this would pick up the cell values and store the numbers into another array in the same order the variables were in previously, except the hope is they would be just values, not variables still. Any help would be awesome! I have included the sampling of code that has this stuff in it. The problem is occurring near the bottom (Search Text for PROBLEM):


Code: 
'SELECT THE DESIRED PLANFORM INPUT SHEET:
    Sheets(PLANFORM_NEW_NAME).Select
'LOAD THE X-LOCATION OF THE FIRST PANEL INNER CHORD LEADING EDGE:
    X_LE_0 = Range("B1").Value
'LOAD THE SPAN FOR THE SEMI-SPAN % CALCULATIONS:
    SPAN = Range("B2").Value
'LOAD THE Z-LOCATION OF THE PLANFORM.  THIS WILL FORM THE LOCATION OF THE CENTERLINE AXIS FROM GLOBAL Z = 0:
    Z_0 = Range("B3").Value
'NOW WE USE A FOR LOOP TO LOOP THROUGH EACH PANEL AND PERFORM THE CALCULATIONS TO LOCATE EACH LEAD EDGE, TRAILING EDGE
'AND SPAR.
For Count = 1 To NUM_PANELS
COUNT5 = Count + 5
'CREATE AN ARRAY FOR THE I'TH PANEL:
    PANEL_ARRAY = Range("A" & COUNT5 & ":I" & COUNT5).Value
 
    'ARRAY NOMENCLATURE:
    'PANEL_ARRAY(1,1) = INNER CHORD (IN)
    'PANEL_ARRAY(1,2) = OUTER CHORD (IN)
    'PANEL_ARRAY(1,3) = QUARTER CHORD ANGLE (DEGREES FROM Y-AXIS (CW +))
    'PANEL_ARRAY(1,4) = SEMI-SPAN LOCATION OF INNER CHORD ON PANEL (%)
    'PANEL_ARRAY(1,5) = SEMI-SPAN LOCATION OF OUTER CHORD ON PANEL (%)
    'PANEL_ARRAY(1,6) = INNER CHORD LOCATION OF FRONT SPAR (%)
    'PANEL_ARRAY(1,7) = OUTER CHORD LOCATION OF FRONT SPAR (%)
    'PANEL_ARRAY(1,8) = INNER CHORD LOCATION OF REAR SPAR (%)
    'PANEL_ARRAY(1,9) = OUTER CHORD LOCATION OF REAR SPAR (%)
 
'THIS IF STATEMENT ALLOWS US TO GENERATE THE INNER MOST CHORD COORDINATES AND THEN
'SUBSEQUENTLY GENERATE THE OTHER COORDINATES BASED ON THESE INITIAL COORDINATES:
 
    If Count = 1 Then
 
    'Y-LOCATIONS OF INNER AND OUTER CHORD:
        Y_I = (SPAN / 2) * PANEL_ARRAY(1, 4) 'INNER
        Y_O = (SPAN / 2) * PANEL_ARRAY(1, 5) 'OUTER
 
    'CHANGE IN Y-LOCATION BETWEEN INNER AND OUTER CHORDS WITHIN THE PANEL:
        DY = Y_O - Y_I
 
    'LOCATING LEADING EDGE OF INNER MOST CHORD:
        X_LEI = X_LE_0
 
    'LOCATING TRAILING EDGE OF INNER MOST CHORD:
        X_TEI = X_LEI + PANEL_ARRAY(1, 1)
 
    'LOCATING LEADING EDGE OF THE OUTER PANEL:
        If PANEL_ARRAY(1, 3) = 90 Then 'THIS HELPS SET THE CHANGE IN CHORD DUE TO 90 DEGREE SWEEP TO ZERO
            TANGENT = 0
            Else
            TANGENT = Tan(WorksheetFunction.Radians(PANEL_ARRAY(1, 3)))
        End If
 
        'CHANGE IN LEADING EDGE X-VALUE FROM INNER CHORD LOCATION
        DX_LEO = 0.25 * PANEL_ARRAY(1, 1) + DY * TANGENT - 0.25 * PANEL_ARRAY(1, 2)
 
        'X-LOCATION OF OUTER CHORD LEADING EDGE
        X_LEO = X_LEI + DX_LEO
 
    'X-LOCATION OF OUTER CHORD TRAILING EDGE
        X_TEO = X_LEO + PANEL_ARRAY(1, 2)
 
    'X-LOCATION OF INNER CHORD FORWARD SPAR
        X_SP_IFWD = X_LEI + PANEL_ARRAY(1, 6) * PANEL_ARRAY(1, 1)
 
    'X-LOCATION OF OUTER CHORD FORWARD SPAR
        X_SP_OFWD = X_LEO + PANEL_ARRAY(1, 7) * PANEL_ARRAY(1, 2)
 
    'X-LOCATION OF INNER CHORD REAR SPAR
        X_SP_IAFT = X_TEI - PANEL_ARRAY(1, 8) * PANEL_ARRAY(1, 1)
 
    'X-LOCATION OF OUTER CHORD REAR SPAR
        X_SP_OAFT = X_TEO - PANEL_ARRAY(1, 9) * PANEL_ARRAY(1, 2)
 
    'CALCULATING THE AREA OF THE I'TH PANEL:
        AREA = DY * PANEL_ARRAY(1, 1) - 0.5 * DY * (X_LEO - X_LEI) - 0.5 * DY * (PANEL_ARRAY(1, 1) - X_TEO)
 
'PARTITION VALUES INTO ARRAY THAT WILL GO INTO THE OUTPUT SPREADSHEET AND BECOME THE BEGINNING CALCULATIONS FOR THE NEXT
'PANEL CALCULATION.  THE INNER CHORD VALUES WILL BE RECORDED, WHILE THE OUTER CHORD WILL BECOME THE INNER CHORD
 
    'CREATE AN ARRAY OF VALUES FOR THE INNER PANEL THAT WILL BE PASTED INTO THE OUTPUT SPREADSHEET THIS ITERATION:
 
        INNER_CHORD_ARRAY = Array(X_LEI, Y_I, Z_0, X_TEI, Y_I, Z_0, X_SP_IFWD, Y_I, Z_0, X_SP_IAFT, Y_I, Z_0, PANEL_ARRAY(1, 1))
 
        OUTER_CHORD_ARRAY = Array(X_LEO, Y_O, Z_0, X_TEO, Y_O, Z_0, X_SP_OFWD, Y_O, Z_0, X_SP_OAFT, Y_O, Z_0, PANEL_ARRAY(1, 2))
 
'GO TO THE OUTPUT SPREADSHEET AND PASTE IN THE VALUES OF THE ARRAYS ABOVE:
    Sheets(PLANFORM_OUTPUT_SHEET).Select
 
    Range("A2:M2").Value = INNER_CHORD_ARRAY
    Range("A3:M3").Value = OUTER_CHORD_ARRAY
 
 
'THE ABOVE CALCULATIONS ARE FOR THE FIRST PANEL ONLY.  NOW WE WILL USE THE VALUES FOR THE OUTER CHORD OF THE FIRST
'PANEL TO DO CALCULATIONS ON THE NEXT PANEL AND SO ON:
 
    Else
 
    'SELECT OUTPUT SPREADSHEET AND CREATE AN ARRAY OF VALUES THAT CAN BE USED AS THE DEFINITION OF THE INNER CHORD OF
    'THE CURRENT I'TH PANEL:
 
        COUNT1 = Count + 1
 
       Sheets(PLANFORM_OUTPUT_SHEET).Select
 
'*******************************************************
'*******************************************************
'*******************************************************
'***********************PROBLEM*BEGINS*******************   
'*******************************************************
'*******************************************************
 
       INBOARD_CHORD_ARRAY = Range("A" & COUNT1 & ":M" & COUNT1).Value
 
'*******************************************************
'*******************************************************
'*******************************************************
'*******************************************************
'*******************************************************
'*******************************************************
'*******************************************************
 
    'ARRAY NOMENCLATURE.  EACH VALUE BECOMES THE NEW INNER CORRESPONDING LOCATION OF THE NEXT PANEL:
    'INBOARD_CHORD_ARRAY(1)= PREVIOUS PANEL'S OUTER LE X-LOCATION (IN)
    'INBOARD_CHORD_ARRAY(2)= PREVIOUS PANEL'S OUTER LE Y-LOCATION (IN)
    'INBOARD_CHORD_ARRAY(3)= PREVIOUS PANEL'S OUTER LE Z-LOCATION (IN)
    'INBOARD_CHORD_ARRAY(4)= PREVIOUS PANEL'S OUTER TE X-LOCATION (IN)
    'INBOARD_CHORD_ARRAY(5)= PREVIOUS PANEL'S OUTER TE Y-LOCATION (IN)
    'INBOARD_CHORD_ARRAY(6)= PREVIOUS PANEL'S OUTER TE Z-LOCATION (IN)
    'INBOARD_CHORD_ARRAY(7)= PREVIOUS PANEL'S OUTER FWD SPAR X-LOCATION (IN)
    'INBOARD_CHORD_ARRAY(8)= PREVIOUS PANEL'S OUTER FWD SPAR Y-LOCATION (IN)
    'INBOARD_CHORD_ARRAY(9)= PREVIOUS PANEL'S OUTER FWD SPAR Z-LOCATION (IN)
    'INBOARD_CHORD_ARRAY(10)= PREVIOUS PANEL'S OUTER AFT SPAR X-LOCATION (IN)
    'INBOARD_CHORD_ARRAY(11)= PREVIOUS PANEL'S OUTER AFT SPAR Y-LOCATION (IN)
    'INBOARD_CHORD_ARRAY(12)= PREVIOUS PANEL'S OUTER AFT SPAR Z-LOCATION (IN)
    'INBOARD_CHORD_ARRAY(13)= PREVIOUS PANEL'S OUTER CHORD (IN)
 
    'RE-SELECT INPUT WORKSHEET AND MAKE ARRAY OF CURRENT PANEL:
 
       Sheets(PLANFORM_NEW_NAME).Select
 
       PANEL_ARRAY = Range("A" & COUNT5 & ":I" & COUNT5).Value
 
    'ARRAY NOMENCLATURE:
    'PANEL_ARRAY(1) = INNER CHORD (IN)
    'PANEL_ARRAY(2) = OUTER CHORD (IN)
    'PANEL_ARRAY(3) = QUARTER CHORD ANGLE (DEGREES FROM Y-AXIS (CW +))
    'PANEL_ARRAY(4) = SEMI-SPAN LOCATION OF INNER CHORD ON PANEL (%)
    'PANEL_ARRAY(5) = SEMI-SPAN LOCATION OF OUTER CHORD ON PANEL (%)
    'PANEL_ARRAY(6) = INNER CHORD LOCATION OF FRONT SPAR (%)
    'PANEL_ARRAY(7) = OUTER CHORD LOCATION OF FRONT SPAR (%)
    'PANEL_ARRAY(8) = INNER CHORD LOCATION OF REAR SPAR (%)
    'PANEL_ARRAY(9) = OUTER CHORD LOCATION OF REAR SPAR (%)
    'AND NOW FOR SIMILAR CALCULATIONS PERFORMED ABOVE:
    'Y-LOCATION OF OUTER CHORD AND CHANGE IN Y-LOCATION:
 
        Y_I = INBOARD_CHORD_ARRAY(1, 2) 'INBOARD
        Y_O = (SPAN / 2) * PANEL_ARRAY(1, 5) 'OUTER
        DY = Y_O - Y_I 'DIFFERENCE
 
   'LOCATING LEADING EDGE OF THE OUTER PANEL:
        If PANEL_ARRAY(1, 3) = 90 Then 'THIS HELPS SET THE CHANGE IN CHORD DUE TO 90 DEGREE SWEEP TO ZERO
            TANGENT = 0
            Else
            TANGENT = Tan(WorksheetFunction.Radians(PANEL_ARRAY(1, 3)))
        End If
 
        'CHANGE IN LEADING EDGE X-VALUE FROM INNER CHORD LOCATION
        DX_LEO = 0.25 * INBOARD_CHORD_ARRAY(1, 13) + DY * TANGENT - 0.25 * PANEL_ARRAY(1, 2)
 
        'X-LOCATION OF OUTER CHORD LEADING EDGE
        X_LEO = INBOARD_CHORD_ARRAY(1, 1) + DX_LEO
 
    'X-LOCATION OF OUTER CHORD TRAILING EDGE
        X_TEO = X_LEO + PANEL_ARRAY(1, 2)
 
    'X-LOCATION OF INNER CHORD FORWARD SPAR
        X_SP_IFWD = (INBOARD_CHORD_ARRAY(1, 1) + PANEL_ARRAY(1, 6) * INBOARD_CHORD_ARRAY(1, 13))
 
    'X-LOCATION OF OUTER CHORD FORWARD SPAR
        X_SP_OFWD = X_LEO + PANEL_ARRAY(1, 7) * PANEL_ARRAY(1, 2)
 
    'X-LOCATION OF INNER CHORD REAR SPAR
        X_SP_IAFT = INBOARD_CHORD_ARRAY(1, 4) - PANEL_ARRAY(1, 8) * INBOARD_CHORD_ARRAY(1, 13)
 
    'X-LOCATION OF OUTER CHORD REAR SPAR
        X_SP_OAFT = X_TEO - PANEL_ARRAY(1, 9) * PANEL_ARRAY(1, 2)
 
    'CALCULATING THE AREA OF THE I'TH PANEL:
        AREA = DY * INBOARD_CHORD_ARRAY(1, 13) - 0.5 * DY * (X_LEO - INBOARD_CHORD_ARRAY(1, 1)) - 0.5 * DY * (INBOARD_CHORD_ARRAY(1, 13) - X_TEO)
 
    'CREATING ARRAY THAT IS THE NEW OUTER CHORD OF THE I'TH PANEL
        OUTBOARD_CHORD_ARRAY = Array("X_LEO", "Y_O", "Z_0", "X_TEO", "Y_O", "Z_0", "X_SP_OFWD", "Y_O", "Z_0", "X_SP_OAFT", "Y_O", "Z_0", "PANEL_ARRAY(2)")
 
    'COPYING THE DATA TO THE OUTPUT SPREADSHEET:
        Sheets(PLANFORM_OUTPUT_SHEET).Select
 
        COUNT2 = Count + 2
 
        Range("A" & COUNT2 & ":M" & COUNT2).Value = OUTBOARD_CHORD_ARRAY
 
    End If
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

A
  • Question Question
Help Keeping Variables As Values
Replies
3
Views
522
dk
D

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top