loop on the rows in excel vba

Question

I need help in Excel. My question is: How can I get the first row of each row in this loop and print the output.

Input Column and Row value is like this:

    col1  col2   col3

    1    test    abc

    2    tests   dfg

    3    gtd     gdd

Output like this.

(col1,col2,col3)('1','test','abc');
(col1,col2,col3)('2','tests','dfg');
(col1,col2,col3)('3','gtd','gdd');

The Code that I am working on is

    For i = 1 To LastRow
        For j = 3 To LastCol
            If IsNumeric(Cells(i, j)) & Cells(i, j) > 0 = True Then
              vaString = vaString & Cells(i, j)
            End If
            If j <> LastCol Then vaString = vaString & ","
            If j = LastCol Then vaString = vaString
        Next j
            myString = myString                  
    Next i

Thanks in advance

bluk · Accepted Answer · 2019-08-27 07:20:19Z

1

From your parameters I will assume your data starts from cell C1. Change the first few lines if otherwise.

Sub Testing()
Dim FirstRow As Integer, FirstCol As Integer, LastRow As Integer, LastCol As Integer
FirstRow = 1
FirstCol = 3
LastRow = 4
LastCol = 5

Dim arrStr() As String
Dim strFirstRow As String
Dim strPath As String
strPath = "C:\..." ' Path of your choice
Open strPath For Append As #1

ReDim arrStr(FirstCol To LastCol)
For j = FirstCol To LastCol
    arrStr(j) = CStr(Cells(FirstRow, j))
Next j
strFirstRow = "(" & Join(arrStr, ",") & ")"

For i = FirstRow + 1 To LastRow
    If IsNumeric(Cells(i, FirstCol).Value) Then
        If Cells(i, FirstCol).Value > 0 Then
            ReDim arrStr(FirstCol To LastCol)
            For j = FirstCol To LastCol
                arrStr(j) = "'" & CStr(Cells(i, j)) & "'" 
            Next j
            Debug.Print strFirstRow & "(" & Join(arrStr, ",") & ");"
            Print #1, strFirstRow & "(" & Join(arrStr, ",") & ");"
        End If
    End If
Next i
Close #1
End Sub

edited Aug 27, 2019 at 7:20

answered Aug 26, 2019 at 6:30

bluk

4107 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arun Solomon Over a year ago

bluk ,Thanks it work. With is there any way to add single quotes like in the new update question also how to write the values to a txt file would you help me in that.

bluk Over a year ago

Edited that for you

JvdV · Accepted Answer · 2019-08-26 07:21:55Z

0

Here is a rather simple example making use of array variables:

Sub Test()

Dim x As Long
Dim str1 As String, str2 As String
Dim arr1() As Variant, arr2() As Variant

With ThisWorkbook.Sheets("Sheet1") 'Change according to your sheetname
    arr1 = Application.Transpose(Application.Transpose(.Range("A1:C1").Value2))
    arr2 = .Range("A2:C" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value2
    str1 = "(" & Join(arr1, ",") & ")"
    For x = LBound(arr2) To UBound(arr2)
        str2 = "(" & Join(Array(arr2(x, 1), arr2(x, 2), arr2(x, 3)), ",") & ")" & ";"
        Debug.Print str1 & str2 'Do something with the full string
    Next x
End With

End Sub

answered Aug 26, 2019 at 7:21

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Collectives™ on Stack Overflow

loop on the rows in excel vba

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related