Need to find last cell in a column

[Robert Adach]

Heres what I am looking to do. Just can't figure how to do it.

I have a list of daa in column B, the number of cells will vary from day to
day due to the number of records created the prvious day.

What i would like to do is take the formula in cell a1 and copy it to only
those cells where there is a value in column B on the same row, for example
if column B has values in rows 1 through 35 then the formula should be
copied from a1 to a35,

There will never be a blank field within range for column b.

Any help would be appreciated.

[email protected]

[Earl Kiosterud]

Robert,

You may want to use "Extend list formats and formulas," if you have it in
your version of Excel, to have it automatically copy your formula as you add
rows.
--
Earl Kiosterud
www.smokeylake.com

"Robert Adach" <[email protected]> wrote in message
news:HD4He.1903$hC.294@lakeread06...
> Heres what I am looking to do. Just can't figure how to do it.
>
> I have a list of daa in column B, the number of cells will vary from day
> to day due to the number of records created the prvious day.
>
> What i would like to do is take the formula in cell a1 and copy it to only
> those cells where there is a value in column B on the same row, for
> example if column B has values in rows 1 through 35 then the formula
> should be copied from a1 to a35,
>
> There will never be a blank field within range for column b.
>
> Any help would be appreciated.
>
> [email protected]
>

[Robert Adach]

Thanks, but I am not adding rows...

Here is the background.

Every morning I receive a list of Help desk tickets opened by our call
center agents, becuase the number of contacts will vary from day to day, the
number of records (rows) will change.

Column A is blank by default
Column b is the name of the agent who submits the ticket.

the macro I currently has places a vlookup formula into cell a1 and I want
it to expand that to every row in column a where the correspoding row in
column b is not blank. I can get the macro to do a predefined number of
rows, but this will sometimes give me too many or to few formulas being
pasted into column a.

Hope this helps.

BTW I am using Excel 2003

What i would like to do is copy
"Earl Kiosterud" <[email protected]> wrote in message
news:[email protected]...
> Robert,
>
> You may want to use "Extend list formats and formulas," if you have it in
> your version of Excel, to have it automatically copy your formula as you
> add rows.
> --
> Earl Kiosterud
> www.smokeylake.com
>
> "Robert Adach" <[email protected]> wrote in message
> news:HD4He.1903$hC.294@lakeread06...
>> Heres what I am looking to do. Just can't figure how to do it.
>>
>> I have a list of daa in column B, the number of cells will vary from day
>> to day due to the number of records created the prvious day.
>>
>> What i would like to do is take the formula in cell a1 and copy it to
>> only those cells where there is a value in column B on the same row, for
>> example if column B has values in rows 1 through 35 then the formula
>> should be copied from a1 to a35,
>>
>> There will never be a blank field within range for column b.
>>
>> Any help would be appreciated.
>>
>> [email protected]
>>
>
>

[Dodo2u]

"Robert Adach" <[email protected]> wrote in
news:HD4He.1903$hC.294@lakeread06:

> Heres what I am looking to do. Just can't figure how to do it.
>
> I have a list of daa in column B, the number of cells will vary from
> day to day due to the number of records created the prvious day.
>
> What i would like to do is take the formula in cell a1 and copy it to
> only those cells where there is a value in column B on the same row,
> for example if column B has values in rows 1 through 35 then the
> formula should be copied from a1 to a35,
>
> There will never be a blank field within range for column b.
>
> Any help would be appreciated.
>
> [email protected]
>
>

You could add a button with the following code behind it:

Private Sub CommandButton1_Click()
Dim myRange As Range

formul = Worksheets("Sheet").Range("A1").Formula
Range("B1").Select
Selection.End(xlDown).Select
mc = ActiveCell.Address
Set myRange = Worksheets("Sheet1").Range("A1:" & mc)
myRange.Formula = formul

End Sub


--

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)

[Dodo2u]

Dodo2u <[email protected]> wrote in
news:[email protected]:

> "Robert Adach" <[email protected]> wrote in
> news:HD4He.1903$hC.294@lakeread06:
>
>> Heres what I am looking to do. Just can't figure how to do it.
>>
>> I have a list of daa in column B, the number of cells will vary from
>> day to day due to the number of records created the prvious day.
>>
>> What i would like to do is take the formula in cell a1 and copy it to
>> only those cells where there is a value in column B on the same row,
>> for example if column B has values in rows 1 through 35 then the
>> formula should be copied from a1 to a35,
>>
>> There will never be a blank field within range for column b.
>>
>> Any help would be appreciated.
>>
>> [email protected]
>>
>>
>
> You could add a button with the following code behind it:
>
> Private Sub CommandButton1_Click()
> Dim myRange As Range
>
> formul = Worksheets("Sheet").Range("A1").Formula
> Range("B1").Select
> Selection.End(xlDown).Select
> mc = ActiveCell.Address
> Set myRange = Worksheets("Sheet1").Range("A1:" & mc)
> myRange.Formula = formul
>
> End Sub
>
>

There is an error in this! I forgot to transfer the last cell address in
column B to an address in column A!
mc should not get the cell address but the cell row!

So, here is the correct code:


Private Sub CommandButton1_Click()
Dim myRange As Range

formul = Worksheets("Blad1").Range("A1").Formula
Range("B1").Select
Selection.End(xlDown).Select
mc = ActiveCell.Row
Set myRange = Worksheets("Blad1").Range("A1:A" & mc)
myRange.Formula = formul

End Sub


Sorry if I messed your worksheet up.


--

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)

[Dave Peterson]

One more...

Option Explicit
sub Testme01()

dim LastRow as long
with worksheets("sheet1")
lastrow = .cells(.rows.count,"B").end(xlup).row
.range("a2:A" & lastrow).formular1c1 = .range("a1").formular1c1
end with
end sub


Robert Adach wrote:
>
> Heres what I am looking to do. Just can't figure how to do it.
>
> I have a list of daa in column B, the number of cells will vary from day to
> day due to the number of records created the prvious day.
>
> What i would like to do is take the formula in cell a1 and copy it to only
> those cells where there is a value in column B on the same row, for example
> if column B has values in rows 1 through 35 then the formula should be
> copied from a1 to a35,
>
> There will never be a blank field within range for column b.
>
> Any help would be appreciated.
>
> [email protected]

--

Dave Peterson

[Jef Gorbach]

"Robert Adach" <[email protected]> wrote in message
news:PD5He.2160$hC.1004@lakeread06...
> Thanks, but I am not adding rows...
>
> Here is the background.
>
> Every morning I receive a list of Help desk tickets opened by our call
> center agents, becuase the number of contacts will vary from day to day,
the
> number of records (rows) will change.
>
> Column A is blank by default
> Column b is the name of the agent who submits the ticket.
>
> the macro I currently has places a vlookup formula into cell a1 and I want
> it to expand that to every row in column a where the correspoding row in
> column b is not blank. I can get the macro to do a predefined number of
> rows, but this will sometimes give me too many or to few formulas being
> pasted into column a.
>
> Hope this helps.
>
> BTW I am using Excel 2003
>
> What i would like to do is copy
> "Earl Kiosterud" <[email protected]> wrote in message
> news:[email protected]...
> > Robert,
> >
> > You may want to use "Extend list formats and formulas," if you have it
in
> > your version of Excel, to have it automatically copy your formula as you
> > add rows.
> > --
> > Earl Kiosterud
> > www.smokeylake.com
> >
> > "Robert Adach" <[email protected]> wrote in message
> > news:HD4He.1903$hC.294@lakeread06...
> >> Heres what I am looking to do. Just can't figure how to do it.
> >>
> >> I have a list of daa in column B, the number of cells will vary from
day
> >> to day due to the number of records created the prvious day.
> >>
> >> What i would like to do is take the formula in cell a1 and copy it to
> >> only those cells where there is a value in column B on the same row,
for
> >> example if column B has values in rows 1 through 35 then the formula
> >> should be copied from a1 to a35,
> >>
> >> There will never be a blank field within range for column b.
> >>
> >> Any help would be appreciated.
> >>
> >> [email protected]
> >>
> >
> >
>
>

Sounds like you already have the code working, but need to determine what
the last used row of column(a) is to end the loop:
finalrow = range("A65536").end(xlup).row

and change your loop to some akin to:
for rowindex = 2 to finalrow
range("B"&rowindex).formula = range("A1").formula
next