none
Sequential numbering with varying spaces RRS feed

  • Question

  • Hi All....

    I have columns C and D, with Names down column D.  there are varying numbers of blank rows between the names. I need a macro to start in C13 and put a sequential number down column C for each name in column D, no matter how many spaces between the names in column D.

     

    Vaya con Dios,

    Chuck, CABGx3


    Chuck, CABGx3
    Thursday, June 23, 2011 11:14 PM

Answers

  • hi  Chuck,
     
    Dim n As Integer, i As Integer
    n = Evaluate("SUMPRODUCT(ISTEXT(D13:D65535)*1)")
    For i = Range("D65536").End(xlUp).Row To 13 Step -1
     If Range("D" & i) <> "" Then Range("C" & i) = n:  n = n - 1
    Next
     
    hola Bélgica
     
    --
    isabelle
     
     
    • Marked as answer by CABGx3 Friday, June 24, 2011 1:45 AM
    Friday, June 24, 2011 12:01 AM
  • hi  Chuck,
     
    i'm glad i was able to help,
     for more caution, perhaps you should check that the cell value is text
    by adding an other condition "Not IsNumeric"
     
    Dim n As Integer, i As Integer
    n = Evaluate("SUMPRODUCT(ISTEXT(D13:D65535)*1)")
    For i = Range("D65536").End(xlUp).Row To 13 Step -1
     If Not IsNumeric(Range("D" & i)) And Range("D" & i) <> "" Then Range("C" & i) = n: n = n - 1
    Next
     
    --
    isabelle
     
    • Marked as answer by CABGx3 Friday, June 24, 2011 1:45 AM
    Friday, June 24, 2011 1:44 AM

All replies

  • hi  Chuck,
     
    Dim n As Integer, i As Integer
    n = Evaluate("SUMPRODUCT(ISTEXT(D13:D65535)*1)")
    For i = Range("D65536").End(xlUp).Row To 13 Step -1
     If Range("D" & i) <> "" Then Range("C" & i) = n:  n = n - 1
    Next
     
    hola Bélgica
     
    --
    isabelle
     
     
    • Marked as answer by CABGx3 Friday, June 24, 2011 1:45 AM
    Friday, June 24, 2011 12:01 AM
  • Absolutely OUTSTANDING!!!

    Your code fills my bill perfectly!

    You are most kind, and so very, very talented.

    Many thanks,

    Vaya con Dios,

    Chuck, CABGx3

     


    Chuck, CABGx3
    Friday, June 24, 2011 12:16 AM
  • hi  Chuck,
     
    i'm glad i was able to help,
     for more caution, perhaps you should check that the cell value is text
    by adding an other condition "Not IsNumeric"
     
    Dim n As Integer, i As Integer
    n = Evaluate("SUMPRODUCT(ISTEXT(D13:D65535)*1)")
    For i = Range("D65536").End(xlUp).Row To 13 Step -1
     If Not IsNumeric(Range("D" & i)) And Range("D" & i) <> "" Then Range("C" & i) = n: n = n - 1
    Next
     
    --
    isabelle
     
    • Marked as answer by CABGx3 Friday, June 24, 2011 1:45 AM
    Friday, June 24, 2011 1:44 AM
  • Hi isabelle

    I made small mod to your code.....

    n = 1000 + Evaluate("SUMPRODUCT(ISTEXT(D13:D65535)*1)") 'Starts with #1001 in C13

    This allows it to start with the number 1001 in C13 instead of just 1..........

    Other than that, it worked perfectly.  I'll check your new addition out tomorrow as it's my bedtime now here in St. Petersburg, Florida.

    Many thanks again

    Vaya con Dios,

    Chuck, CABGx3

     


    Chuck, CABGx3
    Friday, June 24, 2011 1:49 AM
  • please my mistake, i said "hola Bélgica" because i have thought of music band "Vaya con Dios" from belgium
    so good night Petersburg, Florida
     
    --
    isabelle (from Québec, Canada)
     
    Le 2011-06-23 21:49, CABGx3 a écrit :
    it's my bedtime now here in St. Petersburg, Florida.
     
    Friday, June 24, 2011 6:18 AM
  • Not a problem isabelle, I didn't understand it anyway.  I only use the "Vaya con Dios" sign-off in memory of the song of that name by Les Paul and Mary Ford.
    Thanks again for all your help.
     
    Vaya con Dios,
    Chuck, CABGx3

    Chuck, CABGx3
    Friday, June 24, 2011 2:30 PM