Insert Additional Numeric Value in a cell using VBA

[Sam via OfficeKB.com]

Merry Christmas All,

Scenario:
Worksheet cells already populated with numeric values by Excel Worksheet
Formulas.
Is it possible with Excel VBA to insert an additional numeric value in the
Top Left of various cells already populated with numeric values by Excel
Worksheet Formulas?
Can two separate numeric values exist in different areas of the same cell?
The Worksheet cells are formatted as GENERAL.

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.

Use CHART1, Formula to find the Row Number that should be used for CHART2
=INDEX(Range1,MATCH(1,Range2,0),0)

The Horizontal and two Vertical Numeric Labels will be used as co-ordinates
to return the Column Number of where a specific value is housed. There are
two Columns of Vertical Numeric Labels to ensure a correct match. So a value
is matched accordingly with each of the Vertical Labels and finally, one
value matched on the Horizontal Labels. The Returned Column Number needs to
be located on CHART2.
Based on the Named Ranges, this next Formula finds the relevant values in
CHART1 and Returns the correct Column Number that must be located in CHART2.

=MATCH(INDEX(Range1,MATCH(1,Range2,0),0),Range3,0)

So, the pair of co-ordinates to locate the relevant cell for numerical value
1 is Row 11, Column 9 on CHART2.

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

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

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

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

Required Result:
Find the co-ordinates of numeric values 1-100 on CHART1. These co-ordinates
need to be applied to CHART2 to populate the Top Left of the correct cells
with the specific numeric values 1-100.

Best Wishes
Sam

 

[Nigel]

Can two separate numeric values exist in different areas of the same cell?

Afraid not!

 

[Sam via OfficeKB.com]

Hi Nigel,

Thank you for reply.

Can two separate numeric values exist in different areas of the same cell?

Afraid not!

Cheers
Sam

Can two separate numeric values exist in different areas of the same cell?

Afraid not!

Merry Christmas All,

[quoted text clipped - 54 lines]

Best Wishes
Sam

 

[Sam via OfficeKB.com]

Hi Nigel,

Thank you for reply.

If my scenario changed to two values in the SAME area of a cell, but each
value was distinguished or separated by the use of different colours for
each value, would that be possibe?

Cheers
Sam

Can two separate numeric values exist in different areas of the same cell?

Afraid not!

Merry Christmas All,

[quoted text clipped - 54 lines]

Best Wishes
Sam

 

[Nigel]

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



--
Cheers
Nigel

Hi Nigel,

Thank you for reply.

If my scenario changed to two values in the SAME area of a cell, but each
value was distinguished or separated by the use of different colours for
each value, would that be possibe?

Cheers
Sam

Can two separate numeric values exist in different areas of the same cell?

Afraid not!

Merry Christmas All,

[quoted text clipped - 54 lines]

Best Wishes
Sam

 

[Sam via OfficeKB.com]

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

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]