Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to retrieve the value from formula?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to retrieve the value from formula?
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 "Eric" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to retrieve the value from formula?
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 "Eric" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Thank everyone ver much for suggestions
Thank everyone very much for suggestions
Eric "Eric" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to retrieve the value from formula?
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(Ad dr) TheValue = cell.Value End Function HTH. Cheers, Socratis "Eric" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need formula to retrieve last non-blank cell in range | Excel Discussion (Misc queries) | |||
Search and retrieve | Excel Discussion (Misc queries) | |||
Formula to retrieve range of dates from a worksheet to calculate d | Excel Worksheet Functions | |||
retrieve cell formatting with IF(HLOOKUP) formula | Excel Worksheet Functions | |||
Retrieve data | Excel Discussion (Misc queries) |