How to retrieve the value from formula?

E

Eric

The following function returns formula as a string, does anyone have any
suggestions on how to retrieve the value from formula and store into x and y
variable?

Working with an opened file under E:\dir\file.xls, I try to compare the
value under worksheet Table C3 & C4 for if statement.
Does anyone have any suggestions on how to make the code work?
Thank in advance for any suggestions
Eric

Sub Test
x = TheValue("E:\dir", "file.xls", "Table", "C3")
y = TheValue("E:\dir", "file.xls", "Table", "C4")
If x >= y Then
MsgBox ("The value was " & y)
End If

Function TheValue(Path, WorkbookName, Sheet, Addr) As String
TheValue = "[" & WorkbookName & "]" & Sheet & "'!" & Addr
End Function

End Sub
 
T

Toppers

try:

Function TheValue(Path, WorkbookName, Sheet, Addr) As String
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(WorkbookName)
If Err <> 0 Then
Workbooks.Open Filename:=Path & WorkbookName
End If
TheValue = Workbooks(WorkbookName).Sheets(Sheet).Range(Addr).Value
End Function
 
M

Mike H

Eric,

Try this:-

Sub Sistence()
Dim sourcewb As Workbook
Set sourcewb = Workbooks.Open("C:\book2.xls", False, False) '<===Change to
your path
x = sourcewb.Sheets("table").Range("C3").Value
y = sourcewb.Sheets("table").Range("C4").Value
If x >= y Then
MsgBox ("The value was " & y)
End If
End Sub
 
S

Socratis

Eric,

If your workbook (file.xls) is already opened, you don't really need to pass
the path, so here is the modified function.

Function TheValue(WorkbookName, Sheet, Addr) As Variant
Dim cell As Range

Set cell = Workbooks(WorkbookName).Worksheets(Sheet).Range(Addr)
TheValue = cell.Value
End Function

HTH.

Cheers,
Socratis
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top