VBA Range.Formula

  • Thread starter Thread starter King
  • Start date Start date
K

King

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.
 
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.
 
Thanks, Tim,

I will try change the code to read the cells with formula one by one...

King
 
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)
 
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
 
I guess I should test before posting...

Tim

Tim Zych said:
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
Click to expand...
 
Back
Top