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" wrote in message
...
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" wrote in message
...
ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc["&adj&"])"

--
Kind regards,

Niek Otten


"mb" wrote in message
...
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" wrote in message
...
ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc["&adj&"])"

--
Kind regards,

Niek Otten


"mb" wrote in message
...
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" wrote in message
...
Receiving Expected end of statement error.

"Niek Otten" wrote in message
...
ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc["&adj&"])"

--
Kind regards,

Niek Otten


"mb" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Receiving Expected end of statement error.

"Niek Otten" wrote in message
...
ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc["&adj&"])"

--
Kind regards,

Niek Otten


"mb" wrote in message
...
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" wrote in message
...
< I don't know what causes the "Expecting...." error

I think Dave has got it right there

--
Kind regards,

Niek Otten

"Niek Otten" wrote in message
...
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" wrote in message
...
Receiving Expected end of statement error.

"Niek Otten" wrote in message
...
ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc["&adj&"])"

--
Kind regards,

Niek Otten


"mb" wrote in message
...
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" wrote in message
...
< I don't know what causes the "Expecting...." error

I think Dave has got it right there

--
Kind regards,

Niek Otten

"Niek Otten" wrote in message
...
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" wrote in message
...
Receiving Expected end of statement error.

"Niek Otten" wrote in message
...
ActiveCell.FormulaR1C1 = "=SUM(rc[4]:rc["&adj&"])"

--
Kind regards,

Niek Otten


"mb" wrote in message
...
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