Display
Name
|
Variable
Name
|
I/O
and
Type
|
Default
Value
|
Description
|
| Input
|
rng
|
Input/Output
Range
|
<active>
|
Specify the range to perform the replacing.
|
| Data Type
|
type
|
Input
int
|
0
|
Specify the type of data to find and replace.
Option list:
- num:Numeric
- To find and replace numeric data.
- str:String
- To find and replace string.
|
| Find What (-- for Missing Value)
|
find_value
|
Input
double
|
|
This is only available when Number (num) is selected for Data Type (the type variable). Combined with the operator selected for the Condition variable, the value of this variable defines the condition used to find the values to be replaced. "--" stands for the missing value here.
|
| Find What
|
find_str
|
Input
string
|
|
This is only available when String (str) is selected for Data Type (the type variable). Use it to specify the string to find.
|
| Condition
|
cond_value
|
Input
int
|
0
|
This is only available when Number (num) is selected for Data Type (the type variable). It specifies an operator. Combined with the value specified by Find What (the find_value variable), we can define the condition that is used to find the data to be replaced.
Option list:
- eq:Data=
- Find data that is equal to the Find What value.
- lt:Data<
- Find data that is less than the Find What value.
- le:Data<=
- Find data that is not greater than the Find What value.
- gt:Data>
- Find data that is greater than the Find What value.
- ge:Data>=
- Find data that is not less then the Find What value.
- ne:Data<>
- Find data that is not equal to the Find What value.
|
| Replace With
|
replace_value
|
Input
double
|
|
This is only available when Number (num) is selected for Data Type (the type variable). It specifies the new value with which all the found cells will be replaced.
|
| Replace With
|
replace_str
|
Input
string
|
|
This is only available when String (str) is selected for Data Type (the type variable). It specifies the new string with which all the found strings will be replaced.
|
| Tolerance
|
tolerance
|
Input
double
|
1E-8
|
This is only available when Number (num) is selected for Data Type (the type variable). It specifies the tolerance used to find the worksheet data to be replaced. If the absolute difference between the worksheet data value and the Find What value is less than the tolerance, these two values will be regarded as equal.
|
| Use Absolute Value
|
use_abs
|
Input
int
|
0
|
This is only available when Number (num) is selected for Data Type (the type variable). It specifies whether or not to use the absolute values of the worksheet data to test the given condition. This is disabled when Condition is ">" or "<" or "<>".
|
| Keep Sign if Condition True
|
keep_sign
|
Input
int
|
0
|
This is only available when Number (num) is selected for Data Type (the type variable). It specifies whether to keep the sign of the original value if the condition is true and the worksheet data is replaced.
|
| Set as Missing if Condition False
|
set_missing
|
Input
int
|
0
|
This is only available when Number (num) is selected for Data Type (the type variable). It specifies whether or not to set missing value to cells whose original values do not meet the condition.
|
| Match Whole Word Only
|
cond_str
|
Input
int
|
0
|
This is only available when String (str) is selected for Data Type (the type variable). It specifies whether the string to find should match the exact whole string you typed.
|
| Case Sensitive
|
con_case
|
Input
int
|
0
|
This is only available when String (str) is selected for Data Type (the type variable). It specifies whether the string search is case sensitive or not.
|
| Skip Link Cells
|
skip_link
|
Input
int
|
0
|
Specify whether to skip cells that contain links.
|
| Use Wildcards
|
wildcards
|
Input
int
|
0
|
Specify whether wildcards are allowed or not. If wildcards are used, "*" can stand for arbitrary string and "?" can stand for any arbitrary character.
|
| Include Label Rows
|
label
|
Input
int
|
0
|
Specify whether to include column label rows (LongName, Unit, Comment, etc.) to search.
|
|
|
undo
|
Input
int
|
0
|
Specify whether to allow undo.
|
| Look In
|
lookin
|
Input
int
|
6
|
Specify where to search the specified value or string.
Option list:
- active_sheet:Active Worksheet
- The active worksheet will be searched.
- active_book:Active Workbook
- The active workbook will be searched.
- all_in_active_folder:All Workbooks in Active Folder
- All workbooks in the active folder will be searched. But those in the subfolders will not be searched.
- recursive_in_active_folder:All Workbooks in Active Folder (Resursive)
- All workbooks in the active folder will be searched, including those in the subfolders.
- open_in_active_folder:All Workbooks in Active Folder (Open)
- All non-hidden workbooks in the active folder will be searched. But those in the subfolders will not be searched.
- all_in_project:All Workbooks in Project
- All workbooks in the project will be searched.
- specified:Specified Range
- The range specified by the rng variable will be searched.
|
This X-Function is capable of finding and replacing texts or numbers in worksheet cells.