VBA: Add cell value to another worksheet cell using Find and offset
 

Hi All,

I have a workbook containing the accounting for my scout group.

It contains a AnnualReport sheet and a report sheet for each month.

I want the AnnualReport to update automaticly based on each months
report.

I created a button, so that when I finished a monts report (and
verified everything ok) it updates the AnnualReport.

What I'm trying to do in a vba code (assigned to the button) is the
following:
1) look for value in cell B5 of month report (where code is located) -
That contains the expense categorie
2) Look for that value in cells A1 to A50 of sheet AnnualReport
3) Update the value of the cell to the right (offset (1.0)) adding the
value of D5 of months report
4) repeat the operation with the next 10 rows (ie B6 to B15)

I'm kinda lost, everithing I tryied gets me errors.

Thanks in advance.

Add cell value to another worksheet cell using Find and offset
 

if the layout of all the reports is identical you could do this

in B5 of the annual report put in

=sum(Jan:Dec!B5)

then you could drag fill this formula down and across. I assume the monthly
sheets are named Jan, Feb, etc. and they are all together in the tab order
with the first sheet being Jan and the last sheet being Dec.

--
Regards,
Tom Ogilvy

"Caméléon" wrote in message
oups.com...
Hi All,

I have a workbook containing the accounting for my scout group.

It contains a AnnualReport sheet and a report sheet for each month.

I want the AnnualReport to update automaticly based on each months
report.

I created a button, so that when I finished a monts report (and
verified everything ok) it updates the AnnualReport.

What I'm trying to do in a vba code (assigned to the button) is the
following:
1) look for value in cell B5 of month report (where code is located) -
That contains the expense categorie
2) Look for that value in cells A1 to A50 of sheet AnnualReport
3) Update the value of the cell to the right (offset (1.0)) adding the
value of D5 of months report
4) repeat the operation with the next 10 rows (ie B6 to B15)

I'm kinda lost, everithing I tryied gets me errors.

Thanks in advance.

VBA: Add cell value to another worksheet cell using Find and offse
 

Option Explicit

Sub UpdateAnnualReport()
Dim oCell As Range
Dim Dest As Range

For Each oCell In ActiveSheet.Range("B5:B15")
Set Dest =
Sheets("AnnualReport").Range("A1:A50").Find(What:= oCell).Offset(0, 1)
Dest = Dest + oCell.Offset(0, 2)
Next oCell

End Sub


"Caméléon" wrote:

Hi All,

I have a workbook containing the accounting for my scout group.

It contains a AnnualReport sheet and a report sheet for each month.

I want the AnnualReport to update automaticly based on each months
report.

I created a button, so that when I finished a monts report (and
verified everything ok) it updates the AnnualReport.

What I'm trying to do in a vba code (assigned to the button) is the
following:
1) look for value in cell B5 of month report (where code is located) -
That contains the expense categorie
2) Look for that value in cells A1 to A50 of sheet AnnualReport
3) Update the value of the cell to the right (offset (1.0)) adding the
value of D5 of months report
4) repeat the operation with the next 10 rows (ie B6 to B15)

I'm kinda lost, everithing I tryied gets me errors.

Thanks in advance.