i need to get rid of this subscript out of range error

mareenav · Feb 14th 2020

Code

ReDim arr(0 To lastrow - 1, 0 To 4)
For Each wb In Application.Workbooks
If wb.Name <> "Offline Deal prototype.xlsm" Then
wb.Sheets("customer").Activate
For i = 1 To lastrow
For j = 1 To 5
arr(i - 1, j - 1) = Cells(i, j).Value
Next j
Next i
End If
Next wb

Display More

The line with bold letters shows subscript out of range error

Admin edit:

Code

''error here
ReDim arr(0 To lastrow - 1, 0 To 4)

**royUK** · Feb 14th 2020

Welcome to Ozgrid. I have added Code Tags to your post, please read the Forum Rules to understand why.

Where do you give a value to lastrow?

trunten · Feb 14th 2020

Can't really see why you'd get an error, but perhaps try this:

Code

ReDim arr(0 To lastrow - 1, 0 To 4)
For Each wb In Application.Workbooks
    If wb.Name <> "Offline Deal prototype.xlsm" Then
        With wb.Sheets("customer")
            For i = lbound(a) To ubound(a)
                For j = lbound(a, 2) To ubound(a, 2)
                    arr(i, j) = .Cells(i + iif(lbound(a) = 0, 1, 0), j + iif(lbound(a, 2) = 0, 1, 0)).Value
                Next j
            Next i
        End With
    End If
Next wb

Display More

mareenav · Feb 14th 2020

Code

 lastrow = wb.Sheets("customer").Range("A" & Application.Rows.Count).End(xlUp).Row

trunten · Feb 14th 2020

In that case maybe you should be putting you last row calculation inside your loop. Try this:

Code

    For Each wb In Application.Workbooks
        If wb.Name <> "Offline Deal prototype.xlsm" Then
            With wb.Sheets("customer")
                lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
                ReDim arr(0 To lastrow - 1, 0 To 4)
                For i = LBound(a) To UBound(a)
                    For j = LBound(a, 2) To UBound(a, 2)
                        arr(i, j) = .Cells(i + IIf(LBound(a) = 0, 1, 0), j + IIf(LBound(a, 2) = 0, 1, 0)).Value
                    Next j
                Next i
            End With
        End If
    Next wb

Display More

mareenav · Feb 14th 2020

thanks a lot for the code it worked!!!! at the same time got another error

trunten · Feb 14th 2020

I think you may have to let us see all of your code. Too difficult to fix just snipets.

mareenav · Feb 14th 2020

Code

Dim i, j, k, lastrow, lastrow2 As Integer
Dim arr(), outputarr() As Variant
Dim inputData As Variant
Dim custname, kam, segment, unit, contype As String
Dim debnr As Variant
Dim wb As Workbook
Dim ws As Worksheet
Dim target As Workbook


For Each wb In Application.Workbooks
 If wb.Name <> "Offline Deal prototype.xlsm" Then
 With wb.Sheets("customer")
 lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
 
 ReDim arr(0 To lastrow - 1, 0 To 4)
 For i = LBound(a) To UBound(a)
 For j = LBound(a, 2) To UBound(a, 2)
 arr(i, j) = .Cells(i + IIf(LBound(a) = 0, 1, 0), j + IIf(LBound(a, 2) = 0, 1, 0)).Value
 Next j
 Next i
 End With
 End If
 Next wb




inputData = inputbox("Enter EndurID")
For i = 1 To lastrow
    For j = 1 To 1
       If arr(i, 1) <> inputData Then
          custname = arr(i, 2)
          segment = arr(i, 3)
          kam = arr(i, 4)
          unit = arr(i, 5)
        Else: inputbox ("EndurID not found please add")
       End If
    Next j
 Next i



Workbooks("Offline Deal prototype.xlsm").Sheets("output").Activate


ReDim outputarr(0 To lastrow - 1, 0 To 37)



            For p = 1 To lastrow
                If IsEmpty(outputarr(p - 1, 0)) Then
                    outputarr(p - 1, 0) = sosys
                    outputarr(p - 1, 1) = period
                    outputarr(p - 1, 2) = contrid
                    outputarr(p - 1, 4) = unit
                    outputarr(p - 1, 5) = segment
                    outputarr(p - 1, 6) = contype
                    outputarr(p - 1, 7) = custname
                    outputarr(p - 1, 9) = debnr
                    outputarr(p - 1, 10) = icp
                    outputarr(p - 1, 11) = kam
                    outputarr(p - 1, 12) = Format(contrdat, "d.mm.yyyy")
                    outputarr(p - 1, 13) = delper
                    outputarr(p - 1, 14) = delyear
                    outputarr(p - 1, 15) = delmonth
                    outputarr(p - 1, 16) = savol
                    outputarr(p - 1, 36) = cmiii
                    outputarr(p - 1, 37) = cmiiimwh
                    Exit For
                End If
            Next p
 

lastrow2 = Range("F" & Application.Rows.Count).End(xlUp).Row

Columns(2).NumberFormat = "@"
Range(Cells(lastrow2 + 1, 1), Cells(lastrow2 + lastrow, 38)) = outputarr

Worksheets("Action").Activate

Display More

trunten · Feb 14th 2020

Hi,

Please don't take this as a crtiticism, it's not. I get the when you are still learning everything takes a lot of trial and error. Anyway, I'm not really sure what you are trying to achieve as the code just doesn't seem to make sense to me.

That being said, I've made a few assumptions as to what is meant to be happening and knocked this up for you. See how it goes. Perhaps best to create a copy before you let vba run wild!

I've added a few comments, so please read them to make sure you're happy with the assumptions I have made and also confirm to yourself that what you think is happening is actually happening.

Code

    Dim i&, j&, k&, lastrow&, lastrow2& 'Long
    Dim arr(), outputarr(), inputData, debnr 'Variant
    Dim custname$, kam$, segment$, unit$, contype$ 'String
    Dim wb As Workbook, target As Workbook
    Dim ws As Worksheet
    Dim idMatched As Boolean
        
    Set target = Workbooks("Offline Deal prototype.xlsm")
    For Each wb In Application.Workbooks
        If Not wb Is target Then
            With wb.Sheets("customer")
                lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
                ReDim arr(0 To lastrow - 1, 0 To 4)
                For i = LBound(arr) To UBound(arr)
                    For j = LBound(arr, 2) To UBound(arr, 2)
                        arr(i, j) = .Cells(i + IIf(LBound(arr) = 0, 1, 0), j + IIf(LBound(arr, 2) = 0, 1, 0)).Value
                    Next j
                Next i
            End With
        End If
        idMatched = False
        inputData = InputBox("Enter EndurID")
        For i = LBound(arr) To UBound(arr)
            If arr(i, 1) = inputData Then 'You had put this here. Assuming you meant "=" -> arr(i, 1) <> inputData
                idMatched = True
                custname = arr(i, 2)
                segment = arr(i, 3)
                kam = arr(i, 4)
                unit = arr(i, 5)
            End If
        Next i
        If Not idMatched Then
            InputBox "EndurID not found please add" 'Should this be -> MsgBox "EndurID not found please add", vbCritical
            'End 'Should we just finish here? If so uncomment this line
        Else
            With target.Sheets("output")
                ReDim outputarr(0 To lastrow - 1, 0 To 37)
                For i = LBound(outputarr) To UBound(outputarr)
                    If IsEmpty(outputarr(i, 0)) Then
                        outputarr(i, 0) = sosys 'Where is this coming from?
                        outputarr(i, 1) = Period 'Where is this coming from?
                        outputarr(i, 2) = contrid 'Where is this coming from?
                        outputarr(i, 4) = unit
                        outputarr(i, 5) = segment
                        outputarr(i, 6) = contype 'You never intialised this variable. It's still an empty string at this point
                        outputarr(i, 7) = custname
                        outputarr(i, 9) = debnr 'You never intialised this variable. It's still empty at this point
                        outputarr(i, 10) = icp 'Where is this coming from?
                        outputarr(i, 11) = kam
                        outputarr(i, 12) = Format(contrdat, "d.mm.yyyy") 'Where is this coming from?
                        outputarr(i, 13) = delper 'Where is this coming from?
                        outputarr(i, 14) = delyear 'Where is this coming from?
                        outputarr(i, 15) = delmonth 'Where is this coming from?
                        outputarr(i, 16) = savol 'Where is this coming from?
                        outputarr(i, 36) = cmiii 'Where is this coming from?
                        outputarr(i, 37) = cmiiimwh 'Where is this coming from?
                        Exit For 'Do you really want to exit the loop here????
                    End If
                Next i
                lastrow = .Range("F" & .Rows.Count).End(xlUp).Row + 1
                .Columns(2).NumberFormat = "@" 'Do you really want to do this every time? Surely it could be set once manually and just left alone?
                With .Cells(lastrow, 1).Resize(UBound(outputarr) + IIf(LBound(outputarr) = 0, 1, 0), UBound(outputarr, 2) + IIf(LBound(outputarr, 2) = 0, 1, 0))
                    .Value = outputarr
                    'Could include other formatting here. eg:
                    '.Borders.LineStyle = 1
                    '.Font.Size = 10
                    '.HorizontalAlignment = xlCenter
                    '.VerticalAlignment = xlCenter
                    '....etc...
                End With
            End With
        End If
    Next wb
    target.Worksheets("Action").Activate

Display More

trunten · Feb 14th 2020

One little thing. It's definitely not important or neccessary and others may well disagree, but I quite like to be explicit with my input boxes so I can just see from looking at the code what I'm asking for. You can specify a type for the input box (default is text) and if you have a few constants in either your sub or globally you get to have code that is a bit more obvious when you come back to it years later!

Eg:

Code

'https://docs.microsoft.com/en-us/office/vba/api/excel.application.inputbox
Sub InputBoxes()
    Const INPUT_FORMULA As Long = 0
    Const INPUT_NUMBER As Long = 1
    Const INPUT_TEXT As Long = 2
    Const INPUT_BOOLEAN As Long = 4
    Const INPUT_RANGE As Long = 8
    Const INPUT_ERROR As Long = 16
    Const INPUT_ARRAY As Long = 64
    
    Dim v as Variant

    v = Application.InputBox(Prompt:="Enter some value", Type:=INPUT_TEXT) 'NB: Use Set for range objects
End Sub

Display More

mareenav · Feb 14th 2020

Thanks a lot!!!! the other values need to be copy pasted from another workbook using different macro and a few values as I have mentioned need to be copied from the above sheet to the target sheet based on the id provided.I just need you to get some idea about what I was trying to do with this code because you really made a great effort.thanks again

i need to get rid of this subscript out of range error

Participate now!

Share

Tags