Select updated data from a range of columns

Are the values in the columns monthly expenditure or year to date?

Assuming monthly expenditure you would simply need to add the monthly values
together with something like =SUM(A2:C2).
If each is a year to date total, then you just need to find the maximum
value from the columns with somethign like =MAX(A2:C2). If the values are
entered as negative numbers replace MAX with MIN.

--
Ian
--
"Alylia" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have columns of data into which entry should be made at different time
> intervals.
>
> An example of the field format is given below:
>
> Expenditure Expenditure Expenditure
> as at 31/8 as at 30/9 as at 31/10
>
>
> The cumulative expenditures as at the given dates would have to be entered
> under the various coulmns and I would like assistance on which
> syntax/formula
> I should use in a field called "Total Expenditures" to get the latest data
> from the columns of data.
>
> Thx

Go to Tools>Macro>Macros... and enter Total_Expenditures (or another name,
without spaces) as the name, then click Create. Copy and paste all but the
first and last lines of my code (they are already there). Close the Visual
Basic window.

I notice in the message below that some of the lines have wrapped. I have
marked the beginning of each line of code with a *. Anything in between
belongs on the line before.

To run the macro, go to Tools>Macro>Macros..., highlight the macro name and
click run.

--
Ian
--
"Alylia" <[email protected]> wrote in message
news:[email protected]...
> Please can I be guided as to how I should create the macro i.e. what
> should I
> do with the syntax below.
>
> "Ian" wrote:
>
>> Try this macro. I've made some assumptions, as below
>> Assuming you have more than 1 line of monthly totals for different
>> products/departments.
>> Assuming monthly data starts in row 2
>> Assuming monthly data is in columns 1 to 12 (A to L)
>> Assuming Current total is in column 13 (M)
>>
*>> Private Sub Total_Expenditures()
*>> For r = 2 To 20 ' First row of data to last row of data
*>> If Cells(r, 1).Value = "" Then ' If data cell in column A is blank
*>> Cells(r, 13).Value = "" ' Make column M blank
*>> GoTo subend ' Go to end of macro
*>> End If
*>> For c = 1 To 12 ' For columns A to L (12 months)
*>> If Cells(r, c).Value = "" Then GoTo continue ' If column cell is blank,
>> continue with calculation
*>> Next c ' Otherwise try the next column
*>> continue:
*>> c = c - 1 ' Move back one column from the first blank
*>> Cells(r, 13).Value = Cells(r, c) ' Make colum M equal to last column
with
>> value
*>> subend:
*>> Next r ' Start on the next row of data
*>> End Sub
>>
>> --
>> Ian
>> --
>> "Alylia" <[email protected]> wrote in message
>> news:[email protected]...
>> > Thank you for your support
>> >
>> > The expenditures are year to date, but there is a potential problem
>> > with
>> > what you have suggested.
>> >
>> > The total expenditure including funds which have been committed and
>> > which
>> > may be liquidated or cancelled at a later date, as a result the total
>> > expenditure for say 31 August may be higher than that of 30 September
>> > as
>> > some
>> > funds might have been liquidated in September. The formula should pick
>> > out
>> > the total expenditure as at 30 September and not the maximum as you
>> > have
>> > suggested.
>> >
>> > Thx once again
>> >
>> > "Ian" wrote:
>> >
>> >> Are the values in the columns monthly expenditure or year to date?
>> >>
>> >> Assuming monthly expenditure you would simply need to add the monthly
>> >> values
>> >> together with something like =SUM(A2:C2).
>> >> If each is a year to date total, then you just need to find the
>> >> maximum
>> >> value from the columns with somethign like =MAX(A2:C2). If the values
>> >> are
>> >> entered as negative numbers replace MAX with MIN.
>> >>
>> >> --
>> >> Ian
>> >> --
>> >> "Alylia" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Hello,
>> >> >
>> >> > I have columns of data into which entry should be made at different
>> >> > time
>> >> > intervals.
>> >> >
>> >> > An example of the field format is given below:
>> >> >
>> >> > Expenditure Expenditure Expenditure
>> >> > as at 31/8 as at 30/9 as at 31/10
>> >> >
>> >> >
>> >> > The cumulative expenditures as at the given dates would have to be
>> >> > entered
>> >> > under the various coulmns and I would like assistance on which
>> >> > syntax/formula
>> >> > I should use in a field called "Total Expenditures" to get the
>> >> > latest
>> >> > data
>> >> > from the columns of data.
>> >> >
>> >> > Thx
>> >>
>> >>
>> >>
>>
>>
>>

Please can I be guided as to how I should create the macro i.e. what should I
do with the syntax below.

"Ian" wrote:

> Try this macro. I've made some assumptions, as below
> Assuming you have more than 1 line of monthly totals for different
> products/departments.
> Assuming monthly data starts in row 2
> Assuming monthly data is in columns 1 to 12 (A to L)
> Assuming Current total is in column 13 (M)
>
> Private Sub Total_Expenditures()
> For r = 2 To 20 ' First row of data to last row of data
> If Cells(r, 1).Value = "" Then ' If data cell in column A is blank
> Cells(r, 13).Value = "" ' Make column M blank
> GoTo subend ' Go to end of macro
> End If
> For c = 1 To 12 ' For columns A to L (12 months)
> If Cells(r, c).Value = "" Then GoTo continue ' If column cell is blank,
> continue with calculation
> Next c ' Otherwise try the next column
> continue:
> c = c - 1 ' Move back one column from the first blank
> Cells(r, 13).Value = Cells(r, c) ' Make colum M equal to last column with
> value
> subend:
> Next r ' Start on the next row of data
> End Sub
>
> --
> Ian
> --
> "Alylia" <[email protected]> wrote in message
> news:[email protected]...
> > Thank you for your support
> >
> > The expenditures are year to date, but there is a potential problem with
> > what you have suggested.
> >
> > The total expenditure including funds which have been committed and which
> > may be liquidated or cancelled at a later date, as a result the total
> > expenditure for say 31 August may be higher than that of 30 September as
> > some
> > funds might have been liquidated in September. The formula should pick out
> > the total expenditure as at 30 September and not the maximum as you have
> > suggested.
> >
> > Thx once again
> >
> > "Ian" wrote:
> >
> >> Are the values in the columns monthly expenditure or year to date?
> >>
> >> Assuming monthly expenditure you would simply need to add the monthly
> >> values
> >> together with something like =SUM(A2:C2).
> >> If each is a year to date total, then you just need to find the maximum
> >> value from the columns with somethign like =MAX(A2:C2). If the values are
> >> entered as negative numbers replace MAX with MIN.
> >>
> >> --
> >> Ian
> >> --
> >> "Alylia" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Hello,
> >> >
> >> > I have columns of data into which entry should be made at different
> >> > time
> >> > intervals.
> >> >
> >> > An example of the field format is given below:
> >> >
> >> > Expenditure Expenditure Expenditure
> >> > as at 31/8 as at 30/9 as at 31/10
> >> >
> >> >
> >> > The cumulative expenditures as at the given dates would have to be
> >> > entered
> >> > under the various coulmns and I would like assistance on which
> >> > syntax/formula
> >> > I should use in a field called "Total Expenditures" to get the latest
> >> > data
> >> > from the columns of data.
> >> >
> >> > Thx
> >>
> >>
> >>
>
>
>

Try this macro. I've made some assumptions, as below
Assuming you have more than 1 line of monthly totals for different
products/departments.
Assuming monthly data starts in row 2
Assuming monthly data is in columns 1 to 12 (A to L)
Assuming Current total is in column 13 (M)

Private Sub Total_Expenditures()
For r = 2 To 20 ' First row of data to last row of data
If Cells(r, 1).Value = "" Then ' If data cell in column A is blank
Cells(r, 13).Value = "" ' Make column M blank
GoTo subend ' Go to end of macro
End If
For c = 1 To 12 ' For columns A to L (12 months)
If Cells(r, c).Value = "" Then GoTo continue ' If column cell is blank,
continue with calculation
Next c ' Otherwise try the next column
continue:
c = c - 1 ' Move back one column from the first blank
Cells(r, 13).Value = Cells(r, c) ' Make colum M equal to last column with
value
subend:
Next r ' Start on the next row of data
End Sub

--
Ian
--
"Alylia" <[email protected]> wrote in message
news:[email protected]...
> Thank you for your support
>
> The expenditures are year to date, but there is a potential problem with
> what you have suggested.
>
> The total expenditure including funds which have been committed and which
> may be liquidated or cancelled at a later date, as a result the total
> expenditure for say 31 August may be higher than that of 30 September as
> some
> funds might have been liquidated in September. The formula should pick out
> the total expenditure as at 30 September and not the maximum as you have
> suggested.
>
> Thx once again
>
> "Ian" wrote:
>
>> Are the values in the columns monthly expenditure or year to date?
>>
>> Assuming monthly expenditure you would simply need to add the monthly
>> values
>> together with something like =SUM(A2:C2).
>> If each is a year to date total, then you just need to find the maximum
>> value from the columns with somethign like =MAX(A2:C2). If the values are
>> entered as negative numbers replace MAX with MIN.
>>
>> --
>> Ian
>> --
>> "Alylia" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hello,
>> >
>> > I have columns of data into which entry should be made at different
>> > time
>> > intervals.
>> >
>> > An example of the field format is given below:
>> >
>> > Expenditure Expenditure Expenditure
>> > as at 31/8 as at 30/9 as at 31/10
>> >
>> >
>> > The cumulative expenditures as at the given dates would have to be
>> > entered
>> > under the various coulmns and I would like assistance on which
>> > syntax/formula
>> > I should use in a field called "Total Expenditures" to get the latest
>> > data
>> > from the columns of data.
>> >
>> > Thx
>>
>>
>>

Thank you for your support

The expenditures are year to date, but there is a potential problem with
what you have suggested.

The total expenditure including funds which have been committed and which
may be liquidated or cancelled at a later date, as a result the total
expenditure for say 31 August may be higher than that of 30 September as some
funds might have been liquidated in September. The formula should pick out
the total expenditure as at 30 September and not the maximum as you have
suggested.

Thx once again

"Ian" wrote:

> Are the values in the columns monthly expenditure or year to date?
>
> Assuming monthly expenditure you would simply need to add the monthly values
> together with something like =SUM(A2:C2).
> If each is a year to date total, then you just need to find the maximum
> value from the columns with somethign like =MAX(A2:C2). If the values are
> entered as negative numbers replace MAX with MIN.
>
> --
> Ian
> --
> "Alylia" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > I have columns of data into which entry should be made at different time
> > intervals.
> >
> > An example of the field format is given below:
> >
> > Expenditure Expenditure Expenditure
> > as at 31/8 as at 30/9 as at 31/10
> >
> >
> > The cumulative expenditures as at the given dates would have to be entered
> > under the various coulmns and I would like assistance on which
> > syntax/formula
> > I should use in a field called "Total Expenditures" to get the latest data
> > from the columns of data.
> >
> > Thx
>
>
>

Go to Tools>Macro>Macros... and enter Total_Expenditures (or another name,
without spaces) as the name, then click Create. Copy and paste all but the
first and last lines of my code (they are already there). Close the Visual
Basic window.

I notice in the message below that some of the lines have wrapped. I have
marked the beginning of each line of code with a *. Anything in between
belongs on the line before.

To run the macro, go to Tools>Macro>Macros..., highlight the macro name and
click run.

--
Ian
--
"Alylia" <[email protected]> wrote in message
news:[email protected]...
> Please can I be guided as to how I should create the macro i.e. what
> should I
> do with the syntax below.
>
> "Ian" wrote:
>
>> Try this macro. I've made some assumptions, as below
>> Assuming you have more than 1 line of monthly totals for different
>> products/departments.
>> Assuming monthly data starts in row 2
>> Assuming monthly data is in columns 1 to 12 (A to L)
>> Assuming Current total is in column 13 (M)
>>
*>> Private Sub Total_Expenditures()
*>> For r = 2 To 20 ' First row of data to last row of data
*>> If Cells(r, 1).Value = "" Then ' If data cell in column A is blank
*>> Cells(r, 13).Value = "" ' Make column M blank
*>> GoTo subend ' Go to end of macro
*>> End If
*>> For c = 1 To 12 ' For columns A to L (12 months)
*>> If Cells(r, c).Value = "" Then GoTo continue ' If column cell is blank,
>> continue with calculation
*>> Next c ' Otherwise try the next column
*>> continue:
*>> c = c - 1 ' Move back one column from the first blank
*>> Cells(r, 13).Value = Cells(r, c) ' Make colum M equal to last column
with
>> value
*>> subend:
*>> Next r ' Start on the next row of data
*>> End Sub
>>
>> --
>> Ian
>> --
>> "Alylia" <[email protected]> wrote in message
>> news:[email protected]...
>> > Thank you for your support
>> >
>> > The expenditures are year to date, but there is a potential problem
>> > with
>> > what you have suggested.
>> >
>> > The total expenditure including funds which have been committed and
>> > which
>> > may be liquidated or cancelled at a later date, as a result the total
>> > expenditure for say 31 August may be higher than that of 30 September
>> > as
>> > some
>> > funds might have been liquidated in September. The formula should pick
>> > out
>> > the total expenditure as at 30 September and not the maximum as you
>> > have
>> > suggested.
>> >
>> > Thx once again
>> >
>> > "Ian" wrote:
>> >
>> >> Are the values in the columns monthly expenditure or year to date?
>> >>
>> >> Assuming monthly expenditure you would simply need to add the monthly
>> >> values
>> >> together with something like =SUM(A2:C2).
>> >> If each is a year to date total, then you just need to find the
>> >> maximum
>> >> value from the columns with somethign like =MAX(A2:C2). If the values
>> >> are
>> >> entered as negative numbers replace MAX with MIN.
>> >>
>> >> --
>> >> Ian
>> >> --
>> >> "Alylia" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Hello,
>> >> >
>> >> > I have columns of data into which entry should be made at different
>> >> > time
>> >> > intervals.
>> >> >
>> >> > An example of the field format is given below:
>> >> >
>> >> > Expenditure Expenditure Expenditure
>> >> > as at 31/8 as at 30/9 as at 31/10
>> >> >
>> >> >
>> >> > The cumulative expenditures as at the given dates would have to be
>> >> > entered
>> >> > under the various coulmns and I would like assistance on which
>> >> > syntax/formula
>> >> > I should use in a field called "Total Expenditures" to get the
>> >> > latest
>> >> > data
>> >> > from the columns of data.
>> >> >
>> >> > Thx
>> >>
>> >>
>> >>
>>
>>
>>

Please can I be guided as to how I should create the macro i.e. what should I
do with the syntax below.

"Ian" wrote:

> Try this macro. I've made some assumptions, as below
> Assuming you have more than 1 line of monthly totals for different
> products/departments.
> Assuming monthly data starts in row 2
> Assuming monthly data is in columns 1 to 12 (A to L)
> Assuming Current total is in column 13 (M)
>
> Private Sub Total_Expenditures()
> For r = 2 To 20 ' First row of data to last row of data
> If Cells(r, 1).Value = "" Then ' If data cell in column A is blank
> Cells(r, 13).Value = "" ' Make column M blank
> GoTo subend ' Go to end of macro
> End If
> For c = 1 To 12 ' For columns A to L (12 months)
> If Cells(r, c).Value = "" Then GoTo continue ' If column cell is blank,
> continue with calculation
> Next c ' Otherwise try the next column
> continue:
> c = c - 1 ' Move back one column from the first blank
> Cells(r, 13).Value = Cells(r, c) ' Make colum M equal to last column with
> value
> subend:
> Next r ' Start on the next row of data
> End Sub
>
> --
> Ian
> --
> "Alylia" <[email protected]> wrote in message
> news:[email protected]...
> > Thank you for your support
> >
> > The expenditures are year to date, but there is a potential problem with
> > what you have suggested.
> >
> > The total expenditure including funds which have been committed and which
> > may be liquidated or cancelled at a later date, as a result the total
> > expenditure for say 31 August may be higher than that of 30 September as
> > some
> > funds might have been liquidated in September. The formula should pick out
> > the total expenditure as at 30 September and not the maximum as you have
> > suggested.
> >
> > Thx once again
> >
> > "Ian" wrote:
> >
> >> Are the values in the columns monthly expenditure or year to date?
> >>
> >> Assuming monthly expenditure you would simply need to add the monthly
> >> values
> >> together with something like =SUM(A2:C2).
> >> If each is a year to date total, then you just need to find the maximum
> >> value from the columns with somethign like =MAX(A2:C2). If the values are
> >> entered as negative numbers replace MAX with MIN.
> >>
> >> --
> >> Ian
> >> --
> >> "Alylia" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Hello,
> >> >
> >> > I have columns of data into which entry should be made at different
> >> > time
> >> > intervals.
> >> >
> >> > An example of the field format is given below:
> >> >
> >> > Expenditure Expenditure Expenditure
> >> > as at 31/8 as at 30/9 as at 31/10
> >> >
> >> >
> >> > The cumulative expenditures as at the given dates would have to be
> >> > entered
> >> > under the various coulmns and I would like assistance on which
> >> > syntax/formula
> >> > I should use in a field called "Total Expenditures" to get the latest
> >> > data
> >> > from the columns of data.
> >> >
> >> > Thx
> >>
> >>
> >>
>
>
>

Try this macro. I've made some assumptions, as below
Assuming you have more than 1 line of monthly totals for different
products/departments.
Assuming monthly data starts in row 2
Assuming monthly data is in columns 1 to 12 (A to L)
Assuming Current total is in column 13 (M)

Private Sub Total_Expenditures()
For r = 2 To 20 ' First row of data to last row of data
If Cells(r, 1).Value = "" Then ' If data cell in column A is blank
Cells(r, 13).Value = "" ' Make column M blank
GoTo subend ' Go to end of macro
End If
For c = 1 To 12 ' For columns A to L (12 months)
If Cells(r, c).Value = "" Then GoTo continue ' If column cell is blank,
continue with calculation
Next c ' Otherwise try the next column
continue:
c = c - 1 ' Move back one column from the first blank
Cells(r, 13).Value = Cells(r, c) ' Make colum M equal to last column with
value
subend:
Next r ' Start on the next row of data
End Sub

--
Ian
--
"Alylia" <[email protected]> wrote in message
news:[email protected]...
> Thank you for your support
>
> The expenditures are year to date, but there is a potential problem with
> what you have suggested.
>
> The total expenditure including funds which have been committed and which
> may be liquidated or cancelled at a later date, as a result the total
> expenditure for say 31 August may be higher than that of 30 September as
> some
> funds might have been liquidated in September. The formula should pick out
> the total expenditure as at 30 September and not the maximum as you have
> suggested.
>
> Thx once again
>
> "Ian" wrote:
>
>> Are the values in the columns monthly expenditure or year to date?
>>
>> Assuming monthly expenditure you would simply need to add the monthly
>> values
>> together with something like =SUM(A2:C2).
>> If each is a year to date total, then you just need to find the maximum
>> value from the columns with somethign like =MAX(A2:C2). If the values are
>> entered as negative numbers replace MAX with MIN.
>>
>> --
>> Ian
>> --
>> "Alylia" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hello,
>> >
>> > I have columns of data into which entry should be made at different
>> > time
>> > intervals.
>> >
>> > An example of the field format is given below:
>> >
>> > Expenditure Expenditure Expenditure
>> > as at 31/8 as at 30/9 as at 31/10
>> >
>> >
>> > The cumulative expenditures as at the given dates would have to be
>> > entered
>> > under the various coulmns and I would like assistance on which
>> > syntax/formula
>> > I should use in a field called "Total Expenditures" to get the latest
>> > data
>> > from the columns of data.
>> >
>> > Thx
>>
>>
>>

Thank you for your support

The expenditures are year to date, but there is a potential problem with
what you have suggested.

The total expenditure including funds which have been committed and which
may be liquidated or cancelled at a later date, as a result the total
expenditure for say 31 August may be higher than that of 30 September as some
funds might have been liquidated in September. The formula should pick out
the total expenditure as at 30 September and not the maximum as you have
suggested.

Thx once again

"Ian" wrote:

> Are the values in the columns monthly expenditure or year to date?
>
> Assuming monthly expenditure you would simply need to add the monthly values
> together with something like =SUM(A2:C2).
> If each is a year to date total, then you just need to find the maximum
> value from the columns with somethign like =MAX(A2:C2). If the values are
> entered as negative numbers replace MAX with MIN.
>
> --
> Ian
> --
> "Alylia" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > I have columns of data into which entry should be made at different time
> > intervals.
> >
> > An example of the field format is given below:
> >
> > Expenditure Expenditure Expenditure
> > as at 31/8 as at 30/9 as at 31/10
> >
> >
> > The cumulative expenditures as at the given dates would have to be entered
> > under the various coulmns and I would like assistance on which
> > syntax/formula
> > I should use in a field called "Total Expenditures" to get the latest data
> > from the columns of data.
> >
> > Thx
>
>
>

Are the values in the columns monthly expenditure or year to date?

Assuming monthly expenditure you would simply need to add the monthly values
together with something like =SUM(A2:C2).
If each is a year to date total, then you just need to find the maximum
value from the columns with somethign like =MAX(A2:C2). If the values are
entered as negative numbers replace MAX with MIN.

--
Ian
--
"Alylia" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have columns of data into which entry should be made at different time
> intervals.
>
> An example of the field format is given below:
>
> Expenditure Expenditure Expenditure
> as at 31/8 as at 30/9 as at 31/10
>
>
> The cumulative expenditures as at the given dates would have to be entered
> under the various coulmns and I would like assistance on which
> syntax/formula
> I should use in a field called "Total Expenditures" to get the latest data
> from the columns of data.
>
> Thx

Thank you for your support

The expenditures are year to date, but there is a potential problem with
what you have suggested.

The total expenditure including funds which have been committed and which
may be liquidated or cancelled at a later date, as a result the total
expenditure for say 31 August may be higher than that of 30 September as some
funds might have been liquidated in September. The formula should pick out
the total expenditure as at 30 September and not the maximum as you have
suggested.

Thx once again

"Ian" wrote:

> Are the values in the columns monthly expenditure or year to date?
>
> Assuming monthly expenditure you would simply need to add the monthly values
> together with something like =SUM(A2:C2).
> If each is a year to date total, then you just need to find the maximum
> value from the columns with somethign like =MAX(A2:C2). If the values are
> entered as negative numbers replace MAX with MIN.
>
> --
> Ian
> --
> "Alylia" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > I have columns of data into which entry should be made at different time
> > intervals.
> >
> > An example of the field format is given below:
> >
> > Expenditure Expenditure Expenditure
> > as at 31/8 as at 30/9 as at 31/10
> >
> >
> > The cumulative expenditures as at the given dates would have to be entered
> > under the various coulmns and I would like assistance on which
> > syntax/formula
> > I should use in a field called "Total Expenditures" to get the latest data
> > from the columns of data.
> >
> > Thx
>
>
>

Go to Tools>Macro>Macros... and enter Total_Expenditures (or another name,
without spaces) as the name, then click Create. Copy and paste all but the
first and last lines of my code (they are already there). Close the Visual
Basic window.

I notice in the message below that some of the lines have wrapped. I have
marked the beginning of each line of code with a *. Anything in between
belongs on the line before.

To run the macro, go to Tools>Macro>Macros..., highlight the macro name and
click run.

--
Ian
--
"Alylia" <[email protected]> wrote in message
news:[email protected]...
> Please can I be guided as to how I should create the macro i.e. what
> should I
> do with the syntax below.
>
> "Ian" wrote:
>
>> Try this macro. I've made some assumptions, as below
>> Assuming you have more than 1 line of monthly totals for different
>> products/departments.
>> Assuming monthly data starts in row 2
>> Assuming monthly data is in columns 1 to 12 (A to L)
>> Assuming Current total is in column 13 (M)
>>
*>> Private Sub Total_Expenditures()
*>> For r = 2 To 20 ' First row of data to last row of data
*>> If Cells(r, 1).Value = "" Then ' If data cell in column A is blank
*>> Cells(r, 13).Value = "" ' Make column M blank
*>> GoTo subend ' Go to end of macro
*>> End If
*>> For c = 1 To 12 ' For columns A to L (12 months)
*>> If Cells(r, c).Value = "" Then GoTo continue ' If column cell is blank,
>> continue with calculation
*>> Next c ' Otherwise try the next column
*>> continue:
*>> c = c - 1 ' Move back one column from the first blank
*>> Cells(r, 13).Value = Cells(r, c) ' Make colum M equal to last column
with
>> value
*>> subend:
*>> Next r ' Start on the next row of data
*>> End Sub
>>
>> --
>> Ian
>> --
>> "Alylia" <[email protected]> wrote in message
>> news:[email protected]...
>> > Thank you for your support
>> >
>> > The expenditures are year to date, but there is a potential problem
>> > with
>> > what you have suggested.
>> >
>> > The total expenditure including funds which have been committed and
>> > which
>> > may be liquidated or cancelled at a later date, as a result the total
>> > expenditure for say 31 August may be higher than that of 30 September
>> > as
>> > some
>> > funds might have been liquidated in September. The formula should pick
>> > out
>> > the total expenditure as at 30 September and not the maximum as you
>> > have
>> > suggested.
>> >
>> > Thx once again
>> >
>> > "Ian" wrote:
>> >
>> >> Are the values in the columns monthly expenditure or year to date?
>> >>
>> >> Assuming monthly expenditure you would simply need to add the monthly
>> >> values
>> >> together with something like =SUM(A2:C2).
>> >> If each is a year to date total, then you just need to find the
>> >> maximum
>> >> value from the columns with somethign like =MAX(A2:C2). If the values
>> >> are
>> >> entered as negative numbers replace MAX with MIN.
>> >>
>> >> --
>> >> Ian
>> >> --
>> >> "Alylia" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Hello,
>> >> >
>> >> > I have columns of data into which entry should be made at different
>> >> > time
>> >> > intervals.
>> >> >
>> >> > An example of the field format is given below:
>> >> >
>> >> > Expenditure Expenditure Expenditure
>> >> > as at 31/8 as at 30/9 as at 31/10
>> >> >
>> >> >
>> >> > The cumulative expenditures as at the given dates would have to be
>> >> > entered
>> >> > under the various coulmns and I would like assistance on which
>> >> > syntax/formula
>> >> > I should use in a field called "Total Expenditures" to get the
>> >> > latest
>> >> > data
>> >> > from the columns of data.
>> >> >
>> >> > Thx
>> >>
>> >>
>> >>
>>
>>
>>

Please can I be guided as to how I should create the macro i.e. what should I
do with the syntax below.

"Ian" wrote:

> Try this macro. I've made some assumptions, as below
> Assuming you have more than 1 line of monthly totals for different
> products/departments.
> Assuming monthly data starts in row 2
> Assuming monthly data is in columns 1 to 12 (A to L)
> Assuming Current total is in column 13 (M)
>
> Private Sub Total_Expenditures()
> For r = 2 To 20 ' First row of data to last row of data
> If Cells(r, 1).Value = "" Then ' If data cell in column A is blank
> Cells(r, 13).Value = "" ' Make column M blank
> GoTo subend ' Go to end of macro
> End If
> For c = 1 To 12 ' For columns A to L (12 months)
> If Cells(r, c).Value = "" Then GoTo continue ' If column cell is blank,
> continue with calculation
> Next c ' Otherwise try the next column
> continue:
> c = c - 1 ' Move back one column from the first blank
> Cells(r, 13).Value = Cells(r, c) ' Make colum M equal to last column with
> value
> subend:
> Next r ' Start on the next row of data
> End Sub
>
> --
> Ian
> --
> "Alylia" <[email protected]> wrote in message
> news:[email protected]...
> > Thank you for your support
> >
> > The expenditures are year to date, but there is a potential problem with
> > what you have suggested.
> >
> > The total expenditure including funds which have been committed and which
> > may be liquidated or cancelled at a later date, as a result the total
> > expenditure for say 31 August may be higher than that of 30 September as
> > some
> > funds might have been liquidated in September. The formula should pick out
> > the total expenditure as at 30 September and not the maximum as you have
> > suggested.
> >
> > Thx once again
> >
> > "Ian" wrote:
> >
> >> Are the values in the columns monthly expenditure or year to date?
> >>
> >> Assuming monthly expenditure you would simply need to add the monthly
> >> values
> >> together with something like =SUM(A2:C2).
> >> If each is a year to date total, then you just need to find the maximum
> >> value from the columns with somethign like =MAX(A2:C2). If the values are
> >> entered as negative numbers replace MAX with MIN.
> >>
> >> --
> >> Ian
> >> --
> >> "Alylia" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Hello,
> >> >
> >> > I have columns of data into which entry should be made at different
> >> > time
> >> > intervals.
> >> >
> >> > An example of the field format is given below:
> >> >
> >> > Expenditure Expenditure Expenditure
> >> > as at 31/8 as at 30/9 as at 31/10
> >> >
> >> >
> >> > The cumulative expenditures as at the given dates would have to be
> >> > entered
> >> > under the various coulmns and I would like assistance on which
> >> > syntax/formula
> >> > I should use in a field called "Total Expenditures" to get the latest
> >> > data
> >> > from the columns of data.
> >> >
> >> > Thx
> >>
> >>
> >>
>
>
>

Are the values in the columns monthly expenditure or year to date?

Assuming monthly expenditure you would simply need to add the monthly values
together with something like =SUM(A2:C2).
If each is a year to date total, then you just need to find the maximum
value from the columns with somethign like =MAX(A2:C2). If the values are
entered as negative numbers replace MAX with MIN.

--
Ian
--
"Alylia" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have columns of data into which entry should be made at different time
> intervals.
>
> An example of the field format is given below:
>
> Expenditure Expenditure Expenditure
> as at 31/8 as at 30/9 as at 31/10
>
>
> The cumulative expenditures as at the given dates would have to be entered
> under the various coulmns and I would like assistance on which
> syntax/formula
> I should use in a field called "Total Expenditures" to get the latest data
> from the columns of data.
>
> Thx

Try this macro. I've made some assumptions, as below
Assuming you have more than 1 line of monthly totals for different
products/departments.
Assuming monthly data starts in row 2
Assuming monthly data is in columns 1 to 12 (A to L)
Assuming Current total is in column 13 (M)

Private Sub Total_Expenditures()
For r = 2 To 20 ' First row of data to last row of data
If Cells(r, 1).Value = "" Then ' If data cell in column A is blank
Cells(r, 13).Value = "" ' Make column M blank
GoTo subend ' Go to end of macro
End If
For c = 1 To 12 ' For columns A to L (12 months)
If Cells(r, c).Value = "" Then GoTo continue ' If column cell is blank,
continue with calculation
Next c ' Otherwise try the next column
continue:
c = c - 1 ' Move back one column from the first blank
Cells(r, 13).Value = Cells(r, c) ' Make colum M equal to last column with
value
subend:
Next r ' Start on the next row of data
End Sub

--
Ian
--
"Alylia" <[email protected]> wrote in message
news:[email protected]...
> Thank you for your support
>
> The expenditures are year to date, but there is a potential problem with
> what you have suggested.
>
> The total expenditure including funds which have been committed and which
> may be liquidated or cancelled at a later date, as a result the total
> expenditure for say 31 August may be higher than that of 30 September as
> some
> funds might have been liquidated in September. The formula should pick out
> the total expenditure as at 30 September and not the maximum as you have
> suggested.
>
> Thx once again
>
> "Ian" wrote:
>
>> Are the values in the columns monthly expenditure or year to date?
>>
>> Assuming monthly expenditure you would simply need to add the monthly
>> values
>> together with something like =SUM(A2:C2).
>> If each is a year to date total, then you just need to find the maximum
>> value from the columns with somethign like =MAX(A2:C2). If the values are
>> entered as negative numbers replace MAX with MIN.
>>
>> --
>> Ian
>> --
>> "Alylia" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hello,
>> >
>> > I have columns of data into which entry should be made at different
>> > time
>> > intervals.
>> >
>> > An example of the field format is given below:
>> >
>> > Expenditure Expenditure Expenditure
>> > as at 31/8 as at 30/9 as at 31/10
>> >
>> >
>> > The cumulative expenditures as at the given dates would have to be
>> > entered
>> > under the various coulmns and I would like assistance on which
>> > syntax/formula
>> > I should use in a field called "Total Expenditures" to get the latest
>> > data
>> > from the columns of data.
>> >
>> > Thx
>>
>>
>>

Try this macro. I've made some assumptions, as below
Assuming you have more than 1 line of monthly totals for different
products/departments.
Assuming monthly data starts in row 2
Assuming monthly data is in columns 1 to 12 (A to L)
Assuming Current total is in column 13 (M)

Private Sub Total_Expenditures()
For r = 2 To 20 ' First row of data to last row of data
If Cells(r, 1).Value = "" Then ' If data cell in column A is blank
Cells(r, 13).Value = "" ' Make column M blank
GoTo subend ' Go to end of macro
End If
For c = 1 To 12 ' For columns A to L (12 months)
If Cells(r, c).Value = "" Then GoTo continue ' If column cell is blank,
continue with calculation
Next c ' Otherwise try the next column
continue:
c = c - 1 ' Move back one column from the first blank
Cells(r, 13).Value = Cells(r, c) ' Make colum M equal to last column with
value
subend:
Next r ' Start on the next row of data
End Sub

--
Ian
--
"Alylia" <[email protected]> wrote in message
news:[email protected]...
> Thank you for your support
>
> The expenditures are year to date, but there is a potential problem with
> what you have suggested.
>
> The total expenditure including funds which have been committed and which
> may be liquidated or cancelled at a later date, as a result the total
> expenditure for say 31 August may be higher than that of 30 September as
> some
> funds might have been liquidated in September. The formula should pick out
> the total expenditure as at 30 September and not the maximum as you have
> suggested.
>
> Thx once again
>
> "Ian" wrote:
>
>> Are the values in the columns monthly expenditure or year to date?
>>
>> Assuming monthly expenditure you would simply need to add the monthly
>> values
>> together with something like =SUM(A2:C2).
>> If each is a year to date total, then you just need to find the maximum
>> value from the columns with somethign like =MAX(A2:C2). If the values are
>> entered as negative numbers replace MAX with MIN.
>>
>> --
>> Ian
>> --
>> "Alylia" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hello,
>> >
>> > I have columns of data into which entry should be made at different
>> > time
>> > intervals.
>> >
>> > An example of the field format is given below:
>> >
>> > Expenditure Expenditure Expenditure
>> > as at 31/8 as at 30/9 as at 31/10
>> >
>> >
>> > The cumulative expenditures as at the given dates would have to be
>> > entered
>> > under the various coulmns and I would like assistance on which
>> > syntax/formula
>> > I should use in a field called "Total Expenditures" to get the latest
>> > data
>> > from the columns of data.
>> >
>> > Thx
>>
>>
>>

Please can I be guided as to how I should create the macro i.e. what should I
do with the syntax below.

"Ian" wrote:

> Try this macro. I've made some assumptions, as below
> Assuming you have more than 1 line of monthly totals for different
> products/departments.
> Assuming monthly data starts in row 2
> Assuming monthly data is in columns 1 to 12 (A to L)
> Assuming Current total is in column 13 (M)
>
> Private Sub Total_Expenditures()
> For r = 2 To 20 ' First row of data to last row of data
> If Cells(r, 1).Value = "" Then ' If data cell in column A is blank
> Cells(r, 13).Value = "" ' Make column M blank
> GoTo subend ' Go to end of macro
> End If
> For c = 1 To 12 ' For columns A to L (12 months)
> If Cells(r, c).Value = "" Then GoTo continue ' If column cell is blank,
> continue with calculation
> Next c ' Otherwise try the next column
> continue:
> c = c - 1 ' Move back one column from the first blank
> Cells(r, 13).Value = Cells(r, c) ' Make colum M equal to last column with
> value
> subend:
> Next r ' Start on the next row of data
> End Sub
>
> --
> Ian
> --
> "Alylia" <[email protected]> wrote in message
> news:[email protected]...
> > Thank you for your support
> >
> > The expenditures are year to date, but there is a potential problem with
> > what you have suggested.
> >
> > The total expenditure including funds which have been committed and which
> > may be liquidated or cancelled at a later date, as a result the total
> > expenditure for say 31 August may be higher than that of 30 September as
> > some
> > funds might have been liquidated in September. The formula should pick out
> > the total expenditure as at 30 September and not the maximum as you have
> > suggested.
> >
> > Thx once again
> >
> > "Ian" wrote:
> >
> >> Are the values in the columns monthly expenditure or year to date?
> >>
> >> Assuming monthly expenditure you would simply need to add the monthly
> >> values
> >> together with something like =SUM(A2:C2).
> >> If each is a year to date total, then you just need to find the maximum
> >> value from the columns with somethign like =MAX(A2:C2). If the values are
> >> entered as negative numbers replace MAX with MIN.
> >>
> >> --
> >> Ian
> >> --
> >> "Alylia" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Hello,
> >> >
> >> > I have columns of data into which entry should be made at different
> >> > time
> >> > intervals.
> >> >
> >> > An example of the field format is given below:
> >> >
> >> > Expenditure Expenditure Expenditure
> >> > as at 31/8 as at 30/9 as at 31/10
> >> >
> >> >
> >> > The cumulative expenditures as at the given dates would have to be
> >> > entered
> >> > under the various coulmns and I would like assistance on which
> >> > syntax/formula
> >> > I should use in a field called "Total Expenditures" to get the latest
> >> > data
> >> > from the columns of data.
> >> >
> >> > Thx
> >>
> >>
> >>
>
>
>

Are the values in the columns monthly expenditure or year to date?

Assuming monthly expenditure you would simply need to add the monthly values
together with something like =SUM(A2:C2).
If each is a year to date total, then you just need to find the maximum
value from the columns with somethign like =MAX(A2:C2). If the values are
entered as negative numbers replace MAX with MIN.

--
Ian
--
"Alylia" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have columns of data into which entry should be made at different time
> intervals.
>
> An example of the field format is given below:
>
> Expenditure Expenditure Expenditure
> as at 31/8 as at 30/9 as at 31/10
>
>
> The cumulative expenditures as at the given dates would have to be entered
> under the various coulmns and I would like assistance on which
> syntax/formula
> I should use in a field called "Total Expenditures" to get the latest data
> from the columns of data.
>
> Thx

Thank you for your support

The expenditures are year to date, but there is a potential problem with
what you have suggested.

The total expenditure including funds which have been committed and which
may be liquidated or cancelled at a later date, as a result the total
expenditure for say 31 August may be higher than that of 30 September as some
funds might have been liquidated in September. The formula should pick out
the total expenditure as at 30 September and not the maximum as you have
suggested.

Thx once again

"Ian" wrote:

> Are the values in the columns monthly expenditure or year to date?
>
> Assuming monthly expenditure you would simply need to add the monthly values
> together with something like =SUM(A2:C2).
> If each is a year to date total, then you just need to find the maximum
> value from the columns with somethign like =MAX(A2:C2). If the values are
> entered as negative numbers replace MAX with MIN.
>
> --
> Ian
> --
> "Alylia" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > I have columns of data into which entry should be made at different time
> > intervals.
> >
> > An example of the field format is given below:
> >
> > Expenditure Expenditure Expenditure
> > as at 31/8 as at 30/9 as at 31/10
> >
> >
> > The cumulative expenditures as at the given dates would have to be entered
> > under the various coulmns and I would like assistance on which
> > syntax/formula
> > I should use in a field called "Total Expenditures" to get the latest data
> > from the columns of data.
> >
> > Thx
>
>
>

Go to Tools>Macro>Macros... and enter Total_Expenditures (or another name,
without spaces) as the name, then click Create. Copy and paste all but the
first and last lines of my code (they are already there). Close the Visual
Basic window.

I notice in the message below that some of the lines have wrapped. I have
marked the beginning of each line of code with a *. Anything in between
belongs on the line before.

To run the macro, go to Tools>Macro>Macros..., highlight the macro name and
click run.

--
Ian
--
"Alylia" <[email protected]> wrote in message
news:[email protected]...
> Please can I be guided as to how I should create the macro i.e. what
> should I
> do with the syntax below.
>
> "Ian" wrote:
>
>> Try this macro. I've made some assumptions, as below
>> Assuming you have more than 1 line of monthly totals for different
>> products/departments.
>> Assuming monthly data starts in row 2
>> Assuming monthly data is in columns 1 to 12 (A to L)
>> Assuming Current total is in column 13 (M)
>>
*>> Private Sub Total_Expenditures()
*>> For r = 2 To 20 ' First row of data to last row of data
*>> If Cells(r, 1).Value = "" Then ' If data cell in column A is blank
*>> Cells(r, 13).Value = "" ' Make column M blank
*>> GoTo subend ' Go to end of macro
*>> End If
*>> For c = 1 To 12 ' For columns A to L (12 months)
*>> If Cells(r, c).Value = "" Then GoTo continue ' If column cell is blank,
>> continue with calculation
*>> Next c ' Otherwise try the next column
*>> continue:
*>> c = c - 1 ' Move back one column from the first blank
*>> Cells(r, 13).Value = Cells(r, c) ' Make colum M equal to last column
with
>> value
*>> subend:
*>> Next r ' Start on the next row of data
*>> End Sub
>>
>> --
>> Ian
>> --
>> "Alylia" <[email protected]> wrote in message
>> news:[email protected]...
>> > Thank you for your support
>> >
>> > The expenditures are year to date, but there is a potential problem
>> > with
>> > what you have suggested.
>> >
>> > The total expenditure including funds which have been committed and
>> > which
>> > may be liquidated or cancelled at a later date, as a result the total
>> > expenditure for say 31 August may be higher than that of 30 September
>> > as
>> > some
>> > funds might have been liquidated in September. The formula should pick
>> > out
>> > the total expenditure as at 30 September and not the maximum as you
>> > have
>> > suggested.
>> >
>> > Thx once again
>> >
>> > "Ian" wrote:
>> >
>> >> Are the values in the columns monthly expenditure or year to date?
>> >>
>> >> Assuming monthly expenditure you would simply need to add the monthly
>> >> values
>> >> together with something like =SUM(A2:C2).
>> >> If each is a year to date total, then you just need to find the
>> >> maximum
>> >> value from the columns with somethign like =MAX(A2:C2). If the values
>> >> are
>> >> entered as negative numbers replace MAX with MIN.
>> >>
>> >> --
>> >> Ian
>> >> --
>> >> "Alylia" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Hello,
>> >> >
>> >> > I have columns of data into which entry should be made at different
>> >> > time
>> >> > intervals.
>> >> >
>> >> > An example of the field format is given below:
>> >> >
>> >> > Expenditure Expenditure Expenditure
>> >> > as at 31/8 as at 30/9 as at 31/10
>> >> >
>> >> >
>> >> > The cumulative expenditures as at the given dates would have to be
>> >> > entered
>> >> > under the various coulmns and I would like assistance on which
>> >> > syntax/formula
>> >> > I should use in a field called "Total Expenditures" to get the
>> >> > latest
>> >> > data
>> >> > from the columns of data.
>> >> >
>> >> > Thx
>> >>
>> >>
>> >>
>>
>>
>>

Thank you for your support

The expenditures are year to date, but there is a potential problem with
what you have suggested.

The total expenditure including funds which have been committed and which
may be liquidated or cancelled at a later date, as a result the total
expenditure for say 31 August may be higher than that of 30 September as some
funds might have been liquidated in September. The formula should pick out
the total expenditure as at 30 September and not the maximum as you have
suggested.

Thx once again

"Ian" wrote:

> Are the values in the columns monthly expenditure or year to date?
>
> Assuming monthly expenditure you would simply need to add the monthly values
> together with something like =SUM(A2:C2).
> If each is a year to date total, then you just need to find the maximum
> value from the columns with somethign like =MAX(A2:C2). If the values are
> entered as negative numbers replace MAX with MIN.
>
> --
> Ian
> --
> "Alylia" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > I have columns of data into which entry should be made at different time
> > intervals.
> >
> > An example of the field format is given below:
> >
> > Expenditure Expenditure Expenditure
> > as at 31/8 as at 30/9 as at 31/10
> >
> >
> > The cumulative expenditures as at the given dates would have to be entered
> > under the various coulmns and I would like assistance on which
> > syntax/formula
> > I should use in a field called "Total Expenditures" to get the latest data
> > from the columns of data.
> >
> > Thx
>
>
>

Try this macro. I've made some assumptions, as below
Assuming you have more than 1 line of monthly totals for different
products/departments.
Assuming monthly data starts in row 2
Assuming monthly data is in columns 1 to 12 (A to L)
Assuming Current total is in column 13 (M)

Private Sub Total_Expenditures()
For r = 2 To 20 ' First row of data to last row of data
If Cells(r, 1).Value = "" Then ' If data cell in column A is blank
Cells(r, 13).Value = "" ' Make column M blank
GoTo subend ' Go to end of macro
End If
For c = 1 To 12 ' For columns A to L (12 months)
If Cells(r, c).Value = "" Then GoTo continue ' If column cell is blank,
continue with calculation
Next c ' Otherwise try the next column
continue:
c = c - 1 ' Move back one column from the first blank
Cells(r, 13).Value = Cells(r, c) ' Make colum M equal to last column with
value
subend:
Next r ' Start on the next row of data
End Sub

--
Ian
--
"Alylia" <[email protected]> wrote in message
news:[email protected]...
> Thank you for your support
>
> The expenditures are year to date, but there is a potential problem with
> what you have suggested.
>
> The total expenditure including funds which have been committed and which
> may be liquidated or cancelled at a later date, as a result the total
> expenditure for say 31 August may be higher than that of 30 September as
> some
> funds might have been liquidated in September. The formula should pick out
> the total expenditure as at 30 September and not the maximum as you have
> suggested.
>
> Thx once again
>
> "Ian" wrote:
>
>> Are the values in the columns monthly expenditure or year to date?
>>
>> Assuming monthly expenditure you would simply need to add the monthly
>> values
>> together with something like =SUM(A2:C2).
>> If each is a year to date total, then you just need to find the maximum
>> value from the columns with somethign like =MAX(A2:C2). If the values are
>> entered as negative numbers replace MAX with MIN.
>>
>> --
>> Ian
>> --
>> "Alylia" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hello,
>> >
>> > I have columns of data into which entry should be made at different
>> > time
>> > intervals.
>> >
>> > An example of the field format is given below:
>> >
>> > Expenditure Expenditure Expenditure
>> > as at 31/8 as at 30/9 as at 31/10
>> >
>> >
>> > The cumulative expenditures as at the given dates would have to be
>> > entered
>> > under the various coulmns and I would like assistance on which
>> > syntax/formula
>> > I should use in a field called "Total Expenditures" to get the latest
>> > data
>> > from the columns of data.
>> >
>> > Thx
>>
>>
>>

Are the values in the columns monthly expenditure or year to date?

Assuming monthly expenditure you would simply need to add the monthly values
together with something like =SUM(A2:C2).
If each is a year to date total, then you just need to find the maximum
value from the columns with somethign like =MAX(A2:C2). If the values are
entered as negative numbers replace MAX with MIN.

--
Ian
--
"Alylia" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have columns of data into which entry should be made at different time
> intervals.
>
> An example of the field format is given below:
>
> Expenditure Expenditure Expenditure
> as at 31/8 as at 30/9 as at 31/10
>
>
> The cumulative expenditures as at the given dates would have to be entered
> under the various coulmns and I would like assistance on which
> syntax/formula
> I should use in a field called "Total Expenditures" to get the latest data
> from the columns of data.
>
> Thx

Please can I be guided as to how I should create the macro i.e. what should I
do with the syntax below.

"Ian" wrote:

> Try this macro. I've made some assumptions, as below
> Assuming you have more than 1 line of monthly totals for different
> products/departments.
> Assuming monthly data starts in row 2
> Assuming monthly data is in columns 1 to 12 (A to L)
> Assuming Current total is in column 13 (M)
>
> Private Sub Total_Expenditures()
> For r = 2 To 20 ' First row of data to last row of data
> If Cells(r, 1).Value = "" Then ' If data cell in column A is blank
> Cells(r, 13).Value = "" ' Make column M blank
> GoTo subend ' Go to end of macro
> End If
> For c = 1 To 12 ' For columns A to L (12 months)
> If Cells(r, c).Value = "" Then GoTo continue ' If column cell is blank,
> continue with calculation
> Next c ' Otherwise try the next column
> continue:
> c = c - 1 ' Move back one column from the first blank
> Cells(r, 13).Value = Cells(r, c) ' Make colum M equal to last column with
> value
> subend:
> Next r ' Start on the next row of data
> End Sub
>
> --
> Ian
> --
> "Alylia" <[email protected]> wrote in message
> news:[email protected]...
> > Thank you for your support
> >
> > The expenditures are year to date, but there is a potential problem with
> > what you have suggested.
> >
> > The total expenditure including funds which have been committed and which
> > may be liquidated or cancelled at a later date, as a result the total
> > expenditure for say 31 August may be higher than that of 30 September as
> > some
> > funds might have been liquidated in September. The formula should pick out
> > the total expenditure as at 30 September and not the maximum as you have
> > suggested.
> >
> > Thx once again
> >
> > "Ian" wrote:
> >
> >> Are the values in the columns monthly expenditure or year to date?
> >>
> >> Assuming monthly expenditure you would simply need to add the monthly
> >> values
> >> together with something like =SUM(A2:C2).
> >> If each is a year to date total, then you just need to find the maximum
> >> value from the columns with somethign like =MAX(A2:C2). If the values are
> >> entered as negative numbers replace MAX with MIN.
> >>
> >> --
> >> Ian
> >> --
> >> "Alylia" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Hello,
> >> >
> >> > I have columns of data into which entry should be made at different
> >> > time
> >> > intervals.
> >> >
> >> > An example of the field format is given below:
> >> >
> >> > Expenditure Expenditure Expenditure
> >> > as at 31/8 as at 30/9 as at 31/10
> >> >
> >> >
> >> > The cumulative expenditures as at the given dates would have to be
> >> > entered
> >> > under the various coulmns and I would like assistance on which
> >> > syntax/formula
> >> > I should use in a field called "Total Expenditures" to get the latest
> >> > data
> >> > from the columns of data.
> >> >
> >> > Thx
> >>
> >>
> >>
>
>
>

Go to Tools>Macro>Macros... and enter Total_Expenditures (or another name,
without spaces) as the name, then click Create. Copy and paste all but the
first and last lines of my code (they are already there). Close the Visual
Basic window.

I notice in the message below that some of the lines have wrapped. I have
marked the beginning of each line of code with a *. Anything in between
belongs on the line before.

To run the macro, go to Tools>Macro>Macros..., highlight the macro name and
click run.

--
Ian
--
"Alylia" <[email protected]> wrote in message
news:[email protected]...
> Please can I be guided as to how I should create the macro i.e. what
> should I
> do with the syntax below.
>
> "Ian" wrote:
>
>> Try this macro. I've made some assumptions, as below
>> Assuming you have more than 1 line of monthly totals for different
>> products/departments.
>> Assuming monthly data starts in row 2
>> Assuming monthly data is in columns 1 to 12 (A to L)
>> Assuming Current total is in column 13 (M)
>>
*>> Private Sub Total_Expenditures()
*>> For r = 2 To 20 ' First row of data to last row of data
*>> If Cells(r, 1).Value = "" Then ' If data cell in column A is blank
*>> Cells(r, 13).Value = "" ' Make column M blank
*>> GoTo subend ' Go to end of macro
*>> End If
*>> For c = 1 To 12 ' For columns A to L (12 months)
*>> If Cells(r, c).Value = "" Then GoTo continue ' If column cell is blank,
>> continue with calculation
*>> Next c ' Otherwise try the next column
*>> continue:
*>> c = c - 1 ' Move back one column from the first blank
*>> Cells(r, 13).Value = Cells(r, c) ' Make colum M equal to last column
with
>> value
*>> subend:
*>> Next r ' Start on the next row of data
*>> End Sub
>>
>> --
>> Ian
>> --
>> "Alylia" <[email protected]> wrote in message
>> news:[email protected]...
>> > Thank you for your support
>> >
>> > The expenditures are year to date, but there is a potential problem
>> > with
>> > what you have suggested.
>> >
>> > The total expenditure including funds which have been committed and
>> > which
>> > may be liquidated or cancelled at a later date, as a result the total
>> > expenditure for say 31 August may be higher than that of 30 September
>> > as
>> > some
>> > funds might have been liquidated in September. The formula should pick
>> > out
>> > the total expenditure as at 30 September and not the maximum as you
>> > have
>> > suggested.
>> >
>> > Thx once again
>> >
>> > "Ian" wrote:
>> >
>> >> Are the values in the columns monthly expenditure or year to date?
>> >>
>> >> Assuming monthly expenditure you would simply need to add the monthly
>> >> values
>> >> together with something like =SUM(A2:C2).
>> >> If each is a year to date total, then you just need to find the
>> >> maximum
>> >> value from the columns with somethign like =MAX(A2:C2). If the values
>> >> are
>> >> entered as negative numbers replace MAX with MIN.
>> >>
>> >> --
>> >> Ian
>> >> --
>> >> "Alylia" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Hello,
>> >> >
>> >> > I have columns of data into which entry should be made at different
>> >> > time
>> >> > intervals.
>> >> >
>> >> > An example of the field format is given below:
>> >> >
>> >> > Expenditure Expenditure Expenditure
>> >> > as at 31/8 as at 30/9 as at 31/10
>> >> >
>> >> >
>> >> > The cumulative expenditures as at the given dates would have to be
>> >> > entered
>> >> > under the various coulmns and I would like assistance on which
>> >> > syntax/formula
>> >> > I should use in a field called "Total Expenditures" to get the
>> >> > latest
>> >> > data
>> >> > from the columns of data.
>> >> >
>> >> > Thx
>> >>
>> >>
>> >>
>>
>>
>>

Hello,

I have columns of data into which entry should be made at different time
intervals.

An example of the field format is given below:

Expenditure Expenditure Expenditure
as at 31/8 as at 30/9 as at 31/10


The cumulative expenditures as at the given dates would have to be entered
under the various coulmns and I would like assistance on which syntax/formula
I should use in a field called "Total Expenditures" to get the latest data
from the columns of data.

Thx