[RESOLVED] value of formula in vba EXCEL

[sweet_dreams]

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


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

[zaza]

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

[sweet_dreams]

Hi Zaza,

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

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


this code doesn't respond on value in cell a1

[zaza]

Type:


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

and see what you get. It works for me.

zaza

[sweet_dreams]

here is the full 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

[zaza]

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

[sweet_dreams]

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

[zaza]

Do you have a worksheet called "Sheet1"?

[Comintern]

I'd use the Worksheet_Change event for the individual sheet instead:
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

[sweet_dreams]

Zaza, I made like that:


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

[Comintern]

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.

[zaza]

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