Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Need find cell b3 value on another worksheet! | Excel Worksheet Functions | |||
Need find cell b3 value on another worksheet! | Excel Worksheet Functions | |||
Need find cell b3 on another worksheet! | Excel Programming | |||
Need find cell b3 value on another worksheet! | Excel Worksheet Functions |