none
Create a sequece from 1 to whatever accross non-contiuous/contiguous cells w/in one column RRS feed

  • Question

  • OK, I hope I describe this correctly.

    What I would like to do is to fill a series of numbers in column Z based upon weather there is a value in column E.  Keep in mind that values in Column E have blank cells between them which makes filling a series in excel not work.

    Starting at row 9 look at column E if there is a value in that Row/Column then enter a numeric value in the same row (9) and column Z starting with one.  Go to the next row if there is a value in column E and row 10 then increment and make column/row Z10 number 2 and so on until the end of data (based upon last row on column K is reached).  I normally would research but don't have time right now as I am swamped with work, and this is on of my hold ups.  Manually doing this makes for a lot of mistakes.

    Regards,

    Neil


    Jonnybrovo815

    Wednesday, January 20, 2016 5:22 PM

Answers

  • Hi

    Try this macro..

    Sub test()

        Dim lst As Long, i As Integer, tsl as long
        lst = Cells(Rows.Count, 5).End(xlUp).Row
        tsl = Cells(Rows.Count, 25).End(xlUp).Row

        For i = 9 To lst

            If Cells(i, 5) = "" Then
                Cells(i, 5).Offset(0, 21) = tsl

                tsl = tsl + 1
            End If
        Next i

    End Sub


    Cimjet


    • Edited by Cimjet Thursday, January 21, 2016 4:52 PM
    • Proposed as answer by Cimjet Friday, January 22, 2016 5:14 PM
    • Marked as answer by jonnybrovo815 Friday, January 29, 2016 10:09 PM
    Thursday, January 21, 2016 4:48 PM
  • OK this is what I ended up with and it works for my purposes.  I had to change it a bit to add a section to remove any existing values in column Z.  Then I had to make sure it only went to the bottom of my data based upon column K or the last filled in cell in column K before a blank row.  It is in escence what Cimjet provided with some tweeks to make it work exactly how I needed it to work.  Also I decided to go with a base 10 sequence instead of by ones.

    Sub PkgSq()

    Dim i As Integer
    Dim tsl As Long
    Dim LastRow As Long

    LastRow = Worksheets("NCSA_ISS_ITEM_BOM").Range("K9").End(xlDown).row

    'wipe column 21 row 9 to endrow
           Range("Z9:Z10000").ClearContents

        tsl = 10

        For i = 9 To LastRow
            
            If Cells(i, 5) <> "" Then
                Cells(i, 5).Offset(0, 21) = tsl

                tsl = tsl + 10
            End If
        Next i

    End Sub


    Jonnybrovo815

    • Marked as answer by jonnybrovo815 Friday, January 29, 2016 10:09 PM
    Friday, January 29, 2016 10:08 PM

All replies

  • Hi Neil,

    First, to find the last row on column K is reached, we could use Range.End property. For example:

     Dim LastRow As Long
        With ActiveSheet
            LastRow = .Cells(.Rows.Count, "K").End(xlUp).Row
        End With
        MsgBox LastRow

    Secondly, for increment logical, we just could define a variable for increment (add 1 if after set value to Z).

    To move cell/range, we could use Range.Offset property, so you could iterate cells in column E according to the row number (first point result) to check the value and set value.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, January 21, 2016 9:42 AM
    Moderator
  • Hi

    Try this macro..

    Sub test()

        Dim lst As Long, i As Integer, tsl as long
        lst = Cells(Rows.Count, 5).End(xlUp).Row
        tsl = Cells(Rows.Count, 25).End(xlUp).Row

        For i = 9 To lst

            If Cells(i, 5) = "" Then
                Cells(i, 5).Offset(0, 21) = tsl

                tsl = tsl + 1
            End If
        Next i

    End Sub


    Cimjet


    • Edited by Cimjet Thursday, January 21, 2016 4:52 PM
    • Proposed as answer by Cimjet Friday, January 22, 2016 5:14 PM
    • Marked as answer by jonnybrovo815 Friday, January 29, 2016 10:09 PM
    Thursday, January 21, 2016 4:48 PM
  • Thanks Cimjet

    This almost works but it is filling in the values where there are no values in column 5.  It should fill in only where there are values in column 5.  I was looking briefly for a solution or answer to the opposite of "" and didn't come up with anything quickly.  Do you know what that values is?  Is it " "?




    Jonnybrovo815

    Thursday, January 28, 2016 10:49 PM
  • Hi Jonnybrovo815,

    To check the cell value isn’t empty, please use If Cells(I,5)<>”” Then.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, January 29, 2016 2:00 AM
    Moderator
  • Hi

    I'm not having that problem, see link below, it's a zip file with what I understand is your requirement.

    http://1drv.ms/1Qx0DuB


    Cimjet

    Friday, January 29, 2016 1:08 PM
  • OK this is what I ended up with and it works for my purposes.  I had to change it a bit to add a section to remove any existing values in column Z.  Then I had to make sure it only went to the bottom of my data based upon column K or the last filled in cell in column K before a blank row.  It is in escence what Cimjet provided with some tweeks to make it work exactly how I needed it to work.  Also I decided to go with a base 10 sequence instead of by ones.

    Sub PkgSq()

    Dim i As Integer
    Dim tsl As Long
    Dim LastRow As Long

    LastRow = Worksheets("NCSA_ISS_ITEM_BOM").Range("K9").End(xlDown).row

    'wipe column 21 row 9 to endrow
           Range("Z9:Z10000").ClearContents

        tsl = 10

        For i = 9 To LastRow
            
            If Cells(i, 5) <> "" Then
                Cells(i, 5).Offset(0, 21) = tsl

                tsl = tsl + 10
            End If
        Next i

    End Sub


    Jonnybrovo815

    • Marked as answer by jonnybrovo815 Friday, January 29, 2016 10:09 PM
    Friday, January 29, 2016 10:08 PM