VBA script out of range (dynamic arrays)

[robertrobert905]

Hi everyoneI'm getting the runtime error '9', script out of range errors

1.The line of the error is Accountlist(AccountlistCount) = TempText
2.GetLastRowWithData is a function that returns an int
3. temptext is a string in a cell, e.g. Account:Company1


So am I not allowed to assign text to a dynamic array?

Sub RawFileFix()

Dim Accountlist() As String
Dim AccountlistCount As Integer
Dim TempText As String
AccountlistCount = 1

Sheets("Raw files").Select

For i = 1 To GetLastRowWithData
    If InStr(1, Range("A" & CStr(i)).Value, "Account") > 0 Then  'cell says Account:...
         TempText = Range("A" & CStr(i)).Value
         Accountlist(AccountlistCount) = TempText
         AccountlistCount = AccountlistCount + 1
    End If
Next i

For j = 1 To UBound(Accountlist)
Range("B" & CStr(j)).Value = Accountlist(j)

Next j
End Sub

 

[wigi]

Try...

Sub RawFileFix()

    Dim AccountlistCount As Integer
    Dim TempText As String
    Dim Accountlist() As String
    
    Sheets("Raw files").Select
    
    ReDim Accountlist(1 To GetLastRowWithData)
    
    AccountlistCount = 1

    For i = 1 To UBound(Accountlist)
        If InStr(1, Range("A" & CStr(i)).Value, "Account") > 0 Then  'cell says Account:...
            TempText = Range("A" & CStr(i)).Value
            Accountlist(AccountlistCount) = TempText
            AccountlistCount = AccountlistCount + 1
        End If
    Next i
    
    For j = 1 To UBound(Accountlist)
        Range("B" & CStr(j)).Value = Accountlist(j)
    Next j
    
End Sub

 

[p45cal]

Try putting:
ReDim Accountlist(1 To AccountlistCount)
after
AccountlistCount = 1

and putting:
ReDim Preserve Accountlist(1 To AccountlistCount)
after:
TempText = Range("A" & CStr(i)).Value

 

[CharlesChuckieCharles]

Try...

Sub RawFileFix()

    Dim AccountlistCount As Integer
    Dim TempText As String
    Dim Accountlist() As String
    
    Sheets("Raw files").Select
    
    ReDim Accountlist(1 To GetLastRowWithData)
    
    AccountlistCount = 1

    For i = 1 To UBound(Accountlist)
        If InStr(1, Range("A" & CStr(i)).Value, "Account") > 0 Then  'cell says Account:...
            TempText = Range("A" & CStr(i)).Value
            Accountlist(AccountlistCount) = TempText
            AccountlistCount = AccountlistCount + 1
        End If
    Next i
    
    For j = 1 To UBound(Accountlist)
        Range("B" & CStr(j)).Value = Accountlist(j)
    Next j
    
End Sub

There seems to be a lot of unnecessary activity

Sub RawFileFix()

    Dim AccountlistCount As Integer
    dim x as long

    Sheets("Raw files").Select
    
    x=GetLastRowWithData
 
   AccountlistCount = 1

    For i = 1 To x
        If InStr(1, Range("A" & CStr(i)).Value, "Account") > 0 Then  'cell says Account:...
            Range("B" & CStr(AccountlistCount )).Value= Range("A" & CStr(i)).Value
            AccountlistCount = AccountlistCount + 1
        End If
    Next i
    
    
End Sub

 

[wigi]

There seems to be a lot of unnecessary activity

I did not pretend the reverse I only took the code of the OP.

FYI, the & operator forces the two parts to be Strings, so your CStr function is also redundant.

 

[robertrobert905]

thanks everyone, I did not know that you had to redim the dynamic array to static before adding any information in there.

Mr.redundant