Read, Find, Copy and Sum Macro
 

Hi All

I don't know if this is do-able in excel, but if it is, please give me
some assistance on the matter.

I want to read information/instructions from "Sheet1", then find
information in "Sheet2" that correspond to the read information, then
copy the information that was found in "Sheet2" to "Sheet3" and add it
all together and display the sum in a particular cell in "Sheet4".

Does that sound complicated enough? Here are the details:

"Column A" and "Column B" of "Sheet1" are the starting points. The
macro must read the values in "Sheet1" "Column A" and "Column B" and
remember them. Then it must find the row in "Sheet2" "Column A" where
the information from "Sheet1" "Column A" matches and where the
information from "Sheet1" "Column B" matches with the information from
"Sheet2" "Column E".

Once that row has been found, the macro must copy the information from
"Sheet2" "Column P" and the matching row; to cell "A1" in "Sheet3". It
must then continue the search of "Sheet2" for more rows where the
information match, if another row is found the information in "Sheet2"
"Column P" and the matching row must be copied to cell "A2" in "Sheet3"
and so on and so on until all of "Sheet2" has been searched.

All the values in "Sheet3" "Column A" must then be added together and
the total for the values obtained must be written to a cell or cells
defined in "Sheet1". The location where the totals must be written to
in "Sheet4" can be found in "Sheet1" "Column D" and "Column E".

I have included extracts from "Sheet1" and "Sheet2" in CSV format
below. If anybody wants to try and give me a hand with this problem,
please copy the extracts and you should see some of the details of the
sheets.

Any help on this issue will be greatly appreciated.

Thanks,

Dan

==================Sheet1========================== ===========
,,CAA,,,,,
Organizational Area,Item,VOTE NUMBER,Copy To1,Copy To2,Column,No.,No.
21000,23001206,2100023001206,J332,J331,J,332,331
21000,23002206,2100023002206,J340,J339,J,340,339
30400,14001205,3040014001205,J280,J279,J,280,279
30400,14002204,3040014002204,J280,J279,J,280,279
30400,14003204,3040014003204,J281,J280,J,281,280
30400,14003206,3040014003206,J280,J279,J,280,279
30400,14005206,3040014005206,K280,K279,K,280,279
================================================== ===========

=================Sheet2=========================== ============
10000,Column B Info,Column C Info,Column D Info,1005000,Column F
Info,78133.75,0,0,0,0,0,0,0,0,78133.75,0,0,99600,9 9600,0,78133.75,0,0,0,0,1E+12,12,0,PE,Y,1,1,10,1
21000,Column B Info,Column C Info,Column D Info,23002206,Column F
Info,287088,23924,0,0,0,0,0,0,0,287088,23924,0,287 740,287740,0,287088,23924,0,0,0,1E+12,12,0,PE,Y,1, 1,10,1
21000,Column B Info,Column C Info,Column D Info,23001206,Column F
Info,136926.99,12158.42,0,0,0,0,0,0,0,136926.99,12 158.42,0,146250,146250,0,136926.99,12158.42,0,0,0, 1E+12,12,0,PE,Y,1,1,10,1
30400,Column B Info,Column C Info,Column D Info,14001205,Column F
Info,1306004.7,113984.2,0,0,0,0,0,0,0,1306004.7,11 3984.2,0,1371060,1371060,0,1306004.7,113984.2,0,0, 0,1E+12,12,0,PE,Y,1,1,10,1
30400,Column B Info,Column C Info,Column D Info,14001205,Column F
Info,4237181.49,370829.37,0,0,0,0,0,0,0,4237181.49 ,370829.37,0,4168075,4168075,0,4237181.49,370829.3 7,0,0,0,1E+12,12,0,PE,Y,1,1,10,1
30400,Column B Info,Column C Info,Column D Info,14001205,Column F
Info,8780,0,0,0,0,0,0,0,0,8780,0,0,0,0,0,8780,0,0, 0,0,1E+12,12,0,PE,Y,1,1,10,1
30400,Column B Info,Column C Info,Column D Info,14003206,Column F
Info,2225.73,0,0,0,0,0,0,0,0,2225.73,0,0,0,0,0,222 5.73,0,0,0,0,1E+12,12,0,PE,Y,1,1,10,1
30400,Column B Info,Column C Info,Column D Info,14005206,Column F
Info,200293.84,16017.63,0,0,0,0,0,0,0,200293.84,16 017.63,0,839730,839730,0,200293.84,16017.63,0,0,0, 1E+12,12,0,PE,Y,1,1,10,1
30400,Column B Info,Column C Info,Column D Info,14008206,Column F
Info,792952.34,74487.12,0,0,0,0,0,0,0,792952.34,74 487.12,0,845425,845425,0,792952.34,74487.12,0,0,0, 1E+12,12,0,PE,Y,1,1,10,1
30400,Column B Info,Column C Info,Column D Info,14009206,Column F
Info,20775.29,1067.71,0,0,0,0,0,0,0,20775.29,1067. 71,0,46240,46240,0,20775.29,1067.71,0,0,0,1E+12,12 ,0,PE,Y,1,1,10,1
================================================== ================================================== ==========

Read, Find, Copy and Sum Macro
 

On Sheet2, insert a new row 1, and put in entries like

Header1 header2 header3

so the data you show starts in row 2. then you can run this code.

I didn't see any reason to copy the blank value in column P - just copied
the value in the matching row. If it actually needs to be copied and summed
in, then the code could be altered.

Sub ABC()
Dim rng1 As Range, rng2 As Range
Dim rng2a As Range, rng2b As Range
Dim sh3 As Worksheet, sh4 As Worksheet
Dim sh2 As Worksheet, cell As Range
Dim cell2 As Range, i As Long
Dim dest1 As Range, dest2 As Range
With Worksheets("Sheet1")
Set rng1 = .Range(.Cells(3, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
Set sh2 = Worksheets("Sheet2")
With sh2
Set rng2 = .Range("A1").CurrentRegion
Set rng2a = Intersect(rng2, .Columns("P:P"))
Set rng2a = rng2a.Offset(1, 0) _
.Resize(rng2a.Rows.Count - 1, 1)
End With
Set sh3 = Worksheets("Sheet3")
Set sh4 = Worksheets("Sheet4")
For Each cell In rng1
Set dest1 = sh4.Range(cell.Offset(0, 3).Value)
Set dest2 = sh4.Range(cell.Offset(0, 4).Value)
rng2.AutoFilter Field:=1, Criteria1:=cell.Value
rng2.AutoFilter Field:=5, Criteria1:=cell.Offset(0, 1).Value
Set rng2b = Nothing
On Error Resume Next
Set rng2b = rng2a.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng2b Is Nothing Then
sh2.AutoFilterMode = False
sh3.Columns(1).ClearContents
i = 1
For Each cell2 In rng2b
sh3.Cells(i, 1) = cell2.Offset(1, 0)
i = i + 1
Next
dest1.Value = dest1.Value + Application.Sum(sh3.Columns(1))
dest2.Value = dest2.Value + Application.Sum(sh3.Columns(1))
End If

Next
End Sub

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
Hi All

I don't know if this is do-able in excel, but if it is, please give me
some assistance on the matter.

I want to read information/instructions from "Sheet1", then find
information in "Sheet2" that correspond to the read information, then
copy the information that was found in "Sheet2" to "Sheet3" and add it
all together and display the sum in a particular cell in "Sheet4".

Does that sound complicated enough? Here are the details:

"Column A" and "Column B" of "Sheet1" are the starting points. The
macro must read the values in "Sheet1" "Column A" and "Column B" and
remember them. Then it must find the row in "Sheet2" "Column A" where
the information from "Sheet1" "Column A" matches and where the
information from "Sheet1" "Column B" matches with the information from
"Sheet2" "Column E".

Once that row has been found, the macro must copy the information from
"Sheet2" "Column P" and the matching row; to cell "A1" in "Sheet3". It
must then continue the search of "Sheet2" for more rows where the
information match, if another row is found the information in "Sheet2"
"Column P" and the matching row must be copied to cell "A2" in "Sheet3"
and so on and so on until all of "Sheet2" has been searched.

All the values in "Sheet3" "Column A" must then be added together and
the total for the values obtained must be written to a cell or cells
defined in "Sheet1". The location where the totals must be written to
in "Sheet4" can be found in "Sheet1" "Column D" and "Column E".

I have included extracts from "Sheet1" and "Sheet2" in CSV format
below. If anybody wants to try and give me a hand with this problem,
please copy the extracts and you should see some of the details of the
sheets.

Any help on this issue will be greatly appreciated.

Thanks,

Dan

==================Sheet1========================== ===========
,,CAA,,,,,
Organizational Area,Item,VOTE NUMBER,Copy To1,Copy To2,Column,No.,No.
21000,23001206,2100023001206,J332,J331,J,332,331
21000,23002206,2100023002206,J340,J339,J,340,339
30400,14001205,3040014001205,J280,J279,J,280,279
30400,14002204,3040014002204,J280,J279,J,280,279
30400,14003204,3040014003204,J281,J280,J,281,280
30400,14003206,3040014003206,J280,J279,J,280,279
30400,14005206,3040014005206,K280,K279,K,280,279
================================================== ===========

=================Sheet2=========================== ============
10000,Column B Info,Column C Info,Column D Info,1005000,Column F
Info,78133.75,0,0,0,0,0,0,0,0,78133.75,0,0,99600,9 9600,0,78133.75,0,0,0,0,1E+12,12,0,PE,Y,1,1,10,1
21000,Column B Info,Column C Info,Column D Info,23002206,Column F
Info,287088,23924,0,0,0,0,0,0,0,287088,23924,0,287 740,287740,0,287088,23924,0,0,0,1E+12,12,0,PE,Y,1, 1,10,1
21000,Column B Info,Column C Info,Column D Info,23001206,Column F
Info,136926.99,12158.42,0,0,0,0,0,0,0,136926.99,12 158.42,0,146250,146250,0,136926.99,12158.42,0,0,0, 1E+12,12,0,PE,Y,1,1,10,1
30400,Column B Info,Column C Info,Column D Info,14001205,Column F
Info,1306004.7,113984.2,0,0,0,0,0,0,0,1306004.7,11 3984.2,0,1371060,1371060,0,1306004.7,113984.2,0,0, 0,1E+12,12,0,PE,Y,1,1,10,1
30400,Column B Info,Column C Info,Column D Info,14001205,Column F
Info,4237181.49,370829.37,0,0,0,0,0,0,0,4237181.49 ,370829.37,0,4168075,4168075,0,4237181.49,370829.3 7,0,0,0,1E+12,12,0,PE,Y,1,1,10,1
30400,Column B Info,Column C Info,Column D Info,14001205,Column F
Info,8780,0,0,0,0,0,0,0,0,8780,0,0,0,0,0,8780,0,0, 0,0,1E+12,12,0,PE,Y,1,1,10,1
30400,Column B Info,Column C Info,Column D Info,14003206,Column F
Info,2225.73,0,0,0,0,0,0,0,0,2225.73,0,0,0,0,0,222 5.73,0,0,0,0,1E+12,12,0,PE,Y,1,1,10,1
30400,Column B Info,Column C Info,Column D Info,14005206,Column F
Info,200293.84,16017.63,0,0,0,0,0,0,0,200293.84,16 017.63,0,839730,839730,0,200293.84,16017.63,0,0,0, 1E+12,12,0,PE,Y,1,1,10,1
30400,Column B Info,Column C Info,Column D Info,14008206,Column F
Info,792952.34,74487.12,0,0,0,0,0,0,0,792952.34,74 487.12,0,845425,845425,0,792952.34,74487.12,0,0,0, 1E+12,12,0,PE,Y,1,1,10,1
30400,Column B Info,Column C Info,Column D Info,14009206,Column F
Info,20775.29,1067.71,0,0,0,0,0,0,0,20775.29,1067. 71,0,46240,46240,0,20775.29,1067.71,0,0,0,1E+12,12 ,0,PE,Y,1,1,10,1
================================================== ================================================== ==========