NUMBER SERIES FILL ON CONDITION 1

[Evil8]

I am looking for help on I believe it would be an If formula. This will be going into a VBA mod if that makes any difference.

Starting at cell "A5" number rows begining with 1 until there are no more entries in column B.

Column B can be NAMED EENames if that makes it easier.

Thank you for your help.

 

[xlbo]

lRow = Range("EENames").cells.count

with Range("A4")
.value = 1
.AutoFill Destination:=Range("A4:A" & lRow), Type:=xlFillSeries
end with

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Have you tried AutoFill?

have you tried turning on your macro recorder?

What code do you have and where is it failing?

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[Evil8]

Skip, what I have so far is just using the autofill to fill in the possible max of rows which would be 50.

Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A2:A51").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Stop:=50, Trend:=False

Using this the end user will have to remove rows before sending the spreadsheet. I want to try to eliminate this step and define the fill based on how many names are in column B.

So xlbo, your saying I should use the count to count the names in column B and then use that count number as the stop number in the series fill?

Thanks.

 

[AnotherHiggins]

There are a couple of FAQs here on Tek-Tips dedicated to various ways to determine the last row.

faq707-2115
faq707-2112

I'll second Skip on using Macro Recorder. It is a wonderful tool. Tools > Macro > Record New Macro. That alone would get you most of the way there - maybe all the way, depending on the steps you use when recording.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.