J'ai ceci:

| col1 | col2 | col 3 |
|    5 |   FA |   OFF |
|  107 |   FA |    ON |
|   96 |   FO |    ON |

Et je veux MsgBox chaque ligne comme ça

Dim str As String
Dim r As Long

r = 2
While Celles(r,1).Value <> ""
    str = Rows(r)                    ' don't know how get row with space between items
    MsgBox str
    Set WshShell = CreateObject("WScript.Shell")
    Set WshShellExec = WshShell.Exec("""C:\mypath\prog.exe"" " & str)

    r = r+1
Wend

Je veux 3 MsgBox pour apparaître 5 FA OFF 107 FA ON 96 FO ON.

Par conséquent, comment obtenir une ligne correctement et ajouter de l'espace entre les éléments?

(Après je veux appeler WshShellExec avec des paramètres)

1
Rod 16 nov. 2017 à 16:58

5 réponses

Meilleure réponse
Sub x()

For i = 1 To 3
a = Application.Transpose(Application.Transpose(Range("a1:c1").Offset(i, 0).Value))
Debug.Print Join(a, "|")
Next i

End Sub
5
Nathan_Sav 16 nov. 2017 à 14:14

Construire juste une chaîne à partir des valeurs de toutes les colonnes

Dim i as Long 'HERE EDITED
For i = 0 To 2
str = str & " " & Cells(r, 1).Offset(,i) 'HERE EDITED
Next i
MsgBox str
str = ""
1
MarcinSzaleniec 16 nov. 2017 à 14:23

Essayez ça

Sub example()
    Dim str As String
    Dim r As Long
    Dim c

    ' I'd recommend changing this to your actual sheet
    With ActiveSheet
        For r = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
            str = vbNullString
            For Each c In Range(.Cells(r, 1), .Cells(r, .Cells(r, .Columns.Count).End(xlToLeft).Column))
                str = str & " " & c.Value2
            Next c
            MsgBox WorksheetFunction.Trim(str)
        Next r
    End With
End Sub
1
Tom 16 nov. 2017 à 14:13

Veuillez essayer ceci ...

Sub ConcatenateRowValues()
Dim x
Dim i As Long
Dim Str As String
x = Range("A1").CurrentRegion.Value
For i = 2 To UBound(x, 1)
    Str = Join(Application.Index(x, i, 0), " ")
    MsgBox Str
Next i
End Sub
1
Subodh Tiwari sktneer 16 nov. 2017 à 14:11

Essayez le code ci-dessous pour combiner les String par ligne:

Option Explicit

Sub CombStringinRow()

Dim str As String
Dim r As Long, Col As Long
Dim LastCol As Long
Dim LastRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row ' get last row with data from column "A"

For r = 2 To LastRow
    LastCol = Cells(r, Columns.Count).End(xlToLeft).Column ' get last column in current row

    For Col = 1 To LastCol
        If str <> "" Then
            str = str & " " & Cells(r, Col)
        Else
            str = Cells(r, Col)
        End If
    Next Col

    MsgBox str
    str = ""
Next r

End Sub
1
Shai Rado 16 nov. 2017 à 14:09
47331561