Giving Cells a Formula
 

Hey folks,

(Excel Noob here)

I need to give certain cells a formula through code. I need it to grab the
formulas from another Excel spreadsheet's cells. I think I'm pretty close
here, but the following errors out. Do you see what's wrong?

For i = colStart To colEnd
ActiveWorkbook.Worksheets("MyWorksheet").Cells(i, rownum).Formula = "='"
& otherExcelFilePath & "'!OrigSheet:$A" & CStr(i)
Next i

So I want the cells in row rownum from cols colStart to colEnd to contain
the formulas in the other Excel spreadsheet. The other Excel Spreadsheet
should use it's worksheet OrigSheet, row A, cols colStart to colEnd.

Thx!

Giving Cells a Formula
 

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Smith" wrote in message
...
Hey folks,

(Excel Noob here)

I need to give certain cells a formula through code. I need it to grab
the
formulas from another Excel spreadsheet's cells. I think I'm pretty close
here, but the following errors out. Do you see what's wrong?

For i = colStart To colEnd
ActiveWorkbook.Worksheets("MyWorksheet").Cells(i, rownum).Formula =
"='"
& otherExcelFilePath & "'!OrigSheet:$A" & CStr(i)
Next i

So I want the cells in row rownum from cols colStart to colEnd to contain
the formulas in the other Excel spreadsheet. The other Excel Spreadsheet
should use it's worksheet OrigSheet, row A, cols colStart to colEnd.

Thx!

Giving Cells a Formula
 

John,

Not tested, but try

For i = colStart To colEnd
ActiveWorkbook.Worksheets("MyWorksheet").Cells(i, rownum).Formula =
"='[" & _
otherExcelFilePath & "]OrigSheet'!$A" & CStr(i)
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Smith" wrote in message
...
Hey folks,

(Excel Noob here)

I need to give certain cells a formula through code. I need it to grab
the
formulas from another Excel spreadsheet's cells. I think I'm pretty close
here, but the following errors out. Do you see what's wrong?

For i = colStart To colEnd
ActiveWorkbook.Worksheets("MyWorksheet").Cells(i, rownum).Formula =
"='"
& otherExcelFilePath & "'!OrigSheet:$A" & CStr(i)
Next i

So I want the cells in row rownum from cols colStart to colEnd to contain
the formulas in the other Excel spreadsheet. The other Excel Spreadsheet
should use it's worksheet OrigSheet, row A, cols colStart to colEnd.

Thx!

Giving Cells a Formula
 

Hi...Thx.

I tried your suggestion, but I get the error:
"You cannot change part of an array."

So, instead I tried using what you wrote like this:
ActiveWorkbook.Worksheets("MyWorksheet").Range("My Range").Rows(rowcount).For
mulaArray = "='[" & thePathname & "]Reporting'!$A4"

but I get the error:
"Unable to set the FormulaArray property of the Range class"




"Bob Phillips" wrote in message
...
John,

Not tested, but try

For i = colStart To colEnd
ActiveWorkbook.Worksheets("MyWorksheet").Cells(i, rownum).Formula =
"='[" & _
otherExcelFilePath & "]OrigSheet'!$A" & CStr(i)
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Smith" wrote in message
...
Hey folks,

(Excel Noob here)

I need to give certain cells a formula through code. I need it to grab
the
formulas from another Excel spreadsheet's cells. I think I'm pretty
close
here, but the following errors out. Do you see what's wrong?

For i = colStart To colEnd
ActiveWorkbook.Worksheets("MyWorksheet").Cells(i, rownum).Formula =
"='"
& otherExcelFilePath & "'!OrigSheet:$A" & CStr(i)
Next i

So I want the cells in row rownum from cols colStart to colEnd to
contain
the formulas in the other Excel spreadsheet. The other Excel
Spreadsheet
should use it's worksheet OrigSheet, row A, cols colStart to colEnd.

Thx!

Giving Cells a Formula
 

Sorry John,

try this instead

For i = colStart To colEnd
iPos = InStrRev(otherExcelFilePath, "\")
otherExcelFilePath = "='" & Left(otherExcelFilePath, iPos - 2) & _
"[" & Right(otherExcelFilePath, Len(otherExcelFilePath) - iPos) &
"]"
ActiveWorkbook.Worksheets("MyWorksheet").Cells(i, rownum).Formula = _
otherExcelFilePath & "OrigSheet'!$A" & CStr(i)
Next i

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Smith" wrote in message
...
Hi...Thx.

I tried your suggestion, but I get the error:
"You cannot change part of an array."

So, instead I tried using what you wrote like this:

ActiveWorkbook.Worksheets("MyWorksheet").Range("My Range").Rows(rowcount).For
mulaArray = "='[" & thePathname & "]Reporting'!$A4"

but I get the error:
"Unable to set the FormulaArray property of the Range class"




"Bob Phillips" wrote in message
...
John,

Not tested, but try

For i = colStart To colEnd
ActiveWorkbook.Worksheets("MyWorksheet").Cells(i, rownum).Formula =
"='[" & _
otherExcelFilePath & "]OrigSheet'!$A" & CStr(i)
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Smith" wrote in message
...
Hey folks,

(Excel Noob here)

I need to give certain cells a formula through code. I need it to
grab
the
formulas from another Excel spreadsheet's cells. I think I'm pretty
close
here, but the following errors out. Do you see what's wrong?

For i = colStart To colEnd
ActiveWorkbook.Worksheets("MyWorksheet").Cells(i, rownum).Formula
=
"='"
& otherExcelFilePath & "'!OrigSheet:$A" & CStr(i)
Next i

So I want the cells in row rownum from cols colStart to colEnd to
contain
the formulas in the other Excel spreadsheet. The other Excel
Spreadsheet
should use it's worksheet OrigSheet, row A, cols colStart to colEnd.

Thx!

Giving Cells a Formula
 

That was very helpful. Thanks alot. Just had to get rid of the minus 2
though :)

"Bob Phillips" wrote in message
...
Sorry John,

try this instead

For i = colStart To colEnd
iPos = InStrRev(otherExcelFilePath, "\")
otherExcelFilePath = "='" & Left(otherExcelFilePath, iPos - 2) & _
"[" & Right(otherExcelFilePath, Len(otherExcelFilePath) - iPos) &
"]"
ActiveWorkbook.Worksheets("MyWorksheet").Cells(i, rownum).Formula = _
otherExcelFilePath & "OrigSheet'!$A" & CStr(i)
Next i

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Smith" wrote in message
...
Hi...Thx.

I tried your suggestion, but I get the error:
"You cannot change part of an array."

So, instead I tried using what you wrote like this:


ActiveWorkbook.Worksheets("MyWorksheet").Range("My Range").Rows(rowcount).For
mulaArray = "='[" & thePathname & "]Reporting'!$A4"

but I get the error:
"Unable to set the FormulaArray property of the Range class"




"Bob Phillips" wrote in message
...
John,

Not tested, but try

For i = colStart To colEnd
ActiveWorkbook.Worksheets("MyWorksheet").Cells(i, rownum).Formula
=
"='[" & _
otherExcelFilePath & "]OrigSheet'!$A" & CStr(i)
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Smith" wrote in message
...
Hey folks,

(Excel Noob here)

I need to give certain cells a formula through code. I need it to
grab
the
formulas from another Excel spreadsheet's cells. I think I'm pretty
close
here, but the following errors out. Do you see what's wrong?

For i = colStart To colEnd
ActiveWorkbook.Worksheets("MyWorksheet").Cells(i,
rownum).Formula
=
"='"
& otherExcelFilePath & "'!OrigSheet:$A" & CStr(i)
Next i

So I want the cells in row rownum from cols colStart to colEnd to
contain
the formulas in the other Excel spreadsheet. The other Excel
Spreadsheet
should use it's worksheet OrigSheet, row A, cols colStart to colEnd.

Thx!

Giving Cells a Formula
 

That's odd. I tested that one, and that was where I got the -2 from :-).

Aah well!

Bob


"John Smith" wrote in message
...
That was very helpful. Thanks alot. Just had to get rid of the minus 2
though :)

"Bob Phillips" wrote in message
...
Sorry John,

try this instead

For i = colStart To colEnd
iPos = InStrRev(otherExcelFilePath, "\")
otherExcelFilePath = "='" & Left(otherExcelFilePath, iPos - 2) & _
"[" & Right(otherExcelFilePath, Len(otherExcelFilePath) - iPos)
&
"]"
ActiveWorkbook.Worksheets("MyWorksheet").Cells(i, rownum).Formula =
_
otherExcelFilePath & "OrigSheet'!$A" & CStr(i)
Next i

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Smith" wrote in message
...
Hi...Thx.

I tried your suggestion, but I get the error:
"You cannot change part of an array."

So, instead I tried using what you wrote like this:



ActiveWorkbook.Worksheets("MyWorksheet").Range("My Range").Rows(rowcount).For
mulaArray = "='[" & thePathname & "]Reporting'!$A4"

but I get the error:
"Unable to set the FormulaArray property of the Range class"




"Bob Phillips" wrote in message
...
John,

Not tested, but try

For i = colStart To colEnd
ActiveWorkbook.Worksheets("MyWorksheet").Cells(i,
rownum).Formula
=
"='[" & _
otherExcelFilePath & "]OrigSheet'!$A" & CStr(i)
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Smith" wrote in message
...
Hey folks,

(Excel Noob here)

I need to give certain cells a formula through code. I need it to
grab
the
formulas from another Excel spreadsheet's cells. I think I'm
pretty
close
here, but the following errors out. Do you see what's wrong?

For i = colStart To colEnd
ActiveWorkbook.Worksheets("MyWorksheet").Cells(i,
rownum).Formula
=
"='"
& otherExcelFilePath & "'!OrigSheet:$A" & CStr(i)
Next i

So I want the cells in row rownum from cols colStart to colEnd to
contain
the formulas in the other Excel spreadsheet. The other Excel
Spreadsheet
should use it's worksheet OrigSheet, row A, cols colStart to
colEnd.

Thx!