Returning value of a cell containing a formula

  • Thread starter Thread starter Ian Bayly
  • Start date Start date
I

Ian Bayly

This question shows how little I know of Excel.
I'm involved in a VB application running Excel in background
(Visible=false).
All I need to do is read and write values to certain cells.
No problem reading when the cell has a value, but when the cell has a
formula, I get a Type Mismatch error.
I moved a snip of my VB code into Excel VBA and get the same result when
running natively in Excel.

Here is my simple attempt:

Dim myRange As Range
Set myRange = Worksheets("HP - GST Sched").Cells(3, 5)
MsgBox myRange

Any help much appreciated

Ian B
 
Ian,

The only way I could get a type mismatch error is if the result
of the formula is an error value like #VALUE or #DIV/0. To cope
with this situation, use IsError to test the cell value prior to
using it in the MsgBox. For example,

Dim myRange As Range
Set myRange = Worksheets(1).Cells(3, 5)
If IsError(myRange.Value) Then
MsgBox "Cell contains an error value."
Else
MsgBox myRange
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Back
Top