Numbers in format

Hello all

I would like to transfer every value from my column 1 of my MSHFlexgrid1 in my text1 but in a specific format.


Ex:

Collumn 1 of my grid
123456
321654
456789
987654
963852


In my Text1, i would have the result:
Text1 = ('123456','321654','456789','987654','963852')

How can i do that?

This is what i have for now:

Dim s As String
Dim i As Long, j As Long, n As Long
For n = 1 To MSHFlexGrid1.Rows - 1

 If MSHFlexGrid1.TextMatrix(n, 1) <> "" Then

        s = MSHFlexGrid1.TextMatrix(n, 1) & "/" & MSHFlexGrid1.TextMatrix(n, 1)

    Text1 = s
  
End If
Next

Open in new window



Thanks again for your help
LVL 11
Wilder1626Asked:
Who is Participating?
 
IronhoofsConnect With a Mentor Commented:
Oops forgot a part of the format:
Dim s As String
Dim i As Long, j As Long, n As Long
For n = 1 To MSHFlexGrid1.Rows - 1
   If MSHFlexGrid1.TextMatrix(n, 1) <> "" Then
      If Len(s) > 0 Then
         s = s + ","
      End If
      s = s + "'" + MSHFlexGrid1.TextMatrix(n, 1) + "'"
   End If
Next

Text1 = "(" + s + ")"

Open in new window

0
 
santhimurthydCommented:
Try with this

Dim s As String
Dim i As Long, j As Long, n As Long
s = "('"

For n = 1 To MSHFlexGrid1.Rows - 1
 If MSHFlexGrid1.TextMatrix(n, 1) <> "" Then
         s = s & "','" & MSHFlexGrid1.TextMatrix(n, 1)
 End If
Next
s = s & "')"

Text1 = s


0
 
IronhoofsCommented:
Is this what you mean?

Dim s As String
Dim i As Long, j As Long, n As Long
For n = 1 To MSHFlexGrid1.Rows - 1
   If MSHFlexGrid1.TextMatrix(n, 1) <> "" Then
      If Len(s) > 0 Then
         s = s + ","
      End If
      s = s + MSHFlexGrid1.TextMatrix(n, 1)
   End If
Next

Text1 = s

Open in new window

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Wilder1626Author Commented:
Hello santhimurthyd

I was getting an extra ",  before the value.


Ironhoofs, that code is very good :

Dim s As String
Dim i As Long, j As Long, n As Long
For n = 1 To MSHFlexGrid1.Rows - 1
   If MSHFlexGrid1.TextMatrix(n, 1) <> "" Then
      If Len(s) > 0 Then
         s = s + ","
      End If
      s = s + "'" + MSHFlexGrid1.TextMatrix(n, 1) + "'"
   End If
Next

Text1 = "(" + s + ")"

Open in new window


Let me try one last test
0
 
keyuCommented:
Dim s As String
Dim i As int, n As int
s = "('"

For n = 1 To MSHFlexGrid1.Rows - 1

 If MSHFlexGrid1.TextMatrix(n, 1) <> "" Then
if n=1 then

         s = "''" & MSHFlexGrid1.TextMatrix(n, 1)&"'"

else

     s = s & "',''" & MSHFlexGrid1.TextMatrix(n, 1)&"'"

end if

 End If
Next

s = s & "')"

Text1 = s

0
 
santhimurthydCommented:
Dim s As String
Dim i As Long, j As Long, n As Long
s = "('"

For n = 1 To MSHFlexGrid1.Rows - 1
 If MSHFlexGrid1.TextMatrix(n, 1) <> "" Then
      If Len(s) > 0 Then
            s = s & "','" & MSHFlexGrid1.TextMatrix(n, 1)
      Else
        s = MSHFlexGrid1.TextMatrix(n, 1)
      End If
 End If
Next
s = s & "')"

0
 
santhimurthydCommented:
Corrected code

Dim s As String
Dim i As Long, j As Long, n As Long
s = "('"

For n = 1 To MSHFlexGrid1.Rows - 1
 If MSHFlexGrid1.TextMatrix(n, 1) <> "" Then
      If Len(s) == 2 Then
        s = MSHFlexGrid1.TextMatrix(n, 1)
      Else
            s = s & "','" & MSHFlexGrid1.TextMatrix(n, 1)
      End If
 End If
Next
s = s & "')"

0
 
Wilder1626Author Commented:
Hello all

Ok, after many test, i'm having the best solution with this code:
Dim s As String
Dim i As Long, j As Long, n As Long
For n = 1 To MSHFlexGrid1.Rows - 1
   If MSHFlexGrid1.TextMatrix(n, 1) <> "" Then
      If Len(s) > 0 Then
         s = s + ","
      End If
      s = s + "'" + MSHFlexGrid1.TextMatrix(n, 1) + "'"
   End If
Next

Text1 = "(" + s + ")"


Thanks again to all for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.