Code to obtain constant_numbers for xl types

zenigma

New Member
Joined
Oct 19, 2004
Messages
21
Ultimately I'm trying to go through a range of cell and attach to the left the corresponding return value. I've had some problems with the code in not getting what I want - so I inserted some Msgbox(s) for confirmation.
Code is as follows:

Sub FindCde()
'undernoted 3 lines are in range C3:C5
'xlLine
'xlLineMarkersStacked
'xlLineStacked
Dim cel As Range, Rng As Range
'Dim cde As Long 'causes error
Set Rng = Range("C3:C5")
For Each cel In Rng
cde = cel.Value
'expecting this msg to show "cell_ref" and "constant_number":
MsgBox cel & Space(10) & cde
'this message does return (as expected) "constant_numbers" for the undernoted types:
MsgBox xlLine & Space(5) & xlLineMarkersStacked & Space(5) & xlLineStacked
'ultimately next line should insert the "constant_number" in the cell to the left
'Range("cel").Offset(0, -1).Value = cde
Next
End Sub

Any help would be appreciated.
Thanks.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
and what goes wrong?

maybe changing cde from long to string or variant will work, or use:

cde = val(cel.Value)
 
Upvote 0
Thanks for your response Harvey,

The last line of code:
Range("cel").Offset(0, -1).Value = cde
produces an error.
Runtime error '1004': "Method 'Range' of object '_Global' failed"

In my attempt to correct this I commented it out and used the Msgbox feature to see what was being returned.

The second Msgbox (2): MsgBox xlLine & Space(5) & xlLineMarkersStacked & Space(5) & xlLineStacked
produces the information I want, namely: 4 66 63 the respetive return values for the xl types.

The first Msgbox (1): MsgBox cel & Space(10) & cde
produces: xlLine xlLine
when run with my code and the subsequent iterations produce the same results for the other items in the list. I've only shown the first three here out of a list of around 70 for this xl type.
What I'm expecting from this Msgbox is the 'cell reference' followed by the 'numerical value' the results of which I have established.

When I make your suggested change to the code the MsgBox(1)
produces: xlLine 0
while the first item changes in accordance with the list the second is always 0

Maybe this throws more light on the problem,
Thanks.

Edit:
Now managed to get the Cell Reference showing in Msgbox(1) by using cel.Address
Also changed: 'Range("cel").Offset(0, -1).Value = cde
To: cel.Offset(0, -1).Value = cde
but this just repeats the xl type as shown in cell to right.
Still not getting cde to show numeric value
 
Upvote 0
To recap on above:

Msgbox xlLine: 'produces 4
num = xlLine: Msgbox num: 'produces 4
however,
where cel represents the cell range containing xlLine
Msgbox cel.Address & space(5) & cel
produces $C$3 ....... xlLine : I want numeric value in place of xlLine
tried cel.value but same result.

Hoping someone can explain what I'm doing wrong.
Thanks.
 
Upvote 0
Hi,

I understand now what you are trying to do.
The xlLine is a constant variable. What you are trying to do is to convert a string with "xlLine" to the value of the constant xlLine, which is 4.

What you can do is use a select case statement:
This example uses just one cell, but I am sure you can implement it in your code.
Code:
DIM cde AS STRING

cde = RANGE("C3").VALUE
SELECT CASE cde
  CASE "xlLine":
    cde = xlLine
  CASE "xlSquare":
    cde = xlSquare
  'insert the other options here
END SELECT

MSGBOX (cde)
 
Upvote 0
Thanks Harvey,
but the code you've given doesn't help. I know I can get the value from cde = xlLine but I don't want to have to input all the data into a vba procedure. I have the information on a spreadsheet and I want to build that into a table. I already have quite an array of xl types and values and Im looking for a quick way to convert some others including vbext....

From my spreadsheet I will be able to build functions very quickly like:

Function xlChTyp(cde)
Select Case cde
Case 4: xlChTyp = " Line"
Case 66: xlChTyp = " Stacked Line with Markers"
Case 63: xlChTyp = " Stacked Line"
Case n: xlChTyp = " Pie"
Case n: xlChTyp = " Pie of Pie"
Case n: xlChTyp = " Stacked Pyramid Bar"
End Select
End Function

to use in my vba procedures. Now I know that I can substitute these numbers with the strings but I prefer to have the returned codes, as I think it looks a bit neater.

Thanks again for your efforts.

Edit:
Perhaps what I am trying to do is not possible?
 
Upvote 0
I guess there should be some function for evaluating a string as code. Unfortunately, I can't find it (evaluate only executes worksheet formula and is of no use in this case)
 
Upvote 0
Harvey,
What you posted got me thinking, it was an additional step, but from my spreadsheet I built the undernoted function:

Function xlChTypNo(cde)
Select Case cde
Case "xlLine": xlChTypNo = xlLine
Case "xlLineMarkersStacked": xlChTypNo = xlLineMarkersStacked
Case "xlLineStacked": xlChTypNo = xlLineStacked
Case "xlPie": xlChTypNo = xlPie
Case "xlPieOfPie": xlChTypNo = xlPieOfPie
Case "xlPyramidBarStacked": xlChTypNo = xlPyramidBarStacked
End Select
End Function

There are actually 73 types but I've only shown 6
Then ran my adjusted code:

Sub FindCde()
Dim cel As Range, Rng As Range
Dim cde As String
'Listing of xlChartType(s) in range c3:c75
Set Rng = Range("C3:C75")
For Each cel In Rng
cde = cel
cel.Offset(0, -1) = xlChTypNo(cel.Value)
Next
End Sub

It returned the codes I wanted to the adjacent cell. So works a treat.

Thanks again for your input.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top