none
VBA: Making References to Objects With Strings Using Strings RRS feed

  • Question

  • I am trying to create a procedure that allows flexibility in deciding the range names to be copied and pasted. However, I am having a problem in getting it to work as the range names being referred to are not really the range names but a reference to a range name. Does anyone have anyway around this problem? Please see my code below.

    ' Procedure for common usage to copy and paste data
    Private Sub ImportData(ByVal sourceCells As String, ByVal destCells As String)
        Dim tempRange As Variant
        Dim temp1 As String, temp2 As String
        
        Me.Hide
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationManual
        Application.StatusBar = "Importing information from " & wbOperating.Name & "..."
        
        For Counter = 0 To arrayCounterOp
            temp1 = sourceCells & "(" & Counter & ")"
            temp2 = destCells & "(" & Counter & ")"
            
            wbOperating.Activate
            tempRange = Range(temp1).Value
            
            wbSector.Activate
            Range(temp2).Value = tempRange
        
        Next Counter
        
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
        Application.StatusBar = False
    
    End Sub

    Monday, April 27, 2015 11:51 PM

Answers

  • Hi Cloudviake,

    According to the description, you want to refer to a range via a string variable. As far as I know, the index of Range object supports the string index to refer to the specific cell. Here is an example that declare a variable and use this variable to refer to A1 cell for your reference:

    Dim a As String
    a = "A1"
    Range(a) = "Hello!"
    You can get more samples about referring to cell from link below:

    How Do I... with Cells and Ranges

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, April 29, 2015 5:49 AM
    Moderator

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Tuesday, April 28, 2015 2:03 AM
  • Hi Cloudviake,

    According to the description, you want to refer to a range via a string variable. As far as I know, the index of Range object supports the string index to refer to the specific cell. Here is an example that declare a variable and use this variable to refer to A1 cell for your reference:

    Dim a As String
    a = "A1"
    Range(a) = "Hello!"
    You can get more samples about referring to cell from link below:

    How Do I... with Cells and Ranges

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, April 29, 2015 5:49 AM
    Moderator