Number series at end of column macro?

[jimb]

Please help...I need an excel macro that will do the following:

For example, I have Column A (contains the word “ADD” repeating),
Column B(alphanumeric id), all filled with data. The data ends in each
of these columns at row x. Column C will contain a number series based
on the date and ends at row y < x. I would like a macro that finds
where Column C ends, and after the last cell in Column C begins
filling it in with a series starting at z, but the series will be
formatted as follows: <current week’s Monday date in yyyy-mm-dd
format> TEXT-# For example, 2011-11-14 TEXT-0.

By the way, I know there is a non-macro way of doing it, but I need
this to be in a macro.

I have a link Google docs link below to a sample with the before macro
and after macro tabs so show what it is I am looking for.

https://docs.google.com/open?id=0B9dOoyyHY-PyNGVjZjVkYjQtOTU4MS00NDVmLTg3MjAtNTI2YTUzODI5ZTY3

Thank you in advance!
Jim

 

[Don Guillett]

Should do it. Correct any word wrap

Option Explicit
Sub FillInDatesSAS()
Dim thismonday As Date
Dim lra As Long
Dim lrc As Long
lra = Cells(Rows.Count, "a").End(xlUp).Row
lrc = Cells(Rows.Count, "c").End(xlUp).Row + 1
thismonday = Date - Weekday(Date, vbMonday) + 1
Cells(lrc, "c").Value = _
Format(thismonday, "yyyy-mm-dd") & " TEXT-0"
Cells(lrc, "c").AutoFill Destination:= _
Range(Cells(lrc, "c"), Cells(lra, "c"))
End Sub

 

[isabelle]

hi Jim,

txt = Format(DateSerial(Year(Now), Month(Now), Day(Day(Now) - (Weekday(Now) - 3))), "yyyy-mm-dd") & "TEXT-0"

With ActiveSheet
firstcell = .Range("C" & Rows.Count).End(xlUp).Row + 1
lastcell = .Range("A" & Rows.Count).End(xlUp).Row
.Range("C" & firstcell & ":C" & lastcell) = txt
End With

 

[Don Guillett]

hi Jim,

txt = Format(DateSerial(Year(Now), Month(Now), Day(Day(Now) - (Weekday(Now) - 3))), "yyyy-mm-dd") & "TEXT-0"

With ActiveSheet
   firstcell = .Range("C" & Rows.Count).End(xlUp).Row + 1
   lastcell = .Range("A" & Rows.Count).End(xlUp).Row
  .Range("C" & firstcell & ":C" & lastcell) = txt
End With

OP wanted number at end INCREMENTED.

 

[isabelle]

oops i forgot that part, but you have given the solution.
in my example this would be:

txt = Format(DateSerial(Year(Now), Month(Now), Day(Day(Now) - (Weekday(Now) - 3))), "yyyy-mm-dd") & "TEXT-0"

With ActiveSheet
firstcell = .Range("C" & Rows.Count).End(xlUp).Row + 1
lastcell = .Range("A" & Rows.Count).End(xlUp).Row
.Range("C" & firstcell) = txt
.Range("C" & firstcell).AutoFill Destination:=.Range("C" & firstcell & ":C" & lastcell)
End With



--
isabelle



Le 2011-11-23 09:45, Don Guillett a écrit :