Transform3 Method
The Transform3 method applies a mathematical transform equation to columns. Returns a Boolean. This command replaces the Transform and Transform2 commands.
Syntax
object.Transform3( Method, FirstRow, LastRow, Equation, TreatEmptyCells, TreatTextCells, TreatNumberCells )
Parameter | Type | Description |
Method | wksTransformWith | required; transform on rows, columns, or cells; default is wksTransformWithCol |
FirstRow | Variant | required; first row (or column) containing values to transform |
LastRow | Variant | required; last row (or column) containing values to transform |
Equation | String | required; equation written as a string |
TreatEmptyCells | wksTDHEmptyCells | required; treat empty cells as blank, as 0, or as empty text (""); default is wksTDH_EmptyCell_BlankResult (blank) |
TreatTextCells | wksTDHTextCells | required; treat text cells as blank, as text, as 0, or as numbers (if possible); default is wksTDH_TextCell_BlankResult (blank) |
TreatNumberCells | wksTDHNumberCells | required; treat number cells as blank, as numbers, as text, or as empty text (""); default is wksTDH_NumberCell_UseExactly (treat as numbers) |
Remarks
-
See Mathematical Functions for more information on writing the Equation string.
-
This method can be used as a replacement for the Transform method. The Transform method has been preserved for backwards compatibility.
-
If the TransformType is set to wksTransformWithCol or wksTransformWithRow, the RangeMin and RangeMax values are required.
-
If wksTransformWithCol is used, RangeMin and RangeMax are required. These are integers corresponding to the First row and Last row to apply the transform.
-
If wksTransformWithRow is used RangeMin and RangeMax are required. These are integers corresponding to the First column and Last column to apply the transform. An underscore denotes a variable row number in the Equation string.
-
If wksTransformWithCell is used RangeMin and RangeMax are not used and should not be set.
Example 1
This example shows how to transform only numeric cell data by columns.
Wks.Transform3(wksTransformWithCol,1,30,"c=a*b",wksTDH_EmptyCell_BlankResult,wksTDH_TextCell_BlankResult,wksTDH_NumberCell_UseExactly)
Example 2
This example shows how to transform cell data by rows. Text are converted to numbers if possible.
Wks.Transform3(wksTransformWithRow,1,7,"_30 = SUM( _1.._28 )",wksTDH_EmptyCell_BlankResult,wksTDH_TextCell_ConvertToNumber,wksTDH_NumberCell_UseExactly)
Example 3
This example shows how to transform individual cells. Both text and empty cells are treated as zero.
Wks.Transform3(wksTransformWithCell,3,3,"D3 = A1+A2+B1+B2-C1-C2",wksTDH_EmptyCell_ConvertToZero,wksTDH_TextCell_ConvertToZero,wksTDH_NumberCell_UseExactly)
Used by: Worksheet object