Results 1 to 4 of 4

Thread: Excel trying to loop through column starting with cell A3

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    2

    Excel trying to loop through column starting with cell A3

    Hi All,
    I am trying to build a string by looping through all cells in column A however I want to start with row 3 in column A then.


    Code:
    newrow = "("
    For i = 1 To Worksheets("CHASE_LOOKUP").Cells(Worksheets("CHASE_LOOKUP").Rows.Count, "A").End(xlUp).Row
        newrow = newrow & "'" & Trim(Worksheets("CHASE_LOOKUP").Cells(i, "A").Value) & "',"
    Next i
    newrow = Left(newrow, Len(newrow) - 1)
    newrow = newrow & ")"

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Excel trying to loop through column starting with cell A3

    ??
    Code:
    Dim lngUpper As Long
    lngUpper=Worksheets("CHASE_LOOKUP").Cells(Worksheets("CHASE_LOOKUP").Rows.Count, "A").End(xlUp).Row
    newrow="('" & Trim(Worksheets("CHASE_LOOKUP").Cells(3, 1).Value) & "'"
    For i=4 To lngUpper
    newrow=newrow & ",'" & Trim(Worksheets("CHASE_LOOKUP").Cells(i, 1).Value) & "'"
    Next
    newrow=newrow & ")"
    AIRCODE!
    Not tested.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Excel trying to loop through column starting with cell A3

    Code:
    Sub makeString()
        Dim ws As Worksheet
        Dim lastRow As Long
        Dim J As Long
        Dim str As String
        
        Set ws = Worksheets("CHASE_LOOKUP")
        With ws
            lastRow = .Range("a" & Rows.Count).End(xlUp).Row
            str = Trim(.Range("a3").Value)
            For J = 4 To lastRow
                str = str & Trim(.Range("a" & J).Value)
            Next J
        End With
    End Sub

  4. #4

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    2

    Resolved Re: Excel trying to loop through column starting with cell A3

    Quote Originally Posted by vbfbryce View Post
    Code:
    Sub makeString()
        Dim ws As Worksheet
        Dim lastRow As Long
        Dim J As Long
        Dim str As String
        
        Set ws = Worksheets("CHASE_LOOKUP")
        With ws
            lastRow = .Range("a" & Rows.Count).End(xlUp).Row
            str = Trim(.Range("a3").Value)
            For J = 4 To lastRow
                str = str & Trim(.Range("a" & J).Value)
            Next J
        End With
    End Sub
    Thanks! that worked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width