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

  1. See Mathematical Functions for more information on writing the Equation string.

  2. This method can be used as a replacement for the Transform method. The Transform method has been preserved for backwards compatibility.

  3. If the TransformType is set to wksTransformWithCol or wksTransformWithRow, the RangeMin and RangeMax values are required.

  4. If wksTransformWithCol is used, RangeMin and RangeMax are required. These are integers corresponding to the First row and Last row to apply the transform.

  5. 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.

  6. 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