ActiveCell.FormulaR1C1 Sum Function

M

mb

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
 
M

mb

Receiving Expected end of statement error.

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

--
Kind regards,

Niek Otten


mb said:
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
Click to expand...
Click to expand...
 
D

Dave Peterson

Try adding some spaces:

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


Receiving Expected end of statement error.

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

--
Kind regards,

Niek Otten


mb said:
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
Click to expand...
Click to expand...
Click to expand...
 
N

Niek Otten

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 said:
Receiving Expected end of statement error.

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

--
Kind regards,

Niek Otten


mb said:
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
Click to expand...
Click to expand...
Click to expand...
 
N

Niek Otten

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

I think Dave has got it right there

--
Kind regards,

Niek Otten

Niek Otten said:
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 said:
Receiving Expected end of statement error.

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

--
Kind regards,

Niek Otten


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
Click to expand...
Click to expand...
Click to expand...
 
M

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 said:
< I don't know what causes the "Expecting...." error>

I think Dave has got it right there

--
Kind regards,

Niek Otten

Niek Otten said:
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 said:
Receiving Expected end of statement error.

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

--
Kind regards,

Niek Otten


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
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

& 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.
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 said:
< I don't know what causes the "Expecting...." error>

I think Dave has got it right there

--
Kind regards,

Niek Otten

Niek Otten said:
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

Receiving Expected end of statement error.

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

--
Kind regards,

Niek Otten


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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pass a variable back to the host script? 2
Geometric progression in VBA 10
how to use Solver in Excel VBA 1
Data samples 3
Help with my VBA and formula 8
Macro: Copying to all possible rows 2
Creating Pivot Table with Macro using Multiple Sheets 0
Modify range in VBA 9

Top