| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Range.Formula
Hello,
I have a problem with below VBA code to read the cells formula into VBA Dim objSheet As Excel.Worksheet Set objSheet = ThisWorkbook.Sheets("Master") Dim varSheetData As Variant varSheetData = objSheet.Range("A9:FB820").Formula when above line is execute, a error 1004 Application-defined or object-defined error was occur but if the code change to read cells value varSheetData = objSheet.Range("A9:FB820").Value its ok and give me a array contain all the data... is there anything I was missing or it just not supported?? Thanks, King. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Range.Formula
Probably too complicated to program the return of the formulas, since the
precedents and dependents could also be involved. However, it will return a single cell formula. "King" wrote in message ... Hello, I have a problem with below VBA code to read the cells formula into VBA Dim objSheet As Excel.Worksheet Set objSheet = ThisWorkbook.Sheets("Master") Dim varSheetData As Variant varSheetData = objSheet.Range("A9:FB820").Formula when above line is execute, a error 1004 Application-defined or object-defined error was occur but if the code change to read cells value varSheetData = objSheet.Range("A9:FB820").Value its ok and give me a array contain all the data... is there anything I was missing or it just not supported?? Thanks, King. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Range.Formula
Reading into a variant array doesn't work with .Formula
Tim "King" wrote in message ... Hello, I have a problem with below VBA code to read the cells formula into VBA Dim objSheet As Excel.Worksheet Set objSheet = ThisWorkbook.Sheets("Master") Dim varSheetData As Variant varSheetData = objSheet.Range("A9:FB820").Formula when above line is execute, a error 1004 Application-defined or object-defined error was occur but if the code change to read cells value varSheetData = objSheet.Range("A9:FB820").Value its ok and give me a array contain all the data... is there anything I was missing or it just not supported?? Thanks, King. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Range.Formula
Thanks, Tim,
I will try change the code to read the cells with formula one by one... King "Tim Williams" wrote in message ... Reading into a variant array doesn't work with .Formula Tim "King" wrote in message ... Hello, I have a problem with below VBA code to read the cells formula into VBA Dim objSheet As Excel.Worksheet Set objSheet = ThisWorkbook.Sheets("Master") Dim varSheetData As Variant varSheetData = objSheet.Range("A9:FB820").Formula when above line is execute, a error 1004 Application-defined or object-defined error was occur but if the code change to read cells value varSheetData = objSheet.Range("A9:FB820").Value its ok and give me a array contain all the data... is there anything I was missing or it just not supported?? Thanks, King. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Range.Formula
Thanks, Tim,
I will try change the code to read the cells with formula one by one... King "Tim Williams" wrote in message ... Reading into a variant array doesn't work with .Formula Tim "King" wrote in message ... Hello, I have a problem with below VBA code to read the cells formula into VBA Dim objSheet As Excel.Worksheet Set objSheet = ThisWorkbook.Sheets("Master") Dim varSheetData As Variant varSheetData = objSheet.Range("A9:FB820").Formula when above line is execute, a error 1004 Application-defined or object-defined error was occur but if the code change to read cells value varSheetData = objSheet.Range("A9:FB820").Value its ok and give me a array contain all the data... is there anything I was missing or it just not supported?? Thanks, King. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Range.Formula
Hmm, it works for me.
Dim varSheetData As Variant varSheetData = Range("A9:FB820").Formula Just tested it. In the immediate window: ?varSheetData(812,158) =ADDRESS(ROW(),COLUMN(),4) As an alternative try FormulaR1C1. Then convert back using: application.ConvertFormula(varSheetData(812,158) ,xlR1C1,xlA1) -- Regards, Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility "King" wrote in message ... Hello, I have a problem with below VBA code to read the cells formula into VBA Dim objSheet As Excel.Worksheet Set objSheet = ThisWorkbook.Sheets("Master") Dim varSheetData As Variant varSheetData = objSheet.Range("A9:FB820").Formula when above line is execute, a error 1004 Application-defined or object-defined error was occur but if the code change to read cells value varSheetData = objSheet.Range("A9:FB820").Value its ok and give me a array contain all the data... is there anything I was missing or it just not supported?? Thanks, King. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Range.Formula
Ohh..........
I got the problem, its because the worksheet that I want to read the formula is protected, I can now read the formula by cancel the protection first and enable back after read. King "Tim Zych" <tzych@nospam at earthlink dot net wrote in message ... Hmm, it works for me. Dim varSheetData As Variant varSheetData = Range("A9:FB820").Formula Just tested it. In the immediate window: ?varSheetData(812,158) =ADDRESS(ROW(),COLUMN(),4) As an alternative try FormulaR1C1. Then convert back using: application.ConvertFormula(varSheetData(812,158) ,xlR1C1,xlA1) -- Regards, Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility "King" wrote in message ... Hello, I have a problem with below VBA code to read the cells formula into VBA Dim objSheet As Excel.Worksheet Set objSheet = ThisWorkbook.Sheets("Master") Dim varSheetData As Variant varSheetData = objSheet.Range("A9:FB820").Formula when above line is execute, a error 1004 Application-defined or object-defined error was occur but if the code change to read cells value varSheetData = objSheet.Range("A9:FB820").Value its ok and give me a array contain all the data... is there anything I was missing or it just not supported?? Thanks, King. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Range.Formula
I guess I should test before posting...
Tim "Tim Zych" <tzych@nospam at earthlink dot net wrote in message ... Hmm, it works for me. Dim varSheetData As Variant varSheetData = Range("A9:FB820").Formula Just tested it. In the immediate window: ?varSheetData(812,158) =ADDRESS(ROW(),COLUMN(),4) As an alternative try FormulaR1C1. Then convert back using: application.ConvertFormula(varSheetData(812,158) ,xlR1C1,xlA1) -- Regards, Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility "King" wrote in message ... Hello, I have a problem with below VBA code to read the cells formula into VBA Dim objSheet As Excel.Worksheet Set objSheet = ThisWorkbook.Sheets("Master") Dim varSheetData As Variant varSheetData = objSheet.Range("A9:FB820").Formula when above line is execute, a error 1004 Application-defined or object-defined error was occur but if the code change to read cells value varSheetData = objSheet.Range("A9:FB820").Value its ok and give me a array contain all the data... is there anything I was missing or it just not supported?? Thanks, King. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Range.Formula
Ohh..........
I got the problem, its because the worksheet that I want to read the formula is protected, I can now read the formula by cancel the protection first and enable back after read. King "Tim Zych" <tzych@nospam at earthlink dot net wrote in message ... Hmm, it works for me. Dim varSheetData As Variant varSheetData = Range("A9:FB820").Formula Just tested it. In the immediate window: ?varSheetData(812,158) =ADDRESS(ROW(),COLUMN(),4) As an alternative try FormulaR1C1. Then convert back using: application.ConvertFormula(varSheetData(812,158) ,xlR1C1,xlA1) -- Regards, Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility "King" wrote in message ... Hello, I have a problem with below VBA code to read the cells formula into VBA Dim objSheet As Excel.Worksheet Set objSheet = ThisWorkbook.Sheets("Master") Dim varSheetData As Variant varSheetData = objSheet.Range("A9:FB820").Formula when above line is execute, a error 1004 Application-defined or object-defined error was occur but if the code change to read cells value varSheetData = objSheet.Range("A9:FB820").Value its ok and give me a array contain all the data... is there anything I was missing or it just not supported?? Thanks, King. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Range.Formula
I guess I should test before posting...
Tim "Tim Zych" <tzych@nospam at earthlink dot net wrote in message ... Hmm, it works for me. Dim varSheetData As Variant varSheetData = Range("A9:FB820").Formula Just tested it. In the immediate window: ?varSheetData(812,158) =ADDRESS(ROW(),COLUMN(),4) As an alternative try FormulaR1C1. Then convert back using: application.ConvertFormula(varSheetData(812,158) ,xlR1C1,xlA1) -- Regards, Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility "King" wrote in message ... Hello, I have a problem with below VBA code to read the cells formula into VBA Dim objSheet As Excel.Worksheet Set objSheet = ThisWorkbook.Sheets("Master") Dim varSheetData As Variant varSheetData = objSheet.Range("A9:FB820").Formula when above line is execute, a error 1004 Application-defined or object-defined error was occur but if the code change to read cells value varSheetData = objSheet.Range("A9:FB820").Value its ok and give me a array contain all the data... is there anything I was missing or it just not supported?? Thanks, King. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
| conditional formula: sum a range if text present in another range | Excel Discussion (Misc queries) | |||
| formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
| Macro to input formula in range based on another range | Excel Programming | |||
| Range.Formula and Range question using Excel Automation | Excel Programming | |||
![King[_6_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
