Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Reload this Page Wanted to look for the row which matches with a value
|
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
 
Old November 12th, 2014, 09:30 AM
Authorized User
  
Join Date: Nov 2014
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
Default Wanted to look for the row which matches with a value
HI,
I wanted a code to do the following work.
Take a value from a cell in one workbook, look for that value in another workbook in the last sheet of the second workbook. IF the value matches with any value, then a value from a different column in the corresponding row.

Kindly help,

THanks,
Bharat
 
Old November 14th, 2014, 09:01 AM
Friend of Wrox
  
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default
Hi

The Cells.Find / Range.Find method is a good one. Here are some examples

http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

http://vbadud.blogspot.in/2007/05/dy...xcel-data.html

If the find text repeats multiple times in the sheet you need to iterate too

http://vbadud.blogspot.in/2007/10/ex...ll-method.html

HTH, Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old November 18th, 2014, 01:55 AM
Authorized User
  
Join Date: Nov 2014
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
Default Thank you Shasur!! Few more doubts
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 in some for
 
Old November 18th, 2014, 04:42 AM
Friend of Wrox
  
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default
Hi Bharat

Can you share the error that occurs. It should help to identify

also try if you can use Excel as a database and do a query. Since you are performing loop to find match it would slow down your applicatio. A simple join/subquery might extract the relevant data for you

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
The Following User Says Thank You to Shasur For This Useful Post:
bharatmvs (November 18th, 2014)
 
Old November 18th, 2014, 04:49 AM
Friend of Wrox
  
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default
Hi

Please use the following
http://vbadud.blogspot.in/2007/04/im...mance.html?m=1

to improve performance. At times it could cause your macro some problems

Cheers
Shasur
Quote:
Originally Posted by bharatmvs View Post
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 in some for
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old November 18th, 2014, 11:18 PM
Authorized User
  
Join Date: Nov 2014
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
Default
HI Shasur,

Thank you again. Any tutorials on how to use Excel as Database? ALso kindly suggest some good books and some means to contact you directly. I can also Understand telugu.

Other thing is, if I am using a Match function, if there are 3 rows which matches with the ID, I wanted to store all there in kind of array.

WHat would be the best possible way.

Thank you,
Bharat
 
Old November 19th, 2014, 02:55 AM
Friend of Wrox
  
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default
Hi

Please try if the following link works for understanding array

http://vbadud.blogspot.in/2007/10/ex...ethod.html?m=1

Please try to download Excel VBA reference, which is good to start with

Since I am out of touch with Excel VBA for quite sometime requesting you to get in touch with Chip Pearson (cpearson.com), Chandoo.org, John Peltier and their sites for good VBA info

All the best man

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
The Following User Says Thank You to Shasur For This Useful Post:
bharatmvs (November 19th, 2014)
 
Old November 19th, 2014, 03:44 AM
Authorized User
  
Join Date: Nov 2014
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
Default
Thank you Shasur. Will follow their websites :) But kindly do keep helping me whenever you have time.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Complex template matches? whelanj XSLT 1 January 6th, 2011 04:35 AM
Matches help JohnBampton XSLT 4 August 11th, 2009 10:03 AM
Unable to get multiple matches iinf BOOK: Beginning Regular Expressions 1 October 9th, 2008 02:18 PM
Displaying 1 to 9 of 9 matches kumiko Classic ASP Basics 2 February 21st, 2008 05:12 PM
matches without repeated items pongup BOOK: Beginning Regular Expressions 0 April 7th, 2007 09:05 PM




Contact Us - Wrox - Privacy Statement - Top

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.