concatenating excel cells with a loop

Posted on 2011-04-24
Last Modified: 2012-05-11
Hey folks,

I have a workbook with 2 sheets, sheet1 is the "input" and sheet2 is the "output". basically, I'm trying to find a way to derive the sheet2 output from the sheet1 input using a macro.

Essentially, the input sheet is the raw data, the output sheet is creating the skus for something else. The skus created are a combination of cells A+B+ the title and the value for c-n (this'll make more sense by looking at the sample output)

Any help greatly appreciated.

Question by:merv109
    LVL 30

    Expert Comment

    What is the logic behind deciding on "Y" or "N"?

    LVL 30

    Expert Comment

    Ok Ok. Got it.

    Few moments

    LVL 30

    Accepted Solution

    Please find a sample attached.

    Simply run the macro Sample which is there in the module.

    Hope this helps.


    Code Used

    Sub Sample()
        Dim ws1 As Worksheet, ws2 As Worksheet
        Dim ws1LastRow As Long, ws2LastRow As Long
        Dim i As Long, j As Long
        Set ws1 = Sheets("Input")
        Set ws2 = Sheets("output")
        ws1LastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row
        ws2LastRow = ws2.Range("A" & Rows.Count).End(xlUp).Row + 1
        For i = 2 To ws1LastRow
            For j = 3 To 14
                ws2.Range("A" & ws2LastRow).Value = ws1.Range("A" & i).Value & _
                                                    "-" & _
                                                    ws1.Range("B" & i).Value & _
                                                    ws1.Cells(1, j).Value
                ws2.Range("B" & ws2LastRow).Value = ws1.Cells(1, j).Offset(1).Value
                ws2LastRow = ws2LastRow + 1
            Next j
        Next i
    End Sub

    Open in new window


    Author Comment

    Perfect, exactly what I was after. Thanks!

    Author Closing Comment

    Thanks again!

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
    Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  ( Here (http…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now