On 2002-05-12 07:22, delboy74 wrote:
This seems like a really simple thing to do, but I can't seem to do it without specifying an exact cell to move to.
Basically, I am creating a form to collect data and store it on an excel sheet. Each time the user presses the "continue" button on the form, it should go to a new row in the sheet storing the data, and input the new row of info. I can't work out how to get it to check for the next available row and then move to it to store the data.
Sorry if this sounds long winded, but your help would be appreciated.
Regards,
Del
Hi Del, If I understand you correctly then
you have data basically like;<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=6><FONT COLOR=WHITE>
Microsoft Excel - Book2_______________Running: xl2000 : OS = Windows (32-bit) 4.10</FONT></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=6>(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>A1</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >
=</TD><TD COLSPAN=3 BGCOLOR=White>Data1</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
D</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
E</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
1</TD><TD BGCOLOR=#C0C0C0 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FF0000>Data1</FONT></TD><TD BGCOLOR=#C0C0C0 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#0000FF>Data2</FONT></TD><TD BGCOLOR=#C0C0C0 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#008000>Data3</FONT></TD><TD BGCOLOR=#C0C0C0 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Data4</FONT></TD><TD BGCOLOR=#C0C0C0 ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Data5</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
2</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FF0000>Criteria1</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#0000FF>Info1</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#008000>Blah1</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Test1</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Trend1</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FF0000>Criteria2</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#0000FF>Info2</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#008000>Blah2</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Test2</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Trend2</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
4</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FF0000>Criteria3</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#0000FF>Info3</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#008000>Blah3</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Test3</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Trend3</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
5</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FF0000>Criteria4</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#0000FF>Info4</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#008000>Blah4</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Test4</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Trend4</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
6</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FF0000>Criteria5</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#0000FF>Info5</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#008000>Blah5</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Test5</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Trend5</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
7</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FF0000>Criteria6</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#0000FF>Info6</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#008000>Blah6</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Test6</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Trend6</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
8</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FF0000>Criteria7</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#0000FF>Info7</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#008000>Blah7</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Test7</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Trend7</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
9</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FF0000>Criteria8</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#0000FF>Info8</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#008000>Blah8</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Test8</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Trend8</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
10</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FF0000>Criteria9</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#0000FF>Info9</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#008000>Blah9</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Test9</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Trend9</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
11</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FF0000>Criteria10</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#0000FF>Info10</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#008000>Blah10</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Test10</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Trend10</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
12</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FF0000>Criteria11</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#0000FF>Info11</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#008000>Blah11</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Test11</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Trend11</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
13</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#FF0000>Criteria12</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#0000FF>Info12</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#008000>Blah12</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Test12</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Trend12</FONT></TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>
14</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#0000FF><< Next Row</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=6><U>Sheet1</U></TD></TR></TABLE><FONT COLOR=#339966>To see the formula in the cells just click on the cells hyperlink</FONT><FONT COLOR=#339966 SIZE=1>The above image was automatically generated by [HtmlMaker V1.20]</FONT><FONT COLOR=#339966 SIZE=1>If you want this code,<A HREF=mailto:corosuke@chan.co.jp>click here</A> and Colo will email the file to you.</FONT><FONT COLOR=#339966 SIZE=1>This code was graciously allowed to be modified: by<A HREF=mailto:ivanmoala@xtra.co.nz>Ivan F Moala</A> All credit to Colo</FONT></CENTER>
In order to get the Last row assuming
1) Data starts in Column A as above
2) Data is continuous
Then something like this will give you the
Next blank row to input data into.<pre/>
Dim dNxtRow As Long
dNxtRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(dNxtRow, 1) = "Your Data"</pre>
_________________
Kind Regards,<font size=+2><font color="red"> I<font color="blue">van<font color="red"> F M</font color="blue">oala</font>
<MARQUEE>Have a Nice day
</MARQUEE>
This message was edited by Ivan F Moala on 2002-05-12 11:25