Giving Cells a Formula

[John Smith]

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!

 

[Bob Phillips]

--

HTH

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

 

[Bob Phillips]

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]

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("MyRange").Rows(rowcount).For
mulaArray = "='[" & thePathname & "]Reporting'!$A4"

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

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)

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!

 

[Bob Phillips]

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)

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("MyRange").Rows(rowcount).For
mulaArray = "='[" & thePathname & "]Reporting'!$A4"

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

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)

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!

 

[John Smith]

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

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)

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("MyRange").Rows(rowcount).For

mulaArray = "='[" & thePathname & "]Reporting'!$A4"

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

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)


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

=

 

[Bob Phillips]

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

Aah well!

Bob

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

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)

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("MyRange").Rows(rowcount).For

mulaArray = "='[" & thePathname & "]Reporting'!$A4"

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




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)


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!