How to get a macro to do what i want?

[Guest]

I have designed a form in Excel and the data that is inputted i want to be
able to save in a separate worksheet. The problem i am having is that
eveytime i record the macro, it over writes the same row each time (if that
makes sense). For example if i copied every cell in the form and paste them
into the database Row 1, i then want the new piece of information inputted
into the form to go into Row 2 and then Row 3 etc.... But it keeps going
into Row 1, it wouldnt move down the rows.

Hopefully this makes sense to someone and someone can give me some advise.

 

[Bob Phillips]

Calculate the last row

iLastRow = Cells(Rows.Count,"A").End(xlUp).Row
if iLastRow <> 1 Or Range("A1")<>"" Then
iLastRow = iLastRow + 1
End If

then use this in the data placement

Cells(iLastRow,"A").Value = myValue

etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thank you for that information but i do not understand the last bit about
Data Placement. Do i need to write that within the marco? If so then is it
underneath the End If?

Also you wrote calculate the last row, do i need to write that into the macro?

Sorry about being a pain!

Carrie

 

[Bob Phillips]

The first bit shows you have to calculate the next free row by identifying
the last filled row. Somewhere you mast have some code that copies the data
to a worksheet. I haven't seen the code, but maybe something like

Range("A1").Select
Selection.Formula = "ABC"

where ABC may be a variable, or another range, or just a string. I am
suggesting replacing that with

Range("A" & iLastRow).Value = "ABC"

--

HTH

RP
(remove nothere from the email address if mailing direct)