6
Dim i As Integer

i = Int((8 - 2 + 1) * Rnd + 2)

Dim rg As String
Dim con As String

con = Str(i)

rg = "B" & con
MsgBox (rg)

This returns "B 4" not "B4 anyone know the issue

Andy G
  • 19,232
  • 5
  • 47
  • 69
user2642643
  • 61
  • 1
  • 2
  • Trim avoids any heading/ending blank-space related problem (con = Trim(Str(i))). I use it always when dealing with strings. Your question is answered below, this is just a generic recommendation :) – varocarbas Aug 01 '13 at 15:13

6 Answers6

19

Use Cstr(i) rather than Str(i). Cstr does not add a space.

bluish
  • 26,356
  • 27
  • 122
  • 180
Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • 1
    +1, Of the various suggestions, this is the most straightforward. – Doug Glancy Aug 01 '13 at 21:55
  • Str and Cstr do not behave the same if your locale settings uses a different decimal separator than "." Str always uses ".", Cstr uses local decimal separator. – drgs Oct 18 '20 at 21:27
6

From the Help page for Str()

When numbers are converted to strings, a leading space is always reserved for the sign of number. If number is positive, the returned string contains a leading space and the plus sign is implied.

Andy G
  • 19,232
  • 5
  • 47
  • 69
2

Str() leaves space for the sign.

As Excel has implicit conversion, you can use rg = "B" & i and get the range you want

SeanC
  • 15,695
  • 5
  • 45
  • 66
2

Use format() function ...

con = format(i)

rg = "B" & con
MsgBox (rg)
matzone
  • 5,703
  • 3
  • 17
  • 20
1

Use the Trim function to remove leading space as under:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim i As Integer

i = Int((8 - 2 + 1) * Rnd + 2)

Dim rg As String
Dim con As String

con = Str(i)

rg = "B" & Trim(con)
MsgBox (rg)
End Sub
rangan
  • 46
  • 1
0

Excel Concatonate a string based on the int.

Here's a desciption

https://stackoverflow.com/a/10004244/1504882

Community
  • 1
  • 1
Elias
  • 2,602
  • 5
  • 28
  • 57