Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Defining cell name / giving multiple cells a name | Excel Discussion (Misc queries) | |||
Macro that opens a window giving you info in a few cells | Excel Discussion (Misc queries) | |||
looping through an giving values to cells in vba | Excel Discussion (Misc queries) | |||
My formula Is giving me a #NAME! | Excel Worksheet Functions | |||
giving cells a nice color | Excel Programming |