Need to find last cell in a column

  • Thread starter Thread starter Robert Adach
  • Start date Start date
R

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.

(e-mail address removed)
 
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.
 
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
 
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.

(e-mail address removed)
Click to expand...

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
 
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
Click to expand...

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.
 
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 said:
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
Click to expand...

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
 
Back
Top