cell-range relationship & non-standard symbol recognition

[c1802362]

Here are a couple programming problems I am trying to solve:

I have a workbook with two sheets. Sheet 1 ("tabulation") contains
multiple named ranges of varying sizes. Each row within each range has
a name, with multiple columns of numbers. The last column in each row
sums the previous cells in that row.

Sheet 2 ("master") contains the names listed on "tabulation," with
other information. The names are not sorted alphabetically.

My intent is to pick up each name on "master," find the corresponding
cell on "tabulation" (so far so good), then pick off the sum cell for
that row and transfer it back to "master." My problem is that when
the cell with the name is located, I don't seem to have a way to
identify which range the cell is part of. I need a method which can
identify what range a cell belongs to. With that information, I can
get the column count for that range, and go straight to the correct
sum.

Problem number two:

The first character of the name cell contains a symbol which keys the
user into which group the name belongs to (due the peculiarities of
the file, there wasn't a clean way to add a column with the group
name). If I pick a symbol within the standard character range
everything is fine. I can use the symbol and select the cells
accordingly. However, there are a number of symbols I'd like to use,
but they are unrecognizable when pasted into a VBA code module. For
example with Arial selected as the font,

Select Case Example
Case "©": Cells(activecell.Row, 1) =
"Group 1"
Case "#": Cells(activecell.Row, 1) =
"Group 2"

works without issue as the copyright symbol (Unicode (hex) 00A9) and
the pound symbol (0023) paste into the module without issue. However,
if I want to use a solid, right pointing triangle (25BA) I can't get
the code to recognize it - it shows up as a question mark


Case "?": Cells(activecell.Row, 1) =
"Group 3"

Suggestions on how to reference non-standard characters?

 

[mikerickson]

Problem 1) if you have a row named "myRow" where the last column holds the sum of the previous cells, =SUM(myRow)/2 is the value of that sum.

Problem 2) on a sheet put =CHAR(ROW()) and drag down. This will give you the ASCII codes for the non-standard characters avaliable. (Note: Named Ranges cannot have non-standard characters as their first character.)

 

[Joel]

Try this

Sub test()

Const RightArrow = 186


For RowCount = 1 To 3
Mychar = AscB(ActiveSheet.Range("A" & RowCount))
Select Case Mychar
Case AscB("©"): a = 1
Case AscB("#"): a = 2
Case RightArrow: a = 3
End Select
Next RowCount

End Sub

 

[c1802362]

Thanks,

With a few minor changes it worked perfectly. To wit:

Sub test()

Const RightArrow = 186

For RowCount = 1 To 3
Mychar = AscB(ActiveSheet.Range("A" & RowCount))

Symbol = CByte(Left( Mychar, 3)) <<<<<< added this code

Select Case Symbol
Case AscB("©"): a = 1
Case AscB("#"): a = 2
Case RightArrow: a = 3
End Select
Next RowCount

End Sub