vba. Alt Chr populate a matrix

[montecarlo2012]

Hello.

Sub Mr_Excel()

Dim x As Long

      x = Range("A1").Value
      ActiveCell.Value = Chr(x)

End Sub

I am trying to figure out how to loop with the chr. The idea is to populate an array with all the character with the start number on A1

with the little code I already have a problem with the data type, I am not sure, the fact is I was able to see only until 255, after that I got error, and now trying to fill all this array and do the looping job
I really don't know how to put the alt character into the loop, I would like to be able to write a number on Cell A1 for example 250000 and then get all the chr in the way I show in the ilustration.
thank you for reading this
Please some help.

 

[Alex Blakenburg]

I can't really understand what you are trying to do.
The closest I can get to what you seem to be saying is the below:-
It maxes out at 65,535 so numbers in the 128,000 range don't make sense.
also codes 1 to 30 in Windows do not return anything that is visible.

With ActiveCell
      .Value = ChrW(10004)
      .Font.Name = "Segoe UI Emoji"
End With

 

[montecarlo2012]

Hi, Alex thank you for your time. about 128000 is:
as example if I type 128150 alt key code the result

so I was wandering how about if I am able to generate this symbols in a matrix
another example is altkey 128555 is iqual to

and so on.

 

[Alex Blakenburg]

Hi, Alex thank you for your time. about 128000 is:
as example if I type 128150 alt key code the result
View attachment 36141
so I was wandering how about if I am able to generate this symbols in a matrix
another example is altkey 128555 is iqual to

View attachment 36142
and so on.

OK try this.

Sub UsingUniChar()

    Dim ValToUse As Long    
    ValToUse = 128150
    
    ActiveCell = Application.WorksheetFunction.Unichar(ValToUse)

End Sub

 

[montecarlo2012]

Thank you Alex. this is what I got

 

[montecarlo2012]

Ok now I tried something and got some result,but still I need the loop

Sub Alt_Key_Chr()
Range("B2").Value = ChrW(55357) & ChrW(56450)
Range("B3").Value = ChrW(55357) & ChrW(56451)
Range("B4").Value = ChrW(55357) & ChrW(56452)
Range("B5").Value = ChrW(55357) & ChrW(56453)
Range("B6").Value = ChrW(55357) & ChrW(56454)
Range("B7").Value = ChrW(55357) & ChrW(56455)
Range("B8").Value = ChrW(55357) & ChrW(56456)
Range("B9").Value = ChrW(55357) & ChrW(56457)
Range("B10").Value = ChrW(55357) & ChrW(56458)
Range("B11").Value = ChrW(55357) & ChrW(56459)

Range("C2").Value = ChrW(55357) & ChrW(56460)
Range("C3").Value = ChrW(55357) & ChrW(56461)
Range("C4").Value = ChrW(55357) & ChrW(56462)
Range("C5").Value = ChrW(55357) & ChrW(56463)
Range("C6").Value = ChrW(55357) & ChrW(56464)
Range("C7").Value = ChrW(55357) & ChrW(56465)
Range("C8").Value = ChrW(55357) & ChrW(56466)
Range("C9").Value = ChrW(55357) & ChrW(56467)
Range("C10").Value = ChrW(55357) & ChrW(56468)
Range("C11").Value = ChrW(55357) & ChrW(56469)
End Sub

So when I enter a value on "A1" must generate the rest.

 

[montecarlo2012]

Well, I tried something else :
but I can get column by colum, the loop work on column B row by row, but not on C D etc

Sub Monte_carlo()

Dim vr As Integer
Dim VU As Variant
Dim CC As Long

VU = Range("A1").Value

         For vr = 2 To 11
                  For CC = 2 To 12
                
                        Cells(CC, "B").Value = ChrW(55357) & ChrW(VU)
                      
                        VU = VU + 1
                
                  Next CC
         Next vr

End Sub

Thanks for any help.

 

[montecarlo2012]

Ok, is 3:04 in the morning, this time I figure out, I am really happy I did it.

Sub Monte_carlo()

Dim vr As Integer
Dim VU As Variant
Dim CC As Long

VU = Range("A1").Value
For CC = 2 To 20
         For vr = 2 To 11
                  
                        Cells(CC, vr).Value = Cells(CC, vr) + ChrW(55357) & ChrW(VU)
                        
                        VU = VU + 1
                  
                  Next
         Next

End Sub

is exactly what I was looking for.
thanks for reading this

 

[Alex Blakenburg]

Ok now I tried something and got some result,but still I need the loop

This will give you the loop but I have also changed the function.
The one you are using is a totally different numbering system and if use 65 as a starting number instead of A you get �A and if you use your original no of 128000 it will crash.
(it maxes out at 65535)

The function I have used came from Solomon Rutzky at Convert Unicode code point to corresponding character in Excel spreadsheet.

This won't work for your latest numbering range starting from 56450 since these are invalid and if you use UniChar(56450) you will get #N/A.
The excluded range is this:
If val >= 55296 And val <= 57343 Then
UnicodeFromInt = "ERROR: surrogate code points are not displayable!!"

Sub AltKeyChrLoopUniCode()
    
    Dim CodeValue As Long
    Dim lRow As Long
    Dim lCol As Long
    Dim lRowFirst As Long
    Dim lColFirst As Long
    Dim lRowMaxLoop As Long
    Dim lColMaxLoop As Long    
    
    lRowFirst = 2
    lColFirst = 2
    lRowMaxLoop = 10
    lColMaxLoop = 10
    
    CodeValue = Range("A1").Value
    
    'Column Loop
    For lCol = lColFirst To (lColFirst + lColMaxLoop - 1)
        'Row Loop
        For lRow = lRowFirst To (lRowFirst + lRowMaxLoop - 1)
            Cells(lRow, lCol).Value = UnicodeFromInt(CodeValue)
            CodeValue = CodeValue + 1
        Next lRow
    Next lCol

End Sub

'https://superuser.com/questions/308865/convert-unicode-code-point-to-corresponding-character-in-excel-spreadsheet
'by Solomon Rutzky
Function UnicodeFromInt(val As Long)
    If val < 0 Or val > 1114111 Then
        UnicodeFromInt = "ERROR: value must be between 0 and 1114111!!"
        GoTo GetOut
    End If

    If val >= 55296 And val <= 57343 Then
        UnicodeFromInt = "ERROR: surrogate code points are not displayable!!"
        GoTo GetOut
    End If

    If val < 65536 Then
        UnicodeFromInt = ChrW(val)
    Else
        UnicodeFromInt = ChrW(55232 + Int(val / 1024)) & ChrW(56320 + Int(val Mod 1024))
    End If

GetOut:
End Function

 

[montecarlo2012]

Alex thank you work really good.