excel vba programming code

[marthijn99]

when i try to make an vba code in excel vba extension at the moment i added
this line to the code of a button:

Range("A1").End(xlDown).Offset(1, 0).Select


it says there is an error but i did exactly what they said on this video
https://www.youtube.com/watch?v=CzP8nO9UVvY
its explained just after 25:12 minutes

 

[Tookeri]

I tried the code and it works without errors. Are you sure it's that line it complains about?

This shorter one works to: Range("A1").End(xlDown).Select

 

[marthijn99]

I tried the code and it works without errors. Are you sure it's that line it complains about?

This shorter one works to: Range("A1").End(xlDown).Select

can it be because of spaces before it intstead of langspaces (like when you use tab)

okay with the short one it works just fine

 

[Tookeri]

Great! Please mark the thread as solved

 

[strollin]

The short version doesn't do the same thing as the long version. The short version selects the last value in column A while the long version selects the next empty cell after the last value in column A. (This assumes there are no empty cells in column A as it will stop on the first empty cell)

The code works for me but you have to make sure that you are running it against a sheet that has at least one value in column A. If you run it against a column that has no values in it then it will fail because it will go to the last row in column A and then try to go to 1 row down which causes it to fail.

 

[Tookeri]

Good points! If the short version produces a logical error, that may be even worse than a real one. Thanks!

 

[marthijn99]

Good points! If the short version produces a logical error, that may be even worse than a real one. Thanks!

so do i makr it as solved already or can i aks more questions in this one

(next question is: how do i solve this error ? with the short code it does like alraedy said by someone else it goes to the verry last colmn and with the long one it does this error(shown below but when i click debug it shows me the code (Range("A1").End(xlDown).Offset(1, 0).Select) is wrong

 

[Tookeri]

You can ask how many questions you want

If you want the next empty cell you can try the shorter version that doesn't produce an error. And then move one cell down and see if that works:

Range("A1").End(xlDown).Select
ActiveCell.Offset(1, 0).Select 'move one cell down

 

[strollin]

We would need to see what the spreadsheet looks like and what column is "Cell1".

 

[marthijn99]

You can ask how many questions you want

If you want the next empty cell you can try the shorter version that doesn't produce an error. And then move one cell down and see if that works:

Range("A1").End(xlDown).Select
ActiveCell.Offset(1, 0).Select 'move one cell down

We would need to see what the spreadsheet looks like and what column is "Cell1".

okay now i have an error message at: ActiveCell.Offset(1, 0).Select

+ here are the screen shots of what i think are the spread sheets ( sorry i dont know engish verry well )

 

[strollin]

It looks like you are trying to pull data entered into the text fields on the form and attempting to save them in a single row across the spreadsheet. If that's what you are trying to do then the code as written won't work unless there is at least one row of data already in the sheet. If you add a header row as the first row in your spreadsheet, it might work.

 

[marthijn99]

It looks like you are trying to pull data entered into the text fields on the form and attempting to save them in a single row across the spreadsheet. If that's what you are trying to do then the code as written won't work unless there is at least one row of data already in the sheet. If you add a header row as the first row in your spreadsheet, it might work.

do you mean that i need to fill row A or 1 because i did cell 1A and it didnt work

 

[strollin]

Columns use letters, rows use numbers. Yes, I meant for you to put some text or something in cell A1. However, I still don't know what range "Cell1" refers to which is the range used in the formula. When you say it didn't work, did you get the same error?

 

[marthijn99]

Columns use letters, rows use numbers. Yes, I meant for you to put some text or something in cell A1. However, I still don't know what range "Cell1" refers to which is the range used in the formula. When you say it didn't work, did you get the same error?

okay sorry for the inconvinece but this is solved thankyou and the other guy too

next question is why when i click the save button it saves row 3 and when i try to save another one it saves over row 3 again (i know why it saves on row 3 because i collored the other 2 rows in and filled them in)

 

[strollin]

I think I've helped you all I can via this method. Any chance you could save the excel file somewhere where I could download it and look at it? Do you have a DropBox account?

 

[marthijn99]

I think I've helped you all I can via this method. Any chance you could save the excel file somewhere where I could download it and look at it? Do you have a DropBox account?

nope not yet will make one now and be back with the file in 3 min

 

[marthijn99]

I think I've helped you all I can via this method. Any chance you could save the excel file somewhere where I could download it and look at it? Do you have a DropBox account?

okay i cant make an dropbox account because i cant use my bank account

but here is my google drive page with the file

https://drive.google.com/file/d/0B95N5nqw19WoODRZcGgtY2FkN1k/edit?usp=sharing

 

[strollin]

I downloaded your worksheet and added the text 'abc' to cell A1 and the text 'def' to cell A2 then ran your form. The first time it put the values from the form into row 3, the next time I ran it, row 4 and so on. Seems to be working for me.

As I said, you need to put at least a value in cell A1 in order for it to work or else it will attempt to go past the last row in the worksheet.

 

[marthijn99]

I downloaded your worksheet and added the text 'abc' to cell A1 and the text 'def' to cell A2 then ran your form. The first time it put the values from the form into row 3, the next time I ran it, row 4 and so on. Seems to be working for me.

As I said, you need to put at least a value in cell A1 in order for it to work or else it will attempt to go past the last row in the worksheet.

Okay so maybe its some thing i need to instal or chance in the settings ?