How to read a special character (arrow) from one cell in a macro and store it in another

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,675
Office Version
  1. 365
Platform
  1. Windows
I am trying to write a macro that will read in a column of special characters (up arrow, down arrow, etc.) that indicate whether a value is over or under some range. It will then scan another set of numbers, store the appropriate character in the cell to the left, and apply a color to both cells.

This code works as long as the characters are from the normal character set. But it fails if I select any special characters.

Here's the mini-sheet:

Book2
BCDEFGHIJ
1
2
3
4RatingsValuesExpectedUnrated
560Max8484
640Min3939
79191
8Too high77
9Goldilocks5555
10Too low4040
116161
126060
Sheet1


And here's the macro code. It is invoked by a Button control which is not included in the mini-sheet even though I selected a range that includes it.

VBA Code:
Sub RateEm()

Const rnCodes As String = "Codes"     'Column of code characters & colors
Const rnValues As String = "Values"   'Column of values to be rated
Const rnRatings As String = "Ratings" 'Column to put ratings
Dim Values() As Variant               'Values array
Dim NumValues As Long                 'Number of values

Dim Codes() As Variant    'Code characters
Dim NumCodes As Long      'Number of codes
Dim Colors() As Long      'Category colors

Dim Min As Long     'Minimum good value
Dim Max As Long     'Maximum good value
Dim iRtg As Long    'Rating index
Dim i As Long       'Loop index

Codes = Range(rnCodes).Value  'Read in the character codes & colors
NumCodes = UBound(Codes, 1)   'Number of codes
ReDim Colors(1 To NumCodes)   'Make colors array the same size
For i = 1 To NumCodes         'Get the code colors
  Colors(i) = Range(rnCodes).Cells(i, 1).Interior.Color
Next i

Min = Range("Min").Value        'Get the minimum value
Max = Range("Max").Value        'Get the maximum value
Values = Range(rnValues).Value  'Read in the character codes
NumValues = UBound(Values, 1)   'Get the number of values

For i = 1 To NumValues
  Select Case Range(rnValues).Cells(i, 1)
    Case Is > Max: iRtg = 1
    Case Is < Min: iRtg = 3
    Case Else: iRtg = 2
  End Select
  Range(rnValues).Cells(i, 1).Interior.Color = Colors(iRtg)
  Range(rnRatings).Cells(i, 1).Interior.Color = Colors(iRtg)
  Range(rnRatings).Cells(i, 1).Value = Codes(iRtg, 1)
Next i

End Sub

If I use characters from the normal text character set, it works.

1634174588202.png


But if I use special characters from another character set, I get this:

1634174734868.png


Is there a way I can use any character and make it work?

Thanks
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,729
Office Version
  1. 2010
Platform
  1. Windows
It should be achieved directly with some conditional formatting without any code but maybe you have a particular reason to take the VBA path ?​
Maybe you forgot to use the same font in the destination cells …​
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,675
Office Version
  1. 365
Platform
  1. Windows
It should be achieved directly with some conditional formatting without any code but maybe you have a particular reason to take the VBA path ?​
Maybe you forgot to use the same font in the destination cells …​
The code snippet I enclosed is a small part of a much larger macro. I didn't include the whole thing to make it easier to focus on just the character codes.

Can I copy the font along with the character in the VBA code?
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,675
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Yes via the classic easy Range.Copy method …​
Perfect. After a little wandering about, I was able to get this to work. I replaced the 2 lines commented out with the one above.

VBA Code:
For i = 1 To NumValues
  Select Case Range(rnValues).Cells(i, 1)
    Case Is > Max: iRtg = 1
    Case Is < Min: iRtg = 3
    Case Else: iRtg = 2
  End Select
  Range(rnValues).Cells(i, 1).Interior.Color = Colors(iRtg)
  Range(rnCodes).Cells(iRtg, 1).Copy Destination:=Range(rnRatings).Cells(i, 1)
'  Range(rnRatings).Cells(i, 1).Interior.Color = Colors(iRtg)
'  Range(rnRatings).Cells(i, 1).Value = Codes(iRtg, 1)
Next i

Thank you very much. (y)(y)(y)🥰
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,729
Office Version
  1. 2010
Platform
  1. Windows
That's all, already solved ?!​
According to your initial XL2BB attachment, only column F should be updated or H also ?​
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,729
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

According to your initial attachment an easy way to update column F for example :​
VBA Code:
Sub Demo1()
        Dim V, R&
        Application.ScreenUpdating = False
    With [F4].CurrentRegion
            V = Evaluate(Replace("1+(#<=C5)+(#<C6)", "#", .Columns(4).Address))
        For R = 2 To UBound(V)
            Range("C8")(V(R, 1)).Copy .Cells(R, 1)
        Next
    End With
        Application.ScreenUpdating = True
End Sub
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,729
Office Version
  1. 2010
Platform
  1. Windows
More at beginner level so easier to understand like this :​
VBA Code:
Sub Demo1ez()
        Dim V, R&
        Application.ScreenUpdating = False
    With [F4].CurrentRegion
            V = Evaluate(Replace("(#<=C5)+(#<C6)", "#", .Columns(2).Address))
        For R = 2 To UBound(V)
            Cells(8 + V(R, 1), 3).Copy .Cells(R, 1)
        Next
    End With
        Application.ScreenUpdating = True
End Sub
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,729
Office Version
  1. 2010
Platform
  1. Windows
In post #7 it should be .Columns(2) rather than .Columns(4) …​
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,675
Office Version
  1. 365
Platform
  1. Windows
That's all, already solved ?!​
According to your initial XL2BB attachment, only column F should be updated or H also ?​
Only F. I added H to show what I wanted the result to be.

I just ran the updated macro and got this with some super-bold arrows:

1634180621253.png


This code will go in a much more complicated macro, but it gives me what I need to make it work. Thanks
 

Forum statistics

Threads
1,148,396
Messages
5,746,452
Members
424,020
Latest member
LongDoo

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
Top