Posts by morsejon

    Repeating Rows


    Andy,
    Thanks again for your help, everyone in my office thanks you.
    However, as always, with any solution comes one hundred more questions. Thus I am returning to you for further guidance.
    I was wondering if it were possible to run a similar macro, to perform a similar task, however this time I would like to have the macro perform the same function on the first row, then return the second and third rows repeating only these two rows for each new line entry. Then when there is no new line entries return the line designated 004, which I would place in row 10000, thus it would never be touched.
    I am thinking that I would have to make changes such as these
    Sub MyCSVExport()

    Dim strFilename As String
    Dim strBuffer As String
    Dim strColEnd(4) As String
    Dim lngRow As Long
    Dim intIndex As Integer
    Dim rngCell As Range
    Dim intUnit As Integer
    Dim strQuote As String
    Dim strDataRange As String

    strColEnd(1) = ":CR"
    strColEnd(2) = ":BI"
    strColEnd(3) = ":AW"
    strColEnd(4) = ":O"
    strQuote = """"
    strFilename = ThisWorkbook.Path & "\test.csv"
    intUnit = FreeFile
    Open strFilename For Output As intUnit

    lngRow = 1
    With ActiveSheet
    Do While .Cells(lngRow, 1) <> ""
    intIndex = intIndex + 1
    If intIndex > 3 Then intIndex = 2
    strDataRange = "A" & lngRow & strColEnd(intIndex) & lngRow
    strBuffer = ""
    For Each rngCell In .Range(strDataRange)
    If InStr(rngCell.Value, strQuote) > 0 Then
    strBuffer = strBuffer & strQuote & rngCell.Text & strQuote & ","
    Else
    strBuffer = strBuffer & rngCell.Text & ","
    End If
    Next
    Print #intUnit, Left(strBuffer, Len(strBuffer) - 1)
    lngRow = lngRow + 1
    Loop
    End With
    Close intUnit
    End Sub


    However I am unsure how to tell the macro to return row 10000 when there is no more data in 'sheet1'
    Thanks again for your help.

    Jindon,
    I was wondering if you might be able to elaborate on the specific purposes of the functions from the formula you supplied earlier. Specifically this portion of it...
    INDEX(Sheet1!A$1:A1,MAX(IF(Sheet1!A$1:A1<>"",ROW(A$1:A1))),1))
    The reason I am asking, is that while the formula works for cells in the 'A' column 'Sheet1' and the 'A' column of 'Sheet2' I am having trouble using it while trying to implement it in practical use.
    Specifically, I would like to reference cell 'A7' in 'Sheet1' from cell 'J2' in 'Sheet2' then reference cell 'A8' from cell 'J6' in 'Sheet2' etc. So I will not be simply filling in the column with the same formula.
    Thanks in advance for everything.

    Hi!
    I am looking for an Excel expression which I can put in a cell in the second sheet of a workbook in order to reference a cell in the first worksheet. If there is no value entered for the referenced cell, then I would like the cell above returned, if that is blank, then the cell above that... and so on.
    For Example assume in worksheet one cells A1 and A5 have data and no data in any other cell. I would like these values returned,
    For cells referencing A1 return A1
    A2 return A1
    A3 return A1
    A4 return A1
    A5 return A5
    A6 return A5
    A7 return a5


    Thanks in advance.

    Hi! Thanks for the welcome.
    The csv file attached is the ideal output from the 'play' worksheet from the 'play1' workbook.
    The highlighted cells indicate the fields where information maybe inserted, hence I need those places held. You will notice that some of the cells below the highlighted fields also contain data, with no results. When exporting to csv, there are commas which represent these cells as well, ideally there would be none.
    Thanks again for your help.

    I have been trying any and everything I, as well as anyone in my office, can think of to solve this problem but to no avail.
    I have data in series of four rows, each with differing lengths:
    first row ends in cell A107
    second ends B75
    third ends C45
    fourth ends D17
    Then the series will begin again with alternative data, but in the same four row repeating sequence. This time:
    first row ends in cell E107
    second ends F75
    third ends G45
    fourth ends H17
    When I try to export this data to csv format, it returns commas for each row, as place holders, for all empty cells. I believe this is due to Excel viewing the data as a rectangle, as opposed to the stairstep format which I have. As you can assume, these extra commas are a problem.
    Does anyone know if there is some way to instruct Excel to limit each row to a set width, or any other solution to this problem? Thanks!!