Hi guys, bit of a long shot but - i was wondering if you guys could help me, im making a project at college where i have to make a system on excel

and i really need your help!!!

basically i need to know a code for an OK button for a userform so that when data is entered into a user form, it gets added to the next row in the list on a spreadsheet - the current code i have is

Range(col_Pos + "A34") = NewProductsForm.TextBox1.Value
Range(col_Pos + "B34") = NewProductsForm.TextBox2.Value

as you can see when ok is pressed the data will go to A34 and B34

basically i need to know a code so that the next time data is entered it will go to the next free row, e.g A35 and B35 then A36 then B36 etc - onwards... (if the cells above are in use)

hope you understand what i mean!!

and thanks ALOT!!

what does col_Pos do?

Can you do it so you can add a string and a integer together?

eg.

Range(A + int);

This way all you have to do is int = int + 1

Not done visual basic in absolutely ages so dont know what you can do with it

I'm not a VBA expert, but col_Pos looks like a variable

Why not store the data in an array, then simply add the new data at the next free location

cheers ill try that, think it stands for column position

any other ideas?

It a bit difficult to say without seeing the full code

do you have something like

"Dim Col_pos As Long"

It could stand for column position, depends on who originally wrote the code, I dont see any comments

I was thinking 'Column Position' too.

There are loads of tips for VB/Excel coding in the link below

[url="http://www.angelfire.com/biz7/julian_s/julian/julians_macros.htm"]>>click me........you know you want to<<[/url]

If you're still stuck then shout up !


Rich

thanks alot for your replys guys!! seriously appreciate it!! this needs to be handed in tomorrow so i need to get this working so i can get some of the other stuff done

[url="http://www.putfile.com/pic.php?img=5423578"][img]http://img2.putfile.com/thumb/5/12911455148.jpg[/img][/url]

this screen shows the form and where the information should go, basically i need a code so that when ok is clicked the new data wont just go to cell A34 and B34 next time something it added i need it to go to the next free cells

and heres a print screen of the original code

[url="http://www.putfile.com/pic.php?img=5423579"][img]http://img2.putfile.com/thumb/5/12911455145.jpg[/img][/url]

the easiest way is something like this ........


Set a counter out of the way and +1 to it every time an item is added.
( it would be helpful to declare this as a variable rather than just reading the range. it'll make things a whole lot easier. We'll call this variable Roger for this example ! )

Then, instead of saying
[quote]Range(col_Pos + "A34") = NewProductsForm.TextBox1.Value
Range(col_Pos + "B34") = NewProductsForm.TextBox2.Value
[/quote]

use the code A&Roger instead of A34, B&Roger instead of B34.

on the CommandButton1.click subroutine add a line saying Roger = Roger+1

it's a basic way of doing it ( no pun intended ), but perfectly acceptable.

Good luck !"


Rich


Don't forget !

Declare Roger as a public variable and you can stash the counter anywhere on any form !

thanks alot for your help matey!!

managed to get it sorted!

the code used was

Private Sub CommandButton1_Click()
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
LastRow = LastRow + 1
End If
Range("A" & LastRow) = NewProductsForm.TextBox1.Value
Range("B" & LastRow) = NewProductsForm.TextBox2.Value
NewProductsForm.Hide
Unload Me
End Sub

easy !

Nice one mate.

thanks again to every one who helped!

(can be closed if needed)