Finding Next Blank Cell in Blank Row

[Nick Wakeham]

Using Excel 2007 - I have a workbook with two worksheets. The front sheet
has a list of headers along the top such as date and then some data that is
going to be filled in down the columns. The second sheet is the work-out
sheet with the formulas that is going to work out the figures to go into the
first sheet. However, what I want to do to make it simple for the people
using the sheet, is to have one simple line of formulae that they type into
each week and the solutions to those formulae are then placed in the first
sheet but in the next available cell going down the rows. i.e. this week the
solutions would go into Row 3 Columns A, B, C, D; next week they would go
into Row 4 Columns A, B, C, D - all this without them having to do anything
other than type in the data on the second sheet. I know all about the
Worksheet! formula, etc but it is the looking for the next blank row
business that puzzles me.

Is this possible?

Nick

 

[Don Guillett]

You don't say where the input data row is so I use row 2. You could change
to
Cells(activecell.row, "a").Resize(, 4).Value

This assumes that you are executing from the input sheet and are placing the
values of the formulas in the last row +1 of the destination sheet. Notice
the placement of the dots (look in the vba help index for WITH).

Sub copyinputlinetonextrow()
With Sheets("destinationsheetnamehere")
lr = .Cells(Rows.Count, "a").End(xlUp).Row + 1
..Cells(lr, "a").Resize(, 4).Value = _
Cells(2, "a").Resize(, 4).Value
End With
End Sub

 

[Nick Wakeham]

Many thanks, That's exactly what I wanted

Nick

You don't say where the input data row is so I use row 2. You could change
to
Cells(activecell.row, "a").Resize(, 4).Value

This assumes that you are executing from the input sheet and are placing
the values of the formulas in the last row +1 of the destination sheet.
Notice the placement of the dots (look in the vba help index for WITH).

Sub copyinputlinetonextrow()
With Sheets("destinationsheetnamehere")
lr = .Cells(Rows.Count, "a").End(xlUp).Row + 1
.Cells(lr, "a").Resize(, 4).Value = _
Cells(2, "a").Resize(, 4).Value
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

Glad to help