subscript out of range issue. I have a workbook and I need to copy data from a sheet to another which already contains data in it of the same workbook

[mareena]

Sub customercheck()

Dim i, j, k, lastrow, lastrow2 As Integer
Dim arr(), outputarr()
Dim customername, kam, segment, unit As String
Dim endurid 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(i, 0)) Then
                        outputarr(p - 1, 2) = endurid
                        outputarr(p - 1, 7) = customername
                        outputarr(p - 1, 4) = segment
                        outputarr(p - 1, 11) = kam
                        outputarr(p - 1, 4) = unit
                    
                    Exit For
                End If
            Next p
        
        


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

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

Worksheets("Action").Activate


End Sub

 

[steve the fish]

Hi. Its always helpful to give the line that causes the error. One immediate thing that springs out is that you call your array 'arr' then refer to it as 'a' but that wouldnt cause the error you state. Its more likely the sheet called 'customer' doesnt appear in the workbook 'wb'

 

[mareena]

Hi. Its always helpful to give the line that causes the error. One immediate thing that springs out is that you call your array 'arr' then refer to it as 'a' but that wouldnt cause the error you state. Its more likely the sheet called 'customer' doesnt appear in the workbook 'wb'

here shows the error

 

[steve the fish]

How did you get to that line without error in the provided code? Please provide the code thats actually being run.

 

[mareena]

How did you get to that line without error in the provided code? Please provide the code thats actually being run.

Sub customercheck()

Dim i, j, k, lastrow, lastrow2 As Integer
Dim arr(), outputarr()
Dim customername, kam, segment, unit As String
Dim endurid 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(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
 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

[COLOR=rgb(250, 197, 28)]ReDim outputarr(0 To lastrow - 1, 0 To 37)[/COLOR]
 
                For p = 1 To lastrow
                If IsEmpty(outputarr(i, 0)) Then
                        outputarr(p - 1, 2) = endurid
                        outputarr(p - 1, 7) = customername
                        outputarr(p - 1, 4) = segment
                        outputarr(p - 1, 11) = kam
                        outputarr(p - 1, 4) = unit
                    
                    Exit For
                End If
            Next p
        
        


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

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

Worksheets("Action").Activate


End Sub

 

[steve the fish]

Put in this line just before the line that errors:

Msgbox lastrow

I suspect you get an empty message box.

 

[mareena]

Put in this line just before the line that errors:

Msgbox lastrow

I suspect you get an empty message box.

I get the same error subscript out of range highlighting the same line