Use VBA to insert special character as result of If statement

J

jmthompson

Well-known Member
Joined
Mar 31, 2008
Messages
966
Good afternoon,
I am building a macro to automate a quarterly process. The powers that be want the report generated by this macro to look exactly like the manually created report. The manual report uses special characters to illustrate progress, a red triangle pointing up or a black triangle pointing down. They copy and paste these characters from the character map.

I know how to determine which character should be used, but I cannot figure out how to insert the special character. Any ideas?

Code: 
For Each c In Range("C4:C28")
    If c < c.Offset(, 8) Then
    c.Offset(, 1).Value = "?????????"
    End If
Next c
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
without actually trying/testing...
i'm guessing your best bet is to figure out what code it is...
on a blank worksheet....copy and paste your triangle into cell A1...then in B1 place the formula =CODE(A1)
this will give you the number (ie code) of the character...

then in your vba code
c.Offset(,1).value = chr(x) 'where x is the code you got from above...

i'd probably also set (via code) the cell to the font and color of what you grab from the char map...
 
Last edited:
Upvote 0
They're not, but I went back to the Character map and grabbed the characters from there instead of the old file and came up with two completely different chr results.

Code is working perfectly now, thanks for all of your help!

Code: 
Sub progress()
Dim c As Range
For Each c In Range("C4:C28")
    If c = c.Offset(, 8) Then
    c.Offset(, 1) = "-"
    End If
Next c
For Each c In Range("C4:C28")
    If c < c.Offset(, 8) Then
    c.Offset(, 1).Value = Chr(113)
    c.Offset(, 1).Font.ColorIndex = xlAutomatic
    c.Offset(, 1).Font.Name = "Wingdings 3"
    End If
Next c
For Each c In Range("C4:C28")
    If c > c.Offset(, 8) Then
    c.Offset(, 1).Value = Chr(112)
    c.Offset(, 1).Font.ColorIndex = 3
    c.Offset(, 1).Font.Name = "Wingdings 3"
    End If
Next c
    
End Sub
 
Upvote 0
great...glad you got it working...for future reference...this will let you get stuff over #255...

here's another way...put your triangles in A1 & A2...

run this code...
Code: 
Sub test()
MsgBox AscW(Range("A1"))
MsgBox AscW(Range("A2"))
End Sub

then

in your code

Code: 
c.Offset(,1).value = chrw(x) 'where x is the code you got from above...
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Help selecting variable range for cut and paste
2 3
Replies
27
Views
1K
OaklandJim
O
H
  • Solved
Excel inserts a '@' character in cell formula when written using VBA.
Replies
3
Views
460
Fluff
Fluff
S
  • Question
Large project, first time calling subs and using error handling
Replies
0
Views
352
Shakeable_Drip
S
A
  • Solved
Retrieve multiple values using VBA from dictionary
Replies
4
Views
1K
ajl344
A
M
  • Question
Create new workbook for every sheet in active workbook, starting from Sheet(3) and saving them to a user specified folder location.
Replies
0
Views
815
Mars84
M

Forum statistics

Threads
1,214,839
Messages
6,121,892
Members
449,058
Latest member
Guy Boot

Share this page

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