[RESOLVED] value of formula in vba EXCEL

Hi all,

I have such a problem. I would like my code to depend of cells value. But problem is that in this cell there is formula which returns value. How can I make my program read this value.

for example: In A1 i have formula if(b1>0;1,-1)

and programmatically I would like to do sth like that

VB Code:

if range("a1").value = 1 then 
'my code
end if

the problem is that when value in cell A1 comes form formula code shown above doesn't work.

Please help.

Regards,
sweet_dreams

If your formula is like that, then it won't. You need a comma instead of a semicolon.
Otherwise, that exactly works. Unless your code is in a module, in which case you need to specify the worksheet as well as the range.

zaza

Hi Zaza,

I've just made mistake writing formula here. formula works fine. but problem is with this code (it is not in module):

VB Code:

if range("a1").value = 1 then 
'my code
end if

this code doesn't respond on value in cell a1

Type:

VB Code:

MsgBox(Worksheets("Sheet1").Range("A1").value)

and see what you get. It works for me.

zaza

here is the full code

VB Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 
If Target.address = "$A$1" Then
    If Range("a1").Value = 0 Then
        Range("a2:a20").ClearContents
    End If
End If
 
End Sub

You are not within the Sheet module, you are in the workbook module. Hence it doesn't recognise the range because you haven't told it on which sheet to find the range. Put:

Worksheets("Sheet1").Range...

instead.

Or, to use the current sheet, sh.range...

zaza

with Worksheets("Sheet1").Range... it also doesn't work :(

Do you have a worksheet called "Sheet1"?

I'd use the Worksheet_Change event for the individual sheet instead:

VB Code:

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Target.Address = "$A$1" Then
        If Target.Cells(1, 1).Value = 0 Then
            Target.Parent.Range("A2:A20").ClearContents
        End If
    End If
 
End Sub

Zaza, I made like that:

VB Code:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Worksheets(1).Range("a1") = 0 Then
        Range("a2:a20").ClearContents
End If
End Sub

I've just this code into SheetCalculate event and it works now fine. But thanks ZAZA for your help and attention

regards,
sweet_dreams

Quote:

---

Originally Posted by sweet_dreams

with Worksheets("Sheet1").Range... it also doesn't work :(

---

Workbook_Change passes the current sheet as the Sh parameter. You can also use Target.Parent to get the associated sheet.

Ah, did you want it do do something when you change from one sheet to another? This is not what the SheetChange event does. The sheetchange event fires whenever any cells in a sheet are changed.
You could use the SheetActivate event.

zaza