asked on

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

santhimurthyd

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

Ironhoofs

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

ASKER CERTIFIED SOLUTION

Ironhoofs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wilder1626

ASKER

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

keyu

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

santhimurthyd

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 & "')"

santhimurthyd

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 & "')"

Wilder1626

ASKER

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.