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.

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.

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.

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.

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.

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.

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.

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.

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.

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.