Array and string

[sapka]

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
here is the code:<o></o>

[COLOR=black][FONT=Verdana]Sub start()[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]Dim i As Integer[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black]i = 5[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Range("A1:C1") = Array(CStr(i & "."), 111, "text")<o:p></o:p>[/COLOR][/FONT]
[COLOR=black][FONT=Verdana]End Sub<o:p></o:p>[/FONT][/COLOR]

<o></o>
For some reasons Excel reads the first element of array as number rahter than string and for that reason I lose the dot. How to fix it (I want to use Array function)?

 

[Rick Rothstein]

Excel is doing the conversion to a number for you because the cells are formatted as General. Try changing A1:A5's Cell Format to Text or let VB do it as part of your code...

Sub start()
  Dim i As Integer
  [COLOR=darkred]Range("A1:C1").NumberFormat = "@"[/COLOR]
  i = 5
  Range("A1:C1") = Array(i & ".", 111, "text")
End Sub

EDIT NOTE: Removed the CStr function call as per Wigi's posting.

 

[wigi]

Sub start()
    
    Dim i As Integer
    i = 5
    Range("A1:C1") = Array(i & ".", 111, "text")

End Sub

The operator & takes care of converting i to a String. You do not need an explicit CStr function around it.