none
range into array? subscript out of range vba excel

    Question

  • I have the following code using this array works:   myArr = Array("test", "3", "5")

    but when i try this: myArr = Range("a1:a3").Value it doesnt work.

    This code fails then and it says:subscript out of range:

     Set Rng = blad3.range("A:A").Find(What:=myArr(I), _
                        After:=range("A" & Rows.Count), _
                        LookIn:=xlFormulas, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)

    complete code:

    Sub FindExample1()
        'Dim myArr As Variant
        Dim Rng As range
       
       ' Dim I As Long
      
        Application.ScreenUpdating = False
       
       ' myArr = Worksheets("blad2").Range("a1:A9")
       ' myArr = Range("a1:A9")
        'myArr = Range("a1:a3").Value
       myArr = Array("test", "3", "5")

     

        'You can also use more then one value in the Array
      ' Application.Worksheets("Blad3").Activate
       
        For I = LBound(myArr) To UBound(myArr)
        Do
               'MsgBox myArr(I, 1)
     
                Set Rng = blad3.range("A:A").Find(What:=myArr(I), _
                        After:=range("A" & Rows.Count), _
                        LookIn:=xlFormulas, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
                'If you want to search in a part of the rng.value then use xlPart
                'if you use LookIn:=xlValues it will also delete rows with a
                'a formula that evaluates to "ron"
                If Not Rng Is Nothing Then Rng.EntireRow.Delete
            Loop While Not (Rng Is Nothing)
        Next I
        Application.ScreenUpdating = True
    End Sub

    can someone help me filling that array by using a range?

    Monday, October 16, 2006 1:07 PM

Answers

  • Hello Maikel,

    again I tried that macro and reformated:

    Had a list of values on blad2 and a list of values on blad3

    Take values from blad2 and search for that values on blad3

    If a value is found on blad3 - the row is deleted on blad3

    After running that macro - ro on blad3 has been removed

    Sub FindExample1()
       
        ' The worksheet that has to be modified
        Dim worksheetDestination As Worksheet
        ' The Range where to search if there are sourcevalues
        Dim rangeDestination As Range
       
       
        ' The Worksheet that contains the values that you are searchin for
        Dim worksheetSource As Worksheet
        ' The Range with the sourcevalues
        Dim rangeSource As Range
        ' An Array tha cntains the Sourcevalues
        Dim arraySourceValues As Variant
       
        ' A Variable that represents the searchresult
        Dim rangeFound As Range
      
        ' Disable Screen zapping
        Application.ScreenUpdating = False
        
        ' What worksheet contains the values that you are searching for ?
        ' I'm assuming "blad2"
        Set worksheetSource = Worksheets("blad2")
        Set rangeSource = worksheetSource.Range("A1:A3")  'a1=8 a2=5 a3=4
        arraySourceValues = rangeSource
      
        ' Get the worksheet that has to be modified
        Set worksheetDestination = Worksheets("blad3")
        ' Get the destination Range
        Set rangeDestination = worksheetDestination.Range("A:A") 'All rows from column A

        'You can also use more then one value in the Array
        Dim index As Integer
        'Iterate over the values that you are searching for
        For index = LBound(arraySourceValues) To UBound(arraySourceValues)
        Do
                ' Search for a row containig the value that you are searchin for
                Set rangeFound = rangeDestination.Find(What:=arraySourceValues(index, 1), After:=Range("A" & Rows.Count), _
                        LookIn:=xlFormulas, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)

                'If we found an entry - delete that row
                If Not rangeFound Is Nothing Then rangeFound.EntireRow.Delete
            Loop While Not (rangeFound Is Nothing)
        Next index
        'Enable zapping
        Application.ScreenUpdating = True
    End Sub

     

    Hope that helps,

    greets, Helmut

    Tuesday, October 17, 2006 6:14 PM
    Answerer

All replies

  • myArr(I), seems to get wrong can someone help me?
    Monday, October 16, 2006 2:03 PM
  • Hello eyewitness,

    this should run (hopefully)

    Sub FindExample1()
        'Dim myArr As Variant
        Dim Rng As Range
        Dim RngFound As Range

       
       ' Dim I As Long
      
        Application.ScreenUpdating = False
       
       ' myArr = Worksheets("blad2").Range("a1:A9")
       ' myArr = Range("a1:A9")
       myArr = Range("a1:a3").Value
       'myArr = Array("test", "3", "5")

     

        'You can also use more then one value in the Array
      ' Application.Worksheets("Blad3").Activate
       
        For I = LBound(myArr) To UBound(myArr)
        Do
               'MsgBox myArr(I, 1)
     
     
                Set Rng = Worksheets("blad3").Range("A:A")
               
                Set RngFound = Rng.Find(What:=myArr(I, 1), After:=Range("A" & Rows.Count), _
                        LookIn:=xlFormulas, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
                'If you want to search in a part of the rng.value then use xlPart
                'if you use LookIn:=xlValues it will also delete rows with a
                'a formula that evaluates to "ron"
                If Not RngFound Is Nothing Then RngFound.EntireRow.Delete
            Loop While Not (RngFound Is Nothing)
        Next I
        Application.ScreenUpdating = True
    End Sub

    Greets, Helmut

    Tuesday, October 17, 2006 6:18 AM
    Answerer
  • Hi hellmut,

    I'm sorry to say but i tried that too then the application hangs....

    What im trying to do is this:

    i have a1=1 a2=2 a3=3 a4=4 on blad 3

    Then i have blad2 that contains a1=8 a2=5 a3=4

    what im trying too is searching string ons blad 2 and if the string is on blad 3 then delete that row.

    Can u help me by testing it? I would really appriciate that!

    Yours,

    Maikel

     

     

    Tuesday, October 17, 2006 11:34 AM
  • Hello Maikel,

    again I tried that macro and reformated:

    Had a list of values on blad2 and a list of values on blad3

    Take values from blad2 and search for that values on blad3

    If a value is found on blad3 - the row is deleted on blad3

    After running that macro - ro on blad3 has been removed

    Sub FindExample1()
       
        ' The worksheet that has to be modified
        Dim worksheetDestination As Worksheet
        ' The Range where to search if there are sourcevalues
        Dim rangeDestination As Range
       
       
        ' The Worksheet that contains the values that you are searchin for
        Dim worksheetSource As Worksheet
        ' The Range with the sourcevalues
        Dim rangeSource As Range
        ' An Array tha cntains the Sourcevalues
        Dim arraySourceValues As Variant
       
        ' A Variable that represents the searchresult
        Dim rangeFound As Range
      
        ' Disable Screen zapping
        Application.ScreenUpdating = False
        
        ' What worksheet contains the values that you are searching for ?
        ' I'm assuming "blad2"
        Set worksheetSource = Worksheets("blad2")
        Set rangeSource = worksheetSource.Range("A1:A3")  'a1=8 a2=5 a3=4
        arraySourceValues = rangeSource
      
        ' Get the worksheet that has to be modified
        Set worksheetDestination = Worksheets("blad3")
        ' Get the destination Range
        Set rangeDestination = worksheetDestination.Range("A:A") 'All rows from column A

        'You can also use more then one value in the Array
        Dim index As Integer
        'Iterate over the values that you are searching for
        For index = LBound(arraySourceValues) To UBound(arraySourceValues)
        Do
                ' Search for a row containig the value that you are searchin for
                Set rangeFound = rangeDestination.Find(What:=arraySourceValues(index, 1), After:=Range("A" & Rows.Count), _
                        LookIn:=xlFormulas, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)

                'If we found an entry - delete that row
                If Not rangeFound Is Nothing Then rangeFound.EntireRow.Delete
            Loop While Not (rangeFound Is Nothing)
        Next index
        'Enable zapping
        Application.ScreenUpdating = True
    End Sub

     

    Hope that helps,

    greets, Helmut

    Tuesday, October 17, 2006 6:14 PM
    Answerer
  • Helmut,
    I have set up the above sample spreadsheets as described and find that it still gives me the subscript out of range message. Is there a way to stop this happening as I have an very similar workbook to search through and find that I continually get the same problems?

    My workbook required to search on different criteria and therefore requires is enclosed in a select case structure. I have cut the code down to give you an idea of what is required. Any assistance would be appreciated. Worksheets required for this part of the code are Students and BSB10101. I am trying to search for unit codes in column A of the BSB10101 worsheet on a row in the Students worksheet when that student is found

    Sub Certificate_Check()
    Dim Units As Integer
    Dim UnitFound As Boolean
    Dim CertColumn As Integer
    Dim certrow As Integer
    Dim lastrow As Integer
    Dim UnitArray()
    Dim CertUnits As Integer
    Dim IOMsNo As Variant
    Dim studentrange As Range
        ' The worksheet that to search
    Dim worksheetDestination As Worksheet
        ' The Range where to search if there are sourcevalues
    Dim rangeDestination As Range
        ' The Worksheet that contains the values to search for
    Dim worksheetSource As Worksheet
        ' The Range with the sourcevalues
    Dim rangeSource As Range
        ' An Array tha cntains the Sourcevalues
    Dim arraySourceValues As Variant
        ' A Variable that represents the searchresult
    Dim rangeFound As Range
    Dim index As Integer
     
        ' Disable Screen zapping
        Application.ScreenUpdating = False
       
        'find the current student
        IOMsNo = Right(cboxStudents.Value, 6) ' this is s a six digit string e.g. B78564
        If IOMsNo = "" Then
            Application.Goto reference:=("StudentOne")
        Else
            Application.Worksheets("Students").Select
        End If
       
    'look for the student currently chosen on a form
        Set rangeSource = Columns.Range("C:C")
            Set studentrange = Columns("C").Find(IOMsNo, LookIn:=xlValues)
            'MsgBox ActiveCell.Row
        certrow = ActiveCell.Row

    Select Case cboxQualification.Value
       
        Case "Certificate I in Business"
       
        ' Set worksheet that contains the values that you are searching for ?
        Set worksheetSource = Worksheets("BSB10101")
        Application.Goto reference:="BSBOneUnits" 'go to cert 1 units - a range name for top unit
        Set rangeSource = worksheetSource.Range(Selection, Selection.End(xlDown))
    '    Range.Select

        arraySourceValues = rangeSource
     
        ' Get the worksheet that has to be searched
        Worksheets("Students").Select
    '    Set worksheetDestination = Worksheets("Students")
        ' Get the destination Range
    '    Set rangeDestination = Worksheets("Students").Range(lCurrentRow) 'the current student

        'Iterate over the values that you are searching for
        For index = LBound(arraySourceValues) To UBound(arraySourceValues)
        Do
            Set rangeFound = Rows(certrow).Find(arraySourceValues, LookIn:=xlValues)
                If Not rangeFound Is Nothing Then
                    Units = Units + 1
                End If
                'check for compulsory units
                If rangeFound = "BSBCMN106A" Then
                    UnitFound = True
                End If
            Loop While rangeFound Is Nothing
        Next index
        If Units >= 6 And UnitFound Then
            MsgBox ("Units - " & Units)
            CertOK = True
        Else: SOAOK = True
        End If

    Thanks for your assistance,
    Allan

    Sunday, July 08, 2007 12:38 AM