Hi Nigel,

Thanks again for further input.

Why cannot you use two cells?
Due to the existing layout of the Worksheet and manageable printouts it would
be desirable to use one cell.

The only possibility might be to use a value uses the integer and fractional parts as separate values.
e.g.: 100.124 becomes 100 and 124 respectively.

Interested in above possible solution using integer and fractional parts.

The second chart, CHART2, where I wish the VBA numeric values to be placed
has the following Excel Worksheet Formula in each cell. This Formula sums the
totals of a specific Numeric Label appearing in a single column in CHART1
(every third Row is blank):

=SUMPRODUCT(--(MOD(ROW(F$117:F$191)-ROW(F$117)+0,3)=0),--(F$117:F$191=$C269))
+SUMPRODUCT(--(MOD(ROW(F$194:F$264)-ROW(F$194)+0,3)=0),--(F$194:F$264=$C269))

There is a Row of Numeric Labels, then a Row of Numeric Values, then a Blank
Row. This Layout/ pattern is repeated down many Rows. The Rows of Numeric
Labels and Values work in pairs and are sorted as paired Rows, hence the
third Blank Row (after every two Rows) to denote a new paired Row of data.

The cell location of the Values to be added to CHART2 are based on the co-
ordinates (Row and Column Number) of the Values located on CHART1. These co-
ordinates are provided by the MATCH and INDEX Functions.

Could a VBA process and Formula be created to automate the positioning of
numeric values 1-100 on CHART2 by using Formulas similar to those below to
locate the relevant Row and Column Numbers relating to the values 1-100 on
CHART1.

Two Charts involved -
CHART1:
Named Range "Range3" Horizontal Numeric Labels (one Row) not in any order.
Named Range "Range2" First Veritcal Numeric Labels (one Column) in Ascending
order.
Named Range "Range1" Second Vertical Numeric Labels (one Column) not in any
order.

Locate Row Number:
=INDEX(Range1,MATCH(1,Range2,0),0)

Locate Column Number:
=MATCH(INDEX(Range1,MATCH(1,Range2,0),0),Range3,0)

CHART2:
Use the above co-ordinates eg: Row 11, Column 9 to find relevant cell.
This cell should now be populated via VBA with the numeric value 1 - NOT,
overwriting existing values created by Excel Worksheet Formulas.

in VBA you could use something like......(assumes value is in cell A1)

Sub Extract()
Dim val1 As Integer, val2 As Integer
val1 = Int(Range("A1"))
val2 = (Range("A1") - val1) * 1000

MsgBox "Value 1 = " & val1 & vbCrLf & "Value 2 = " & val2
End Sub

The above VBA code does not provide required Result. If possible, would
appreciate further assistance.

Cheers,
Sam

Nigel wrote:
The only possibility might be to use a value uses the integer and fractional
parts as separate values.

e.g.: 100.124 becomes 100 and 124 respectively. Using Integer to get the
first value and then subtract that from the original value and multiply it
up to the scale required. But you are going to be limited in the range of
values available. Why cannot you use two cells?

in VBA you could use something like......(assumes value is in cell A1)

Sub Extract()
Dim val1 As Integer, val2 As Integer
val1 = Int(Range("A1"))
val2 = (Range("A1") - val1) * 1000

MsgBox "Value 1 = " & val1 & vbCrLf & "Value 2 = " & val2
End Sub

Hi Nigel,

[quoted text clipped - 16 lines]
Best Wishes
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1