VBA question
 

I have a list of data that changes everytime someone runs their excel
spreadsheet. ( It is populated by another software taht we us for our
database.) I want to be able to find the first blank cell (where the
list of data ends) and enter a calculation there. How can I do this
within vba? I want to find the first blank cell and enter a formula
there, I have tried everything!! Thanks in advance!

VBA question
 

On Oct 9, 2:43 pm, "aggiechick717" wrote:
I have a list of data that changes everytime someone runs their excel
spreadsheet. ( It is populated by another software taht we us for our
database.) I want to be able to find the first blank cell (where the
list of data ends) and enter a calculation there. How can I do this
within vba? I want to find the first blank cell and enter a formula
there, I have tried everything!! Thanks in advance!

Assuming that there will never be an empty cell until the bottom of
your list, and that your data is in column A (if not - adapt
accordingly):

application.worksheetfunction.counta(activesheet.r ange("A:A")) + 1 is
the row number of your first empty cell. meaning that:

activesheet.range("A" &
application.worksheetfunction.counta(activesheet.r ange("A:A")) +
1).value = YOUR FORMULA

HTH

AR

VBA question
 

Range("A1").End(xlDown).Offset(1).FormulaR1C1 = "=Sum(R1C:R[-1]C)"

This will find the next empty cell in Column A then enter a Sum formula to
sum all rows above it.
--
Charles Chickering

"A good example is twice the value of good advice."


"aggiechick717" wrote:

I have a list of data that changes everytime someone runs their excel
spreadsheet. ( It is populated by another software taht we us for our
database.) I want to be able to find the first blank cell (where the
list of data ends) and enter a calculation there. How can I do this
within vba? I want to find the first blank cell and enter a formula
there, I have tried everything!! Thanks in advance!

VBA question
 

Sub Lastrow()

Dim l_column As String

Range(l_column & "65535").Select
Selection.End(xlUp).Select

End Sub

Hope this helps,

Thanks

Simon

aggiechick717 wrote:
I have a list of data that changes everytime someone runs their excel
spreadsheet. ( It is populated by another software taht we us for our
database.) I want to be able to find the first blank cell (where the
list of data ends) and enter a calculation there. How can I do this
within vba? I want to find the first blank cell and enter a formula
there, I have tried everything!! Thanks in advance!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1

VBA question
 

Sorry, you will need to define l_column!



smw226 wrote:
Sub Lastrow()

Dim l_column As String
l_column = "A"
Range(l_column & "65535").Select
Selection.End(xlUp).Select

End Sub

Hope this helps,

Thanks

Simon

I have a list of data that changes everytime someone runs their excel
spreadsheet. ( It is populated by another software taht we us for our
database.) I want to be able to find the first blank cell (where the
list of data ends) and enter a calculation there. How can I do this
within vba? I want to find the first blank cell and enter a formula
there, I have tried everything!! Thanks in advance!

--
Message posted via http://www.officekb.com

VBA question
 

That sounds good, but when I try to do it it gives me an error. Any
ideas? I think this will work as long as I can code it in right!!
Thanks!


Charles Chickering wrote:
Range("A1").End(xlDown).Offset(1).FormulaR1C1 = "=Sum(R1C:R[-1]C)"

This will find the next empty cell in Column A then enter a Sum formula to
sum all rows above it.
--
Charles Chickering

"A good example is twice the value of good advice."


"aggiechick717" wrote:

I have a list of data that changes everytime someone runs their excel
spreadsheet. ( It is populated by another software taht we us for our
database.) I want to be able to find the first blank cell (where the
list of data ends) and enter a calculation there. How can I do this
within vba? I want to find the first blank cell and enter a formula
there, I have tried everything!! Thanks in advance!

VBA question
 

Thank you all, I got it to work!!!




smw226 via OfficeKB.com wrote:
Sorry, you will need to define l_column!



smw226 wrote:
Sub Lastrow()

Dim l_column As String
l_column = "A"
Range(l_column & "65535").Select
Selection.End(xlUp).Select

End Sub

Hope this helps,

Thanks

Simon

I have a list of data that changes everytime someone runs their excel
spreadsheet. ( It is populated by another software taht we us for our
database.) I want to be able to find the first blank cell (where the
list of data ends) and enter a calculation there. How can I do this
within vba? I want to find the first blank cell and enter a formula
there, I have tried everything!! Thanks in advance!

--
Message posted via http://www.officekb.com