ActiveCell.FormulaR1C1 Sum Function

Seem to be having a problem with this line:

ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc[adj])"

Object Defined Error (#1004)

The basic premise is there are dates loaded into the 4 row in each of the
columns listed in the array. The Pull_Fwd function is determining if the
date range is less than the number of days entered for the pull forward, and
then only adding those quantities for each item listed (starting at D5).

Entire Script:
Public Sub Pull_Forward()
num = InputBox("Enter the range (in calendar days) you wish to include
in the Pull Forward Calculation: ")
strWeek = Module1.current_date(num)
MsgBox (strWeek)
Call Module1.Pull_Fwd(strWeek)
End Sub

Private Function current_date(num)
today = Date
today = Format(today, "mm/dd")
next_week = DateAdd("d", num, today)
next_week = Format(next_week, "mm/dd")
current_date = next_week
End Function

Private Function Pull_Fwd(strWeek) As Date
Dim adj As Integer
gcolumn = Array("H4", "I4", "J4", "K4", "L4", "M4", "N4", "O4", "P4",
"Q4", "R4", "S4", "T4", "U4", "V4", "W4", "X4", "Y4", "Z4")

count_column = 0
For i = LBound(gcolumn) To UBound(gcolumn)
sheet_date = Range(gcolumn(i)).Text
'MsgBox (sheet_date)
If sheet_date < strWeek Then
count_column = count_column + 1
End If
Next i
count_column = count_column - 1
'MsgBox (count_column)
adj = 4 + count_column
MsgBox (adj)
Range("d5").Select
ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc[adj])"
End Function

Thanks,
mb

ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc["&adj&"])"

--
Kind regards,

Niek Otten


"mb" <[email protected]> wrote in message
news:[email protected]...
> Seem to be having a problem with this line:
>
> ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc[adj])"
>
> Object Defined Error (#1004)
>
> The basic premise is there are dates loaded into the 4 row in each of the
> columns listed in the array. The Pull_Fwd function is determining if the
> date range is less than the number of days entered for the pull forward,
> and
> then only adding those quantities for each item listed (starting at D5).
>
> Entire Script:
> Public Sub Pull_Forward()
> num = InputBox("Enter the range (in calendar days) you wish to include
> in the Pull Forward Calculation: ")
> strWeek = Module1.current_date(num)
> MsgBox (strWeek)
> Call Module1.Pull_Fwd(strWeek)
> End Sub
>
> Private Function current_date(num)
> today = Date
> today = Format(today, "mm/dd")
> next_week = DateAdd("d", num, today)
> next_week = Format(next_week, "mm/dd")
> current_date = next_week
> End Function
>
> Private Function Pull_Fwd(strWeek) As Date
> Dim adj As Integer
> gcolumn = Array("H4", "I4", "J4", "K4", "L4", "M4", "N4", "O4", "P4",
> "Q4", "R4", "S4", "T4", "U4", "V4", "W4", "X4", "Y4", "Z4")
>
> count_column = 0
> For i = LBound(gcolumn) To UBound(gcolumn)
> sheet_date = Range(gcolumn(i)).Text
> 'MsgBox (sheet_date)
> If sheet_date < strWeek Then
> count_column = count_column + 1
> End If
> Next i
> count_column = count_column - 1
> 'MsgBox (count_column)
> adj = 4 + count_column
> MsgBox (adj)
> Range("d5").Select
> ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc[adj])"
> End Function
>
> Thanks,
> mb
>
>

Receiving Expected end of statement error.

"Niek Otten" <[email protected]> wrote in message
news:#[email protected]...
> ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc["&adj&"])"
>
> --
> Kind regards,
>
> Niek Otten
>
>
> "mb" <[email protected]> wrote in message
> news:[email protected]...
> > Seem to be having a problem with this line:
> >
> > ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc[adj])"
> >
> > Object Defined Error (#1004)
> >
> > The basic premise is there are dates loaded into the 4 row in each of
the
> > columns listed in the array. The Pull_Fwd function is determining if
the
> > date range is less than the number of days entered for the pull forward,
> > and
> > then only adding those quantities for each item listed (starting at D5).
> >
> > Entire Script:
> > Public Sub Pull_Forward()
> > num = InputBox("Enter the range (in calendar days) you wish to
include
> > in the Pull Forward Calculation: ")
> > strWeek = Module1.current_date(num)
> > MsgBox (strWeek)
> > Call Module1.Pull_Fwd(strWeek)
> > End Sub
> >
> > Private Function current_date(num)
> > today = Date
> > today = Format(today, "mm/dd")
> > next_week = DateAdd("d", num, today)
> > next_week = Format(next_week, "mm/dd")
> > current_date = next_week
> > End Function
> >
> > Private Function Pull_Fwd(strWeek) As Date
> > Dim adj As Integer
> > gcolumn = Array("H4", "I4", "J4", "K4", "L4", "M4", "N4", "O4", "P4",
> > "Q4", "R4", "S4", "T4", "U4", "V4", "W4", "X4", "Y4", "Z4")
> >
> > count_column = 0
> > For i = LBound(gcolumn) To UBound(gcolumn)
> > sheet_date = Range(gcolumn(i)).Text
> > 'MsgBox (sheet_date)
> > If sheet_date < strWeek Then
> > count_column = count_column + 1
> > End If
> > Next i
> > count_column = count_column - 1
> > 'MsgBox (count_column)
> > adj = 4 + count_column
> > MsgBox (adj)
> > Range("d5").Select
> > ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc[adj])"
> > End Function
> >
> > Thanks,
> > mb
> >
> >
>
>

Try adding some spaces:

ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc[" & adj & "])"



mb wrote:
>
> Receiving Expected end of statement error.
>
> "Niek Otten" <[email protected]> wrote in message
> news:#[email protected]...
> > ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc["&adj&"])"
> >
> > --
> > Kind regards,
> >
> > Niek Otten
> >
> >
> > "mb" <[email protected]> wrote in message
> > news:[email protected]...
> > > Seem to be having a problem with this line:
> > >
> > > ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc[adj])"
> > >
> > > Object Defined Error (#1004)
> > >
> > > The basic premise is there are dates loaded into the 4 row in each of
> the
> > > columns listed in the array. The Pull_Fwd function is determining if
> the
> > > date range is less than the number of days entered for the pull forward,
> > > and
> > > then only adding those quantities for each item listed (starting at D5).
> > >
> > > Entire Script:
> > > Public Sub Pull_Forward()
> > > num = InputBox("Enter the range (in calendar days) you wish to
> include
> > > in the Pull Forward Calculation: ")
> > > strWeek = Module1.current_date(num)
> > > MsgBox (strWeek)
> > > Call Module1.Pull_Fwd(strWeek)
> > > End Sub
> > >
> > > Private Function current_date(num)
> > > today = Date
> > > today = Format(today, "mm/dd")
> > > next_week = DateAdd("d", num, today)
> > > next_week = Format(next_week, "mm/dd")
> > > current_date = next_week
> > > End Function
> > >
> > > Private Function Pull_Fwd(strWeek) As Date
> > > Dim adj As Integer
> > > gcolumn = Array("H4", "I4", "J4", "K4", "L4", "M4", "N4", "O4", "P4",
> > > "Q4", "R4", "S4", "T4", "U4", "V4", "W4", "X4", "Y4", "Z4")
> > >
> > > count_column = 0
> > > For i = LBound(gcolumn) To UBound(gcolumn)
> > > sheet_date = Range(gcolumn(i)).Text
> > > 'MsgBox (sheet_date)
> > > If sheet_date < strWeek Then
> > > count_column = count_column + 1
> > > End If
> > > Next i
> > > count_column = count_column - 1
> > > 'MsgBox (count_column)
> > > adj = 4 + count_column
> > > MsgBox (adj)
> > > Range("d5").Select
> > > ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc[adj])"
> > > End Function
> > >
> > > Thanks,
> > > mb
> > >
> > >
> >
> >

--

Dave Peterson

You may agree that understanding someone else's code is always difficult.
But there are a few things I think need attention.

1. You don't DIM your variables. To be honest, that in itself for many pros
is sufficient to not look at the rest of the code at all.
2. You try to select and change ranges in worksheets from within a function.
That is impossible. Functions can only replace their call with a result;
they cannot change anything else.
3. I don't know what causes the "Expecting...." error. Something else may be
wrong in the surrounding code, but certainly =SUM(rc[4]:rc[adj]) does not
evaluate to a valid formula; adj needs to be outside of the quotes (if my
understanding that it is a column number is correct).

If you post again (in this thread please), please give the values of the
input cells and the values form your message boxes.

--
Kind regards,

Niek Otten

"mb" <[email protected]> wrote in message
news:[email protected]...
> Receiving Expected end of statement error.
>
> "Niek Otten" <[email protected]> wrote in message
> news:#[email protected]...
>> ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc["&adj&"])"
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>>
>>
>> "mb" <[email protected]> wrote in message
>> news:[email protected]...
>> > Seem to be having a problem with this line:
>> >
>> > ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc[adj])"
>> >
>> > Object Defined Error (#1004)
>> >
>> > The basic premise is there are dates loaded into the 4 row in each of
> the
>> > columns listed in the array. The Pull_Fwd function is determining if
> the
>> > date range is less than the number of days entered for the pull
>> > forward,
>> > and
>> > then only adding those quantities for each item listed (starting at
>> > D5).
>> >
>> > Entire Script:
>> > Public Sub Pull_Forward()
>> > num = InputBox("Enter the range (in calendar days) you wish to
> include
>> > in the Pull Forward Calculation: ")
>> > strWeek = Module1.current_date(num)
>> > MsgBox (strWeek)
>> > Call Module1.Pull_Fwd(strWeek)
>> > End Sub
>> >
>> > Private Function current_date(num)
>> > today = Date
>> > today = Format(today, "mm/dd")
>> > next_week = DateAdd("d", num, today)
>> > next_week = Format(next_week, "mm/dd")
>> > current_date = next_week
>> > End Function
>> >
>> > Private Function Pull_Fwd(strWeek) As Date
>> > Dim adj As Integer
>> > gcolumn = Array("H4", "I4", "J4", "K4", "L4", "M4", "N4", "O4",
>> > "P4",
>> > "Q4", "R4", "S4", "T4", "U4", "V4", "W4", "X4", "Y4", "Z4")
>> >
>> > count_column = 0
>> > For i = LBound(gcolumn) To UBound(gcolumn)
>> > sheet_date = Range(gcolumn(i)).Text
>> > 'MsgBox (sheet_date)
>> > If sheet_date < strWeek Then
>> > count_column = count_column + 1
>> > End If
>> > Next i
>> > count_column = count_column - 1
>> > 'MsgBox (count_column)
>> > adj = 4 + count_column
>> > MsgBox (adj)
>> > Range("d5").Select
>> > ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc[adj])"
>> > End Function
>> >
>> > Thanks,
>> > mb
>> >
>> >
>>
>>
>
>

< I don't know what causes the "Expecting...." error>

I think Dave has got it right there

--
Kind regards,

Niek Otten

"Niek Otten" <[email protected]> wrote in message
news:[email protected]...
> You may agree that understanding someone else's code is always difficult.
> But there are a few things I think need attention.
>
> 1. You don't DIM your variables. To be honest, that in itself for many
> pros is sufficient to not look at the rest of the code at all.
> 2. You try to select and change ranges in worksheets from within a
> function. That is impossible. Functions can only replace their call with a
> result; they cannot change anything else.
> 3. I don't know what causes the "Expecting...." error. Something else may
> be wrong in the surrounding code, but certainly =SUM(rc[4]:rc[adj]) does
> not evaluate to a valid formula; adj needs to be outside of the quotes (if
> my understanding that it is a column number is correct).
>
> If you post again (in this thread please), please give the values of the
> input cells and the values form your message boxes.
>
> --
> Kind regards,
>
> Niek Otten
>
> "mb" <[email protected]> wrote in message
> news:[email protected]...
>> Receiving Expected end of statement error.
>>
>> "Niek Otten" <[email protected]> wrote in message
>> news:#[email protected]...
>>> ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc["&adj&"])"
>>>
>>> --
>>> Kind regards,
>>>
>>> Niek Otten
>>>
>>>
>>> "mb" <[email protected]> wrote in message
>>> news:[email protected]...
>>> > Seem to be having a problem with this line:
>>> >
>>> > ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc[adj])"
>>> >
>>> > Object Defined Error (#1004)
>>> >
>>> > The basic premise is there are dates loaded into the 4 row in each of
>> the
>>> > columns listed in the array. The Pull_Fwd function is determining if
>> the
>>> > date range is less than the number of days entered for the pull
>>> > forward,
>>> > and
>>> > then only adding those quantities for each item listed (starting at
>>> > D5).
>>> >
>>> > Entire Script:
>>> > Public Sub Pull_Forward()
>>> > num = InputBox("Enter the range (in calendar days) you wish to
>> include
>>> > in the Pull Forward Calculation: ")
>>> > strWeek = Module1.current_date(num)
>>> > MsgBox (strWeek)
>>> > Call Module1.Pull_Fwd(strWeek)
>>> > End Sub
>>> >
>>> > Private Function current_date(num)
>>> > today = Date
>>> > today = Format(today, "mm/dd")
>>> > next_week = DateAdd("d", num, today)
>>> > next_week = Format(next_week, "mm/dd")
>>> > current_date = next_week
>>> > End Function
>>> >
>>> > Private Function Pull_Fwd(strWeek) As Date
>>> > Dim adj As Integer
>>> > gcolumn = Array("H4", "I4", "J4", "K4", "L4", "M4", "N4", "O4",
>>> > "P4",
>>> > "Q4", "R4", "S4", "T4", "U4", "V4", "W4", "X4", "Y4", "Z4")
>>> >
>>> > count_column = 0
>>> > For i = LBound(gcolumn) To UBound(gcolumn)
>>> > sheet_date = Range(gcolumn(i)).Text
>>> > 'MsgBox (sheet_date)
>>> > If sheet_date < strWeek Then
>>> > count_column = count_column + 1
>>> > End If
>>> > Next i
>>> > count_column = count_column - 1
>>> > 'MsgBox (count_column)
>>> > adj = 4 + count_column
>>> > MsgBox (adj)
>>> > Range("d5").Select
>>> > ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc[adj])"
>>> > End Function
>>> >
>>> > Thanks,
>>> > mb
>>> >
>>> >
>>>
>>>
>>
>>
>
>

Thanks to both of you (Niek and Dave) for trying to help me on this. It is
working now, after a few changes. The primary is I needed to use "+"
instead of "&", must be my version of Excel being used.

Thanks again,
mb


"Niek Otten" <[email protected]> wrote in message
news:[email protected]...
> < I don't know what causes the "Expecting...." error>
>
> I think Dave has got it right there
>
> --
> Kind regards,
>
> Niek Otten
>
> "Niek Otten" <[email protected]> wrote in message
> news:[email protected]...
> > You may agree that understanding someone else's code is always
difficult.
> > But there are a few things I think need attention.
> >
> > 1. You don't DIM your variables. To be honest, that in itself for many
> > pros is sufficient to not look at the rest of the code at all.
> > 2. You try to select and change ranges in worksheets from within a
> > function. That is impossible. Functions can only replace their call with
a
> > result; they cannot change anything else.
> > 3. I don't know what causes the "Expecting...." error. Something else
may
> > be wrong in the surrounding code, but certainly =SUM(rc[4]:rc[adj]) does
> > not evaluate to a valid formula; adj needs to be outside of the quotes
(if
> > my understanding that it is a column number is correct).
> >
> > If you post again (in this thread please), please give the values of the
> > input cells and the values form your message boxes.
> >
> > --
> > Kind regards,
> >
> > Niek Otten
> >
> > "mb" <[email protected]> wrote in message
> > news:[email protected]...
> >> Receiving Expected end of statement error.
> >>
> >> "Niek Otten" <[email protected]> wrote in message
> >> news:#[email protected]...
> >>> ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc["&adj&"])"
> >>>
> >>> --
> >>> Kind regards,
> >>>
> >>> Niek Otten
> >>>
> >>>
> >>> "mb" <[email protected]> wrote in message
> >>> news:[email protected]...
> >>> > Seem to be having a problem with this line:
> >>> >
> >>> > ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc[adj])"
> >>> >
> >>> > Object Defined Error (#1004)
> >>> >
> >>> > The basic premise is there are dates loaded into the 4 row in each
of
> >> the
> >>> > columns listed in the array. The Pull_Fwd function is determining
if
> >> the
> >>> > date range is less than the number of days entered for the pull
> >>> > forward,
> >>> > and
> >>> > then only adding those quantities for each item listed (starting at
> >>> > D5).
> >>> >
> >>> > Entire Script:
> >>> > Public Sub Pull_Forward()
> >>> > num = InputBox("Enter the range (in calendar days) you wish to
> >> include
> >>> > in the Pull Forward Calculation: ")
> >>> > strWeek = Module1.current_date(num)
> >>> > MsgBox (strWeek)
> >>> > Call Module1.Pull_Fwd(strWeek)
> >>> > End Sub
> >>> >
> >>> > Private Function current_date(num)
> >>> > today = Date
> >>> > today = Format(today, "mm/dd")
> >>> > next_week = DateAdd("d", num, today)
> >>> > next_week = Format(next_week, "mm/dd")
> >>> > current_date = next_week
> >>> > End Function
> >>> >
> >>> > Private Function Pull_Fwd(strWeek) As Date
> >>> > Dim adj As Integer
> >>> > gcolumn = Array("H4", "I4", "J4", "K4", "L4", "M4", "N4", "O4",
> >>> > "P4",
> >>> > "Q4", "R4", "S4", "T4", "U4", "V4", "W4", "X4", "Y4", "Z4")
> >>> >
> >>> > count_column = 0
> >>> > For i = LBound(gcolumn) To UBound(gcolumn)
> >>> > sheet_date = Range(gcolumn(i)).Text
> >>> > 'MsgBox (sheet_date)
> >>> > If sheet_date < strWeek Then
> >>> > count_column = count_column + 1
> >>> > End If
> >>> > Next i
> >>> > count_column = count_column - 1
> >>> > 'MsgBox (count_column)
> >>> > adj = 4 + count_column
> >>> > MsgBox (adj)
> >>> > Range("d5").Select
> >>> > ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc[adj])"
> >>> > End Function
> >>> >
> >>> > Thanks,
> >>> > mb
> >>> >
> >>> >
> >>>
> >>>
> >>
> >>
> >
> >
>
>

& is usually used to concatenate text (what you're doing)
+ is usually used to add numbers.

If you insert the spaces, I bet that the & (ampersand) works nicely.

mb wrote:
>
> Thanks to both of you (Niek and Dave) for trying to help me on this. It is
> working now, after a few changes. The primary is I needed to use "+"
> instead of "&", must be my version of Excel being used.
>
> Thanks again,
> mb
>
> "Niek Otten" <[email protected]> wrote in message
> news:[email protected]...
> > < I don't know what causes the "Expecting...." error>
> >
> > I think Dave has got it right there
> >
> > --
> > Kind regards,
> >
> > Niek Otten
> >
> > "Niek Otten" <[email protected]> wrote in message
> > news:[email protected]...
> > > You may agree that understanding someone else's code is always
> difficult.
> > > But there are a few things I think need attention.
> > >
> > > 1. You don't DIM your variables. To be honest, that in itself for many
> > > pros is sufficient to not look at the rest of the code at all.
> > > 2. You try to select and change ranges in worksheets from within a
> > > function. That is impossible. Functions can only replace their call with
> a
> > > result; they cannot change anything else.
> > > 3. I don't know what causes the "Expecting...." error. Something else
> may
> > > be wrong in the surrounding code, but certainly =SUM(rc[4]:rc[adj]) does
> > > not evaluate to a valid formula; adj needs to be outside of the quotes
> (if
> > > my understanding that it is a column number is correct).
> > >
> > > If you post again (in this thread please), please give the values of the
> > > input cells and the values form your message boxes.
> > >
> > > --
> > > Kind regards,
> > >
> > > Niek Otten
> > >
> > > "mb" <[email protected]> wrote in message
> > > news:[email protected]...
> > >> Receiving Expected end of statement error.
> > >>
> > >> "Niek Otten" <[email protected]> wrote in message
> > >> news:#[email protected]...
> > >>> ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc["&adj&"])"
> > >>>
> > >>> --
> > >>> Kind regards,
> > >>>
> > >>> Niek Otten
> > >>>
> > >>>
> > >>> "mb" <[email protected]> wrote in message
> > >>> news:[email protected]...
> > >>> > Seem to be having a problem with this line:
> > >>> >
> > >>> > ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc[adj])"
> > >>> >
> > >>> > Object Defined Error (#1004)
> > >>> >
> > >>> > The basic premise is there are dates loaded into the 4 row in each
> of
> > >> the
> > >>> > columns listed in the array. The Pull_Fwd function is determining
> if
> > >> the
> > >>> > date range is less than the number of days entered for the pull
> > >>> > forward,
> > >>> > and
> > >>> > then only adding those quantities for each item listed (starting at
> > >>> > D5).
> > >>> >
> > >>> > Entire Script:
> > >>> > Public Sub Pull_Forward()
> > >>> > num = InputBox("Enter the range (in calendar days) you wish to
> > >> include
> > >>> > in the Pull Forward Calculation: ")
> > >>> > strWeek = Module1.current_date(num)
> > >>> > MsgBox (strWeek)
> > >>> > Call Module1.Pull_Fwd(strWeek)
> > >>> > End Sub
> > >>> >
> > >>> > Private Function current_date(num)
> > >>> > today = Date
> > >>> > today = Format(today, "mm/dd")
> > >>> > next_week = DateAdd("d", num, today)
> > >>> > next_week = Format(next_week, "mm/dd")
> > >>> > current_date = next_week
> > >>> > End Function
> > >>> >
> > >>> > Private Function Pull_Fwd(strWeek) As Date
> > >>> > Dim adj As Integer
> > >>> > gcolumn = Array("H4", "I4", "J4", "K4", "L4", "M4", "N4", "O4",
> > >>> > "P4",
> > >>> > "Q4", "R4", "S4", "T4", "U4", "V4", "W4", "X4", "Y4", "Z4")
> > >>> >
> > >>> > count_column = 0
> > >>> > For i = LBound(gcolumn) To UBound(gcolumn)
> > >>> > sheet_date = Range(gcolumn(i)).Text
> > >>> > 'MsgBox (sheet_date)
> > >>> > If sheet_date < strWeek Then
> > >>> > count_column = count_column + 1
> > >>> > End If
> > >>> > Next i
> > >>> > count_column = count_column - 1
> > >>> > 'MsgBox (count_column)
> > >>> > adj = 4 + count_column
> > >>> > MsgBox (adj)
> > >>> > Range("d5").Select
> > >>> > ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc[adj])"
> > >>> > End Function
> > >>> >
> > >>> > Thanks,
> > >>> > mb
> > >>> >
> > >>> >
> > >>>
> > >>>
> > >>
> > >>
> > >
> > >
> >
> >

--

Dave Peterson