 |
| Excel VBA Discuss using VBA for Excel programming. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
November 18th, 2014, 02:31 AM
|
|
Authorized User
|
|
Join Date: Nov 2014
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
|
|
Large Amount of data unable to run with Macro
Hi,
Thank you so much for the help.
One other major doubt i m facing is, I have written a code using Find method.
It works well for lines upto 30k. But if it is more than that, I will have to break the code into parts.
Code:
Sub macro_consolidator()
Dim spendSheet As Worksheet
Dim outputSheet As Worksheet
Dim i As Long
Dim spendlastrow As Long
Dim outputLastRow As Long
Dim spendID As String
Dim outputIDRow As Variant
Dim ctSheet As Worksheet
Dim sh As Worksheet, flg As Boolean
Application.ScreenUpdating = False
Set spendSheet = ThisWorkbook.Sheets("Sheet1")
For Each sh In Worksheets
If sh.Name Like "Output" Then flg = True:
Exit For
Next
If flg = False Then
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
ws.Name = "Output"
Range("A1").Value = "Part Category"
Range("B1").Value = "Supplier Name"
Range("C1").Value = "Part Number"
Range("D1").Value = "MPN Number"
Range("E1").Value = "Item Description"
Range("F1").Value = "Cost Centre Desc"
Range("G1").Value = "Spend Date Year"
Range("H1").Value = "Category Level 2"
Range("I1").Value = "Line NUmber"
Range("J1").Value = "Currency"
Range("K1").Value = "Unit Price"
Range("L1").Value = "Actual Spend USD"
Range("M1").Value = "CCP"
Range("N1").Value = "Corporate"
Range("O1").Value = "Fab2"
Range("P1").Value = "Fab 3"
Range("Q1").Value = "Fab3E"
Range("R1").Value = "Fab 5"
Range("S1").Value = "Fab 6"
Range("T1").Value = "Fab 7"
Range("U1").Value = "Total"
Range("V1").Value = "Consignment"
Range("W1").Value = "Special words"
Range("X1").Value = "Fab 8"
Range("Y1").Value = "Fab 1"
Range("Z1").Value = "Upload File Value"
Range("AA1").Value = "F2-F6 saving"
Range("AB1").Value = "F7 Saving"
End If
Set outputSheet = ThisWorkbook.Sheets("output")
Set spendSheet = ThisWorkbook.Sheets("Sheet1")
spendlastrow = spendSheet.UsedRange.Rows.Count
outputLastRow = outputSheet.UsedRange.Rows.Count
For i = 1 To spendlastrow
spendID = CStr(spendSheet.Cells(i, 17)) + CStr(spendSheet.Cells(i, 3)) + CStr(spendSheet.Cells(i, 4)) + CStr(spendSheet.Cells(i, 5)) + CStr(spendSheet.Cells(i, 6)) + CStr(spendSheet.Cells(i, 10)) + CStr(spendSheet.Cells(i, 15)) + CStr(spendSheet.Cells(i, 16))
outputIDRow = Application.Match(spendID, outputSheet.Columns(35), 0)
If Not IsError(outputIDRow) Then
outputSheet.Cells(outputIDRow, 11) = spendSheet.Cells(i, 14)
outputSheet.Cells(outputIDRow, 12) = outputSheet.Cells(outputIDRow, 12) + spendSheet.Cells(i, 13) ' Actual Spend - Addition wit previous
If spendSheet.Cells(i, 1) = "CORPORATE" Then
MsgBox (outputIDRow):
outputSheet.Cells(outputIDRow, 14) = outputSheet.Cells(outputIDRow, 14) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "CCP" Then
outputSheet.Cells(outputIDRow, 13) = outputSheet.Cells(outputIDRow, 13) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 2" Then
outputSheet.Cells(outputIDRow, 15) = outputSheet.Cells(outputIDRow, 15) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 3" Then
outputSheet.Cells(outputIDRow, 16) = outputSheet.Cells(outputIDRow, 16) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 3E" Then
outputSheet.Cells(outputIDRow, 17) = outputSheet.Cells(outputIDRow, 17) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 5" Then
outputSheet.Cells(outputIDRow, 18) = outputSheet.Cells(outputIDRow, 18) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 6" Then
outputSheet.Cells(outputIDRow, 19) = outputSheet.Cells(outputIDRow, 19) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 7" Then
outputSheet.Cells(outputIDRow, 20) = outputSheet.Cells(outputIDRow, 20) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 1" Then
outputSheet.Cells(outputIDRow, 25) = outputSheet.Cells(outputIDRow, 25) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 8" Then
outputSheet.Cells(outputIDRow, 24) = outputSheet.Cells(outputIDRow, 24) + spendSheet.Cells(i, 9)
End If
' MsgBox (outputSheet.Cells(3, 13))
Else
outputLastRow = outputLastRow + 1
outputSheet.Cells(outputLastRow, 35) = spendID
outputSheet.Cells(outputLastRow, 1) = spendSheet.Cells(i, 2)
outputSheet.Cells(outputLastRow, 2) = spendSheet.Cells(i, 5)
outputSheet.Cells(outputLastRow, 3) = spendSheet.Cells(i, 4) ' was (i,5)
outputSheet.Cells(outputLastRow, 4) = spendSheet.Cells(i, 10)
outputSheet.Cells(outputLastRow, 5) = spendSheet.Cells(i, 16)
outputSheet.Cells(outputLastRow, 6) = spendSheet.Cells(i, 15)
outputSheet.Cells(outputLastRow, 7) = spendSheet.Cells(i, 6)
'outputSheet.Cells(outputLastRow, 8) = spendSheet.Cells(i, 14)
' outputSheet.Cells(outputLastRow, 9) = spendSheet.Cells(i, 11)
outputSheet.Cells(outputLastRow, 10) = spendSheet.Cells(i, 3)
outputSheet.Cells(outputLastRow, 11) = spendSheet.Cells(i, 14) ' Unit Price
outputSheet.Cells(outputLastRow, 12) = spendSheet.Cells(i, 13) ' Actual USD
'outputSheet.Cells(outputLastRow, 13) = outputSheet.Cells(outputLastRow, 13) + spendSheet.Cells(i, 19)
' outputSheet.Cells(outputLastRow, 13) = spendSheet.Cells(i, 19)
If spendSheet.Cells(i, 1) = "CORPORATE" Then
MsgBox (outputLastRow):
outputSheet.Cells(outputLastRow, 14) = outputSheet.Cells(outputLastRow, 14) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "CCP" Then
outputSheet.Cells(outputLastRow, 13) = outputSheet.Cells(outputLastRow, 13) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 2" Then
outputSheet.Cells(outputLastRow, 15) = outputSheet.Cells(outputLastRow, 15) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 3" Then
outputSheet.Cells(outputLastRow, 16) = outputSheet.Cells(outputLastRow, 16) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 3E" Then
outputSheet.Cells(outputLastRow, 17) = outputSheet.Cells(outputLastRow, 17) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 5" Then
outputSheet.Cells(outputLastRow, 18) = outputSheet.Cells(outputLastRow, 18) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 6" Then
outputSheet.Cells(outputLastRow, 19) = outputSheet.Cells(outputLastRow, 19) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 7" Then
outputSheet.Cells(outputLastRow, 20) = outputSheet.Cells(outputLastRow, 20) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 1" Then
outputSheet.Cells(outputLastRow, 25) = outputSheet.Cells(outputLastRow, 25) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 8" Then
outputSheet.Cells(outputLastRow, 24) = outputSheet.Cells(outputLastRow, 24) + spendSheet.Cells(i, 9)
End If
End If
Next i
-----------if the amount of data i run is quite big, i break it to this way--------
Code:
Sub macro_consolidator()
Dim spendSheet As Worksheet
Dim outputSheet As Worksheet
Dim i As Long
Dim spendlastrow As Long
Dim outputLastRow As Long
Dim spendID As String
Dim outputIDRow As Variant
Dim ctSheet As Worksheet
Dim sh As Worksheet, flg As Boolean
Application.ScreenUpdating = False
Set spendSheet = ThisWorkbook.Sheets("Sheet1")
For Each sh In Worksheets
If sh.Name Like "Output" Then flg = True:
Exit For
Next
If flg = False Then
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
ws.Name = "Output"
Range("A1").Value = "Part Category"
Range("B1").Value = "Supplier Name"
Range("C1").Value = "Part Number"
Range("D1").Value = "MPN Number"
Range("E1").Value = "Item Description"
Range("F1").Value = "Cost Centre Desc"
Range("G1").Value = "Spend Date Year"
Range("H1").Value = "Category Level 2"
Range("I1").Value = "Line NUmber"
Range("J1").Value = "Currency"
Range("K1").Value = "Unit Price"
Range("L1").Value = "Actual Spend USD"
Range("M1").Value = "CCP"
Range("N1").Value = "Corporate"
Range("O1").Value = "Fab2"
Range("P1").Value = "Fab 3"
Range("Q1").Value = "Fab3E"
Range("R1").Value = "Fab 5"
Range("S1").Value = "Fab 6"
Range("T1").Value = "Fab 7"
Range("U1").Value = "Total"
Range("V1").Value = "Consignment"
Range("W1").Value = "Special words"
Range("X1").Value = "Fab 8"
Range("Y1").Value = "Fab 1"
Range("Z1").Value = "Upload File Value"
Range("AA1").Value = "F2-F6 saving"
Range("AB1").Value = "F7 Saving"
End If
Set outputSheet = ThisWorkbook.Sheets("output")
Set spendSheet = ThisWorkbook.Sheets("Sheet1")
spendlastrow = spendSheet.UsedRange.Rows.Count
outputLastRow = outputSheet.UsedRange.Rows.Count
For i = 4 To 40000
'For i = 3 To 1000
spendID = CStr(spendSheet.Cells(i, 17)) + CStr(spendSheet.Cells(i, 3)) + CStr(spendSheet.Cells(i, 4)) + CStr(spendSheet.Cells(i, 5)) + CStr(spendSheet.Cells(i, 6)) + CStr(spendSheet.Cells(i, 10)) + CStr(spendSheet.Cells(i, 15)) + CStr(spendSheet.Cells(i, 16))
outputIDRow = Application.Match(spendID, outputSheet.Columns(35), 0)
If Not IsError(outputIDRow) Then
outputSheet.Cells(outputIDRow, 11) = spendSheet.Cells(i, 14)
outputSheet.Cells(outputIDRow, 12) = outputSheet.Cells(outputIDRow, 12) + spendSheet.Cells(i, 13) ' Actual Spend - Addition wit previous
If spendSheet.Cells(i, 1) = "CORPORATE" Then
MsgBox (outputIDRow):
outputSheet.Cells(outputIDRow, 14) = outputSheet.Cells(outputIDRow, 14) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "CCP" Then
outputSheet.Cells(outputIDRow, 13) = outputSheet.Cells(outputIDRow, 13) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 2" Then
outputSheet.Cells(outputIDRow, 15) = outputSheet.Cells(outputIDRow, 15) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 3" Then
outputSheet.Cells(outputIDRow, 16) = outputSheet.Cells(outputIDRow, 16) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 3E" Then
outputSheet.Cells(outputIDRow, 17) = outputSheet.Cells(outputIDRow, 17) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 5" Then
outputSheet.Cells(outputIDRow, 18) = outputSheet.Cells(outputIDRow, 18) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 6" Then
outputSheet.Cells(outputIDRow, 19) = outputSheet.Cells(outputIDRow, 19) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 7" Then
outputSheet.Cells(outputIDRow, 20) = outputSheet.Cells(outputIDRow, 20) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 1" Then
outputSheet.Cells(outputIDRow, 25) = outputSheet.Cells(outputIDRow, 25) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 8" Then
outputSheet.Cells(outputIDRow, 24) = outputSheet.Cells(outputIDRow, 24) + spendSheet.Cells(i, 9)
End If
' MsgBox (outputSheet.Cells(3, 13))
Else
outputLastRow = outputLastRow + 1
outputSheet.Cells(outputLastRow, 35) = spendID
outputSheet.Cells(outputLastRow, 1) = spendSheet.Cells(i, 2)
outputSheet.Cells(outputLastRow, 2) = spendSheet.Cells(i, 5)
outputSheet.Cells(outputLastRow, 3) = spendSheet.Cells(i, 4) ' was (i,5)
outputSheet.Cells(outputLastRow, 4) = spendSheet.Cells(i, 10)
outputSheet.Cells(outputLastRow, 5) = spendSheet.Cells(i, 16)
outputSheet.Cells(outputLastRow, 6) = spendSheet.Cells(i, 15)
outputSheet.Cells(outputLastRow, 7) = spendSheet.Cells(i, 6)
outputSheet.Cells(outputLastRow, 8) = spendSheet.Cells(i, 17)
'outputSheet.Cells(outputLastRow, 8) = spendSheet.Cells(i, 14)
' outputSheet.Cells(outputLastRow, 9) = spendSheet.Cells(i, 11)
outputSheet.Cells(outputLastRow, 10) = spendSheet.Cells(i, 3)
outputSheet.Cells(outputLastRow, 11) = spendSheet.Cells(i, 14) ' Unit Price
outputSheet.Cells(outputLastRow, 12) = spendSheet.Cells(i, 13) ' Actual USD
'outputSheet.Cells(outputLastRow, 13) = outputSheet.Cells(outputLastRow, 13) + spendSheet.Cells(i, 19)
' outputSheet.Cells(outputLastRow, 13) = spendSheet.Cells(i, 19)
If spendSheet.Cells(i, 1) = "CORPORATE" Then
MsgBox (outputLastRow):
outputSheet.Cells(outputLastRow, 14) = outputSheet.Cells(outputLastRow, 14) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "CCP" Then
outputSheet.Cells(outputLastRow, 13) = outputSheet.Cells(outputLastRow, 13) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 2" Then
outputSheet.Cells(outputLastRow, 15) = outputSheet.Cells(outputLastRow, 15) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 3" Then
outputSheet.Cells(outputLastRow, 16) = outputSheet.Cells(outputLastRow, 16) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 3E" Then
outputSheet.Cells(outputLastRow, 17) = outputSheet.Cells(outputLastRow, 17) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 5" Then
outputSheet.Cells(outputLastRow, 18) = outputSheet.Cells(outputLastRow, 18) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 6" Then
outputSheet.Cells(outputLastRow, 19) = outputSheet.Cells(outputLastRow, 19) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 7" Then
outputSheet.Cells(outputLastRow, 20) = outputSheet.Cells(outputLastRow, 20) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 1" Then
outputSheet.Cells(outputLastRow, 25) = outputSheet.Cells(outputLastRow, 25) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 8" Then
outputSheet.Cells(outputLastRow, 24) = outputSheet.Cells(outputLastRow, 24) + spendSheet.Cells(i, 9)
End If
End If
Next i
For i = 40001 To 60000
'For i = 3 To 1000
spendID = CStr(spendSheet.Cells(i, 17)) + CStr(spendSheet.Cells(i, 3)) + CStr(spendSheet.Cells(i, 4)) + CStr(spendSheet.Cells(i, 5)) + CStr(spendSheet.Cells(i, 6)) + CStr(spendSheet.Cells(i, 10)) + CStr(spendSheet.Cells(i, 15)) + CStr(spendSheet.Cells(i, 16))
outputIDRow = Application.Match(spendID, outputSheet.Columns(35), 0)
If Not IsError(outputIDRow) Then
outputSheet.Cells(outputIDRow, 11) = spendSheet.Cells(i, 14)
outputSheet.Cells(outputIDRow, 12) = outputSheet.Cells(outputIDRow, 12) + spendSheet.Cells(i, 13) ' Actual Spend - Addition wit previous
If spendSheet.Cells(i, 1) = "CORPORATE" Then
MsgBox (outputIDRow):
outputSheet.Cells(outputIDRow, 14) = outputSheet.Cells(outputIDRow, 14) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "CCP" Then
outputSheet.Cells(outputIDRow, 13) = outputSheet.Cells(outputIDRow, 13) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 2" Then
outputSheet.Cells(outputIDRow, 15) = outputSheet.Cells(outputIDRow, 15) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 3" Then
outputSheet.Cells(outputIDRow, 16) = outputSheet.Cells(outputIDRow, 16) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 3E" Then
outputSheet.Cells(outputIDRow, 17) = outputSheet.Cells(outputIDRow, 17) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 5" Then
outputSheet.Cells(outputIDRow, 18) = outputSheet.Cells(outputIDRow, 18) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 6" Then
outputSheet.Cells(outputIDRow, 19) = outputSheet.Cells(outputIDRow, 19) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 7" Then
outputSheet.Cells(outputIDRow, 20) = outputSheet.Cells(outputIDRow, 20) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 1" Then
outputSheet.Cells(outputIDRow, 25) = outputSheet.Cells(outputIDRow, 25) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 8" Then
outputSheet.Cells(outputIDRow, 24) = outputSheet.Cells(outputIDRow, 24) + spendSheet.Cells(i, 9)
End If
' MsgBox (outputSheet.Cells(3, 13))
Else
outputLastRow = outputLastRow + 1
outputSheet.Cells(outputLastRow, 35) = spendID
outputSheet.Cells(outputLastRow, 1) = spendSheet.Cells(i, 2)
outputSheet.Cells(outputLastRow, 2) = spendSheet.Cells(i, 5)
outputSheet.Cells(outputLastRow, 3) = spendSheet.Cells(i, 4) ' was (i,5)
outputSheet.Cells(outputLastRow, 4) = spendSheet.Cells(i, 10)
outputSheet.Cells(outputLastRow, 5) = spendSheet.Cells(i, 16)
outputSheet.Cells(outputLastRow, 6) = spendSheet.Cells(i, 15)
outputSheet.Cells(outputLastRow, 7) = spendSheet.Cells(i, 6)
'outputSheet.Cells(outputLastRow, 8) = spendSheet.Cells(i, 14)
' outputSheet.Cells(outputLastRow, 9) = spendSheet.Cells(i, 11)
outputSheet.Cells(outputLastRow, 10) = spendSheet.Cells(i, 3)
outputSheet.Cells(outputLastRow, 11) = spendSheet.Cells(i, 14) ' Unit Price
outputSheet.Cells(outputLastRow, 12) = spendSheet.Cells(i, 13) ' Actual USD
'outputSheet.Cells(outputLastRow, 13) = outputSheet.Cells(outputLastRow, 13) + spendSheet.Cells(i, 19)
' outputSheet.Cells(outputLastRow, 13) = spendSheet.Cells(i, 19)
If spendSheet.Cells(i, 1) = "CORPORATE" Then
MsgBox (outputLastRow):
outputSheet.Cells(outputLastRow, 14) = outputSheet.Cells(outputLastRow, 14) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "CCP" Then
outputSheet.Cells(outputLastRow, 13) = outputSheet.Cells(outputLastRow, 13) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 2" Then
outputSheet.Cells(outputLastRow, 15) = outputSheet.Cells(outputLastRow, 15) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 3" Then
outputSheet.Cells(outputLastRow, 16) = outputSheet.Cells(outputLastRow, 16) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 3E" Then
outputSheet.Cells(outputLastRow, 17) = outputSheet.Cells(outputLastRow, 17) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 5" Then
outputSheet.Cells(outputLastRow, 18) = outputSheet.Cells(outputLastRow, 18) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 6" Then
outputSheet.Cells(outputLastRow, 19) = outputSheet.Cells(outputLastRow, 19) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 7" Then
outputSheet.Cells(outputLastRow, 20) = outputSheet.Cells(outputLastRow, 20) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 1" Then
outputSheet.Cells(outputLastRow, 25) = outputSheet.Cells(outputLastRow, 25) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 8" Then
outputSheet.Cells(outputLastRow, 24) = outputSheet.Cells(outputLastRow, 24) + spendSheet.Cells(i, 9)
End If
End If
Next i
For i = 60001 To 80000
'For i = 3 To 1000
spendID = CStr(spendSheet.Cells(i, 17)) + CStr(spendSheet.Cells(i, 3)) + CStr(spendSheet.Cells(i, 4)) + CStr(spendSheet.Cells(i, 5)) + CStr(spendSheet.Cells(i, 6)) + CStr(spendSheet.Cells(i, 10)) + CStr(spendSheet.Cells(i, 15)) + CStr(spendSheet.Cells(i, 16))
outputIDRow = Application.Match(spendID, outputSheet.Columns(35), 0)
If Not IsError(outputIDRow) Then
outputSheet.Cells(outputIDRow, 11) = spendSheet.Cells(i, 14)
outputSheet.Cells(outputIDRow, 12) = outputSheet.Cells(outputIDRow, 12) + spendSheet.Cells(i, 13) ' Actual Spend - Addition wit previous
If spendSheet.Cells(i, 1) = "CORPORATE" Then
MsgBox (outputIDRow):
outputSheet.Cells(outputIDRow, 14) = outputSheet.Cells(outputIDRow, 14) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "CCP" Then
outputSheet.Cells(outputIDRow, 13) = outputSheet.Cells(outputIDRow, 13) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 2" Then
outputSheet.Cells(outputIDRow, 15) = outputSheet.Cells(outputIDRow, 15) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 3" Then
outputSheet.Cells(outputIDRow, 16) = outputSheet.Cells(outputIDRow, 16) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 3E" Then
outputSheet.Cells(outputIDRow, 17) = outputSheet.Cells(outputIDRow, 17) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 5" Then
outputSheet.Cells(outputIDRow, 18) = outputSheet.Cells(outputIDRow, 18) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 6" Then
outputSheet.Cells(outputIDRow, 19) = outputSheet.Cells(outputIDRow, 19) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 7" Then
outputSheet.Cells(outputIDRow, 20) = outputSheet.Cells(outputIDRow, 20) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 1" Then
outputSheet.Cells(outputIDRow, 25) = outputSheet.Cells(outputIDRow, 25) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 8" Then
outputSheet.Cells(outputIDRow, 24) = outputSheet.Cells(outputIDRow, 24) + spendSheet.Cells(i, 9)
End If
' MsgBox (outputSheet.Cells(3, 13))
Else
outputLastRow = outputLastRow + 1
outputSheet.Cells(outputLastRow, 35) = spendID
outputSheet.Cells(outputLastRow, 1) = spendSheet.Cells(i, 2)
outputSheet.Cells(outputLastRow, 2) = spendSheet.Cells(i, 5)
outputSheet.Cells(outputLastRow, 3) = spendSheet.Cells(i, 4) ' was (i,5)
outputSheet.Cells(outputLastRow, 4) = spendSheet.Cells(i, 10)
outputSheet.Cells(outputLastRow, 5) = spendSheet.Cells(i, 16)
outputSheet.Cells(outputLastRow, 6) = spendSheet.Cells(i, 15)
outputSheet.Cells(outputLastRow, 7) = spendSheet.Cells(i, 6)
'outputSheet.Cells(outputLastRow, 8) = spendSheet.Cells(i, 14)
' outputSheet.Cells(outputLastRow, 9) = spendSheet.Cells(i, 11)
outputSheet.Cells(outputLastRow, 10) = spendSheet.Cells(i, 3)
outputSheet.Cells(outputLastRow, 11) = spendSheet.Cells(i, 14) ' Unit Price
outputSheet.Cells(outputLastRow, 12) = spendSheet.Cells(i, 13) ' Actual USD
'outputSheet.Cells(outputLastRow, 13) = outputSheet.Cells(outputLastRow, 13) + spendSheet.Cells(i, 19)
' outputSheet.Cells(outputLastRow, 13) = spendSheet.Cells(i, 19)
If spendSheet.Cells(i, 1) = "CORPORATE" Then
MsgBox (outputLastRow):
outputSheet.Cells(outputLastRow, 14) = outputSheet.Cells(outputLastRow, 14) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "CCP" Then
outputSheet.Cells(outputLastRow, 13) = outputSheet.Cells(outputLastRow, 13) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 2" Then
outputSheet.Cells(outputLastRow, 15) = outputSheet.Cells(outputLastRow, 15) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 3" Then
outputSheet.Cells(outputLastRow, 16) = outputSheet.Cells(outputLastRow, 16) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 3E" Then
outputSheet.Cells(outputLastRow, 17) = outputSheet.Cells(outputLastRow, 17) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 5" Then
outputSheet.Cells(outputLastRow, 18) = outputSheet.Cells(outputLastRow, 18) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 6" Then
outputSheet.Cells(outputLastRow, 19) = outputSheet.Cells(outputLastRow, 19) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 7" Then
outputSheet.Cells(outputLastRow, 20) = outputSheet.Cells(outputLastRow, 20) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 1" Then
outputSheet.Cells(outputLastRow, 25) = outputSheet.Cells(outputLastRow, 25) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 8" Then
outputSheet.Cells(outputLastRow, 24) = outputSheet.Cells(outputLastRow, 24) + spendSheet.Cells(i, 9)
End If
End If
Next i
For i = 80001 To 95000
'For i = 3 To 1000
spendID = CStr(spendSheet.Cells(i, 17)) + CStr(spendSheet.Cells(i, 3)) + CStr(spendSheet.Cells(i, 4)) + CStr(spendSheet.Cells(i, 5)) + CStr(spendSheet.Cells(i, 6)) + CStr(spendSheet.Cells(i, 10)) + CStr(spendSheet.Cells(i, 15)) + CStr(spendSheet.Cells(i, 16))
outputIDRow = Application.Match(spendID, outputSheet.Columns(35), 0)
If Not IsError(outputIDRow) Then
outputSheet.Cells(outputIDRow, 11) = spendSheet.Cells(i, 14)
outputSheet.Cells(outputIDRow, 12) = outputSheet.Cells(outputIDRow, 12) + spendSheet.Cells(i, 13) ' Actual Spend - Addition wit previous
If spendSheet.Cells(i, 1) = "CORPORATE" Then
MsgBox (outputIDRow):
outputSheet.Cells(outputIDRow, 14) = outputSheet.Cells(outputIDRow, 14) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "CCP" Then
outputSheet.Cells(outputIDRow, 13) = outputSheet.Cells(outputIDRow, 13) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 2" Then
outputSheet.Cells(outputIDRow, 15) = outputSheet.Cells(outputIDRow, 15) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 3" Then
outputSheet.Cells(outputIDRow, 16) = outputSheet.Cells(outputIDRow, 16) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 3E" Then
outputSheet.Cells(outputIDRow, 17) = outputSheet.Cells(outputIDRow, 17) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 5" Then
outputSheet.Cells(outputIDRow, 18) = outputSheet.Cells(outputIDRow, 18) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 6" Then
outputSheet.Cells(outputIDRow, 19) = outputSheet.Cells(outputIDRow, 19) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 7" Then
outputSheet.Cells(outputIDRow, 20) = outputSheet.Cells(outputIDRow, 20) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 1" Then
outputSheet.Cells(outputIDRow, 25) = outputSheet.Cells(outputIDRow, 25) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 8" Then
outputSheet.Cells(outputIDRow, 24) = outputSheet.Cells(outputIDRow, 24) + spendSheet.Cells(i, 9)
End If
' MsgBox (outputSheet.Cells(3, 13))
Else
outputLastRow = outputLastRow + 1
outputSheet.Cells(outputLastRow, 35) = spendID
outputSheet.Cells(outputLastRow, 1) = spendSheet.Cells(i, 2)
outputSheet.Cells(outputLastRow, 2) = spendSheet.Cells(i, 5)
outputSheet.Cells(outputLastRow, 3) = spendSheet.Cells(i, 4) ' was (i,5)
outputSheet.Cells(outputLastRow, 4) = spendSheet.Cells(i, 10)
outputSheet.Cells(outputLastRow, 5) = spendSheet.Cells(i, 16)
outputSheet.Cells(outputLastRow, 6) = spendSheet.Cells(i, 15)
outputSheet.Cells(outputLastRow, 7) = spendSheet.Cells(i, 6)
'outputSheet.Cells(outputLastRow, 8) = spendSheet.Cells(i, 14)
' outputSheet.Cells(outputLastRow, 9) = spendSheet.Cells(i, 11)
outputSheet.Cells(outputLastRow, 10) = spendSheet.Cells(i, 3)
outputSheet.Cells(outputLastRow, 11) = spendSheet.Cells(i, 14) ' Unit Price
outputSheet.Cells(outputLastRow, 12) = spendSheet.Cells(i, 13) ' Actual USD
'outputSheet.Cells(outputLastRow, 13) = outputSheet.Cells(outputLastRow, 13) + spendSheet.Cells(i, 19)
' outputSheet.Cells(outputLastRow, 13) = spendSheet.Cells(i, 19)
If spendSheet.Cells(i, 1) = "CORPORATE" Then
MsgBox (outputLastRow):
outputSheet.Cells(outputLastRow, 14) = outputSheet.Cells(outputLastRow, 14) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "CCP" Then
outputSheet.Cells(outputLastRow, 13) = outputSheet.Cells(outputLastRow, 13) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 2" Then
outputSheet.Cells(outputLastRow, 15) = outputSheet.Cells(outputLastRow, 15) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 3" Then
outputSheet.Cells(outputLastRow, 16) = outputSheet.Cells(outputLastRow, 16) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 3E" Then
outputSheet.Cells(outputLastRow, 17) = outputSheet.Cells(outputLastRow, 17) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 5" Then
outputSheet.Cells(outputLastRow, 18) = outputSheet.Cells(outputLastRow, 18) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 6" Then
outputSheet.Cells(outputLastRow, 19) = outputSheet.Cells(outputLastRow, 19) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 7" Then
outputSheet.Cells(outputLastRow, 20) = outputSheet.Cells(outputLastRow, 20) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 1" Then
outputSheet.Cells(outputLastRow, 25) = outputSheet.Cells(outputLastRow, 25) + spendSheet.Cells(i, 9)
End If
If spendSheet.Cells(i, 1) = "FAB 8" Then
outputSheet.Cells(outputLastRow, 24) = outputSheet.Cells(outputLastRow, 24) + spendSheet.Cells(i, 9)
End If
End If
Next i
MsgBox ("DOne"):
Application.ScreenUpdating = True
End Sub
Because it says there is some error at this line outputIDRow = Application.Match(spendID, outputSheet.Columns(35), 0) and breaks at some FOR Loop.
At times it says "SYSTEM DLL , 'COMCT32.DLL' couldnt be loaded
Please help with this
|
|
|
