Visual Basic Help In Excel

[S.Kayani]

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!!

[Moffmo]

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

[vmail]

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

[S.Kayani]

cheers ill try that, think it stands for column position

any other ideas?

[vmail]

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

[Boro Sera]

I was thinking 'Column Position' too.

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

>>click me........you know you want to<<

If you're still stuck then shout up !

Rich

[S.Kayani]

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

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

[Boro Sera]

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

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

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

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 !

[S.Kayani]

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

[Boro Sera]

easy !

Nice one mate.

[S.Kayani]

thanks again to every one who helped!

(can be closed if needed)