none
Dim Array RRS feed

  • Question

  • Hi everybody,

    I want to implement a CommandButton. When you click on this button, it will transfer the numbers from a static array to another worksheet. Can someone help me please?

    So far I have got following:

    Private Sub CommandButton1_Click()

    Worksheets("Sheet1").Select
    Dim Prox(1 To 5) As Variant
    Prox = Range("C5:H5")
    Worksheets("Sheet2").Select
    Worksheets("Sheet2").Range("A1").Select
    If Worksheets("Sheet2").Range("A1").Offset(1, 0) <> "" Then
    Worksheets("Sheet2").Range("A2").End(xlDown).Select
    End If
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = Prox

    End Sub


    • Edited by Jagssss Tuesday, June 11, 2019 10:20 PM
    Tuesday, June 11, 2019 10:01 PM

All replies

  • Long time since you posted this so maybe you already have your answer from elsewhere but see if the following helps.

    Private Sub CommandButton1_Click()
        Dim Prox As Variant        'Don't dimension as an array; Just a variant
        Dim i As Long               'Usd to test array contents
        Dim r As Long               'Number of rows in the array
        Dim c As Long               'Number of columns in the array
       
        Worksheets("Sheet1").Select
       
        Prox = Range("C5:H5").Value    'Creates 2 dimensional one based array. Note the Value
       
        'Following loop used for testing only
        For i = LBound(Prox, 2) To UBound(Prox, 2)  'Note loops through  the 2nd dimension (columns)
           Debug.Print Prox(1, i)   'First dimension 1 is the rows and i is the columns.
        Next i
       
        Worksheets("Sheet2").Select
       
        Worksheets("Sheet2").Range("A1").Select
        If Worksheets("Sheet2").Range("A1").Offset(1, 0) <> "" Then
           Worksheets("Sheet2").Range("A2").End(xlDown).Select
        End If
       
        r = UBound(Prox, 1) 'Number of rows in array
        c = UBound(Prox, 2) 'Number of columns in array
       
        ActiveCell.Offset(1, 0).Select
       
        'Must resize the destination to match the array size
        ActiveCell.Resize(r, c).Value = Prox
       
        'The array can also be transposed as follows so it is pasted down the row
        'Note that the c and r are reversed in resize and use of WorksheetFunction.Transpose
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Resize(c, r).Value = WorksheetFunction.Transpose(Prox)
    End Sub

    Suggestion: Try the following Microsoft Community forum for faster answers. Follow the link to Office ->Excel and then select from the DropDowns at top of page. "Ask a question" is top line.

    https://answers.microsoft.com/en-us/


    Regards, OssieMac

    Thursday, July 4, 2019 7:05 AM
  • Just curious...as I am learning VBA.

    If you do...

    Worksheets("Sheet2").Select

    Are you referring to the Worksheet name or the Worksheet number?

    Basically, can you rename worksheets but still reference them by number?

    Thank you.

    Monday, July 8, 2019 1:03 AM
  • The following command references the worksheet by the tab name. Note that the name is enclosed in double quotes which indicates a string.

    Worksheets("Sheet2").Select

    You can reference a worksheet by number as follows. Note that the 2 is not enclosed in double quotes

    Worksheets(2).Select

    However, the numbers always start at the left of the tabs as they are displayed. If you rearrange the worksheets by selecting the tabs and dragging them to a different position, the displayed tab name does not change but the number of the sheet does change so referencing by the worksheet number is not reliable in VBA code.

    Open the VBA Editor and view the left column which is called Project Explorer. (If Project Explorer is not displayed then click Menu item View and select Project Explorer). You will see 2 names for each worksheet. The name NOT enclosed in brackets is called the CodeName and the name enclosed in brackets is the name as displayed in the worksheet tab. Because the default name for worksheets is always "Sheet1", "Sheet2" etc, the CodeName and Tab name will be the same until the User changes a tab name.

    Change back to the worksheets window (Alt and F11 toggles between the worksheets and the VBA editor).Try double clicking a worksheet tab and change its name and then view the Project Explorer in the VBA editor again. Note that the CodeName does not change but the worksheet tab name has changed.

    Now in VBA you can also reference the worksheets by its CodeName and if you do this then the user can alter the tab name but the CodeName does not change and therefore your VBA code remains correct. The following example code to use the CodeName to select a range.

    Sheet2.Range("A1:A10").Select

    Note that Sheet2 here is not enclosed in double quotes and does not have the leading Worksheets and the brackets.

    It is possible to change the CodeName but it can only be done in the VBA editor as follows.

    Click on the worksheet in the Project Explorer and the name will be highlighted. Press F4 and the Properties dialog will open. You can change the Code name.

    Using the CodeName in VBA code is advantageous if there is any chance that the User might change the tab name.

    This forum is mostly for Developers so in future I suggest that you try the following Microsoft Community forum which is more directed to the new users.

    https://answers.microsoft.com/en-us/

    Select Office and then select Office "Office Topic" and "Office Sub-Topic" from the DropDowns.


    Regards, OssieMac

    • Proposed as answer by mongoose00318 Thursday, August 15, 2019 5:49 PM
    Monday, July 8, 2019 1:52 AM