Posts by mareenav

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

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