PDA

View Full Version : Solved: Insert specified number of rows



lukes123
12-14-2008, 12:10 PM
Hi

I am creating quite a simple checkout system, whereby when the user has finished, all the information goes into a new sheet. This needs to be done by inserting new rows as t how many items the person has added to the basket (a table which holds the items bought). When finished, all the data will go onto the new sheet by copying and pasting, but the information will, be at the table. I need to be able to get VBA to insert the number of rows according to how many items is in the basket. I have set up a cell which calculates this, and I thought that vba might be able to use this to work out how many rows to add to the new sheet. If I don't add this code, and simply tell it to insert one row, it just pastes over all the items that are currently in the table.

Can anyone help me? Sorry it is so wordy but I try to provide as much info as possible just in case someone can think up a different way of doing it.

Thanks
Luke Stratton

lucas
12-14-2008, 12:42 PM
Hi Luke, welcome to the board. Would it be possible for you to upload your workbook without any personal/business info so we can take a closer look at what you are doing?

click on post reply at the bottom left of the last post and when the page loads add your message and then scroll down and look for the button that says "manage attachments"

lukes123
12-14-2008, 12:49 PM
Attached, is the file. If you look at the vb, there should be a form which is used as a checkout.

Don't worry about any of the other sheets, except salesFormTemp and Data.
Thanks
Luke Stratton

lucas
12-14-2008, 01:00 PM
Luke, I don't have office 07......could you upload one that you save as an earlier version.....2003.

lukes123
12-14-2008, 01:04 PM
Sorry, I didn't know. Here it is, but it says 'Some items are not compatible' and it came up with my items table. That is not really needed for the vb so it doesn't matter.
Thanks
Luke Stratton

lucas
12-14-2008, 01:13 PM
A few clarifications:

I am creating quite a simple checkout system, whereby when the user has finished, all the information goes into a new sheet
The user picks a product in cell G4 of sheet SalesFormTemp...
the info goes into a new sheet ??? what sheet, a new one?

lukes123
12-14-2008, 01:17 PM
Everything is controlled by userform1. The product is selected, which is automatically transferred to G4. When the user has selected everything, they click the Finish Sale button which needs to transfer everything to the Sale Log sheet.

What I need, is for it to make new rows in this sheet, and then it should copy and paste. Vba needs to know how many rows to create in accordance to how many items are in the basket.
Thanks
Luke Stratton

lucas
12-14-2008, 01:34 PM
Sales Log? and which userform? userform1?

lukes123
12-14-2008, 01:37 PM
I said in the post above userform1! And the sheet is sale log not sales log. There are quite a lot of sheets so you may have to scroll.
Thanks
Luke Stratton

lucas
12-14-2008, 01:45 PM
Luke, we have a huge winter storm moving in here and I will have to get back to this when I can.

I would say that there are many problems with the code that exists. Your use of select is prevelant throughout all of the modules in the workbook and the use of the SalesFormTemp is questionable. Why put it in that sheet at all from the userform.....why not input it directly into the sales log sheet.

Evaluating your project has given rise to many questions and some of them will not be easy to overcome.

Just glancing through the code I find many lines of red code indicating syntax error or some other kind of error......

That doesn't mean it can't be made to work but it will take some time and I just don't have it right at the moment.......

Just a quick example, I couldn't even get userform1 to run without commenting out some of your code........

lukes123
12-14-2008, 01:58 PM
Thanks a lot for your time in helping me.

The form works fine for me, I admit that I did save the document in the middle of testing a line of code, which I should have replaced.

The sales log, is a log of all the purchases made over a period of time, and the salesFormTemp sheet is for calculating and temporarily storing the data, functions, formulas, and calculations.
The reason I do not directly submit everything to the sale log sheet, is because I need a temporary store for the basket.

As you can probably see, my knowledge of Vba is not great and I only know a few features, so expect some dodgy lines of code.

Thanks again for your time.

Luke Stratton

lucas
12-14-2008, 02:03 PM
Luke, I have some serious winterizing to take care of today. I will be frozen in soon and will have time to look at this while sitting by a warm wood stove. Your immediate problem should not be that hard to resolve but will take a while to organize........

Very possible that someone else will come along and help you with this before I get back to it but don't give up either way......

mdmackillop
12-14-2008, 02:37 PM
Keep warm Steve!
:coffee:

Private Sub FinishSale_Click()
'Reset Form
ComboBox1.Value = "Please Select..."
TextBox2.Value = ""
TextBox1.Value = "1"
TextBox3.Value = ""
'Copy data from basket to sale log
Sheets("salesFormTemp").Range(Cells(19, 1), Cells(19, 1).End(xlDown)).Resize(, 5).Cut

Sheets("Sale Log").Range("A6").Insert Shift:=xlDown

'Clear Basket
Call CommandButton2_Click
'Show confirmation message
MsgBox "Sale Complete!", vbInformation, "Notification"
End Sub

lukes123
12-14-2008, 02:47 PM
Wow thanks a lot.

Could you explain these lines of code?
Sheets("salesFormTemp").Range(Cells(19, 1), Cells(19, 1).End(xlDown)).Resize(, 5).Cut

Sheets("Sale Log").Range("A6").Insert Shift:=xlDown

Thanks
Luke Stratton

mdmackillop
12-14-2008, 02:57 PM
Sheets("salesFormTemp").Range(Cells(19, 1), Cells(19, 1).End(xlDown))
Sets a range starting at Row 19 Column 1 to the bottom of the selection

.Resize(, 5)
Increases the width to 5 columns

.Cut
says it all

Sheets("Sale Log").Range("A6")
Address to paste result of cut

.Insert Shift:=xlDown
Inserts space at selected cell to avoid overwriting

lukes123
12-14-2008, 03:05 PM
Thanks a lot I really appreciate it. If I have any more problems, I know where to come!

Thanks
Luke Stratton

lukes123
01-06-2009, 10:31 AM
I have used the code that mdmackillop sent. It works fine but now it says whenever I run it:
The operation is attempting to shift cells in a list on your worksheet.

Then it stops and asks me to debug.

Whats the problem here?
Thanks

Luke Stratton

lucas
01-06-2009, 11:19 AM
I downloaded your file at post#5 and put Malcolm's code in it. It worked fine. Have you changed the workbook?

lukes123
01-06-2009, 11:38 AM
I have changed it quite a bit actually. I'll send you the latest in a minute.
Thanks

lukes123
01-06-2009, 11:44 AM
Here is the latest. The password for the completely unsecure security system which I made :-) is 'vba'.

lucas
01-06-2009, 11:51 AM
Works fine on my end. You will have to describe the exact steps to be taken to expose your error.

I would also suggest that you add Option Explicit to the top of every userform module and standard module and thisworkbook module. It will reveal many problems with your code.

lukes123
01-06-2009, 11:56 AM
I've taken a screenshot at the failiure point.

Thanks

lucas
01-06-2009, 01:00 PM
I understand that you got an error. I ran the same form and did not get an error so you are doing something different than I am.

Steps....what did you do, exactly.....step by step so we can see when and why the error is triggered.

lukes123
01-06-2009, 03:23 PM
I opened the form, selected the item from the menu. Clicked Add with 1 in the quantity box, thn clicked finish sale. This also happens if you end the form with this error and attempt to paste the data manually.

Thanks
Luke

lukes123
01-07-2009, 03:46 PM
Do you have any suggestions yet?

Thanks
Luke

lucas
01-10-2009, 01:45 PM
On your sales log sheet you have a "list" That is the problem. I tried deleting it but then when I open your workbook it locks up excel and I have to go to task manager to get out of it.......I get lots of out of memory errors just moving around in the workbook......

You really need to redesign your workbook and you should start fresh with Option explicit at the top of every module, userform, sheet code module.

I have attached a picture of the offending list on your sales log sheet. It's the blue line and you can select it but I don't know how to get rid of it.

Notice the debug error directly references this "list"

lukes123
01-13-2009, 09:39 AM
OK Thanks. I have managged to solve the problems and the workbook seems to be opening fine. I have removed the list and it now doesn't come up with that error. I now get another problem. I now get an error that comes up when I run the 'Finish Sale' button. I have attached a screenshot since I can't copy and paste it.

If this goes on any longer and I keep getting errors, then I will re-make the whole thing but if this problem can be fixed then this is all that is wrong.

Thanks for your help.

Luke

lucas
01-13-2009, 10:11 AM
Luke, I hate to tell you but your workbook is laden with problems. I got an out of memory error every time I ran it when it was closed. You don't use Option Explicit at the top of your modules and you use select for everything you do.

I would take a look at it if you would post your new workbook. You said you fixed the last problem......do you want me to download it and fix that so I can look at this new problem......I think you expect too much.

lukes123
01-13-2009, 10:18 AM
Hi

Thanks again. I get no memory errors on mine but anyway - here is the workbook. What do you suggest I use instead of select, I don't know much more about VBA but if you couldoint me in the right direction then I could learn?

Thanks

Luke

lucas
01-17-2009, 12:17 PM
Luke, I have grown frustrated with you because just in this latest post you cite an error when you hit the finish button.

In post 24 you cite the same problem. I give you an idea that fixes it in post 26. When I did that I got no error after that so you had to have done something else to the workbook to cause the new error.......you don't tell me what it is, you just say you have a new error.

I don't have the time or the inclination to chase down your new problem, especially if you won't tell me what changes you made to cause it.....

On the select, just an example from your code. this part:

Range("B19").Select
'Set currency on price column
Selection.NumberFormat = "?#,##0.00_);(?#,##0.00)"

Should look something like this:
Sheets("salesFormTemp").Range("B19").NumberFormat = "?#,##0.00_);(?#,##0.00)"

No selects necessary and they slow things down and make your screen flash. The movements to select use memory and time and you can do things to ranges that you don't select......

lukes123
01-17-2009, 12:45 PM
OK Thanks,

I'll try and condense the code as to the example you give and try and get rid of most of the selects.

Luke

Aussiebear
01-17-2009, 03:14 PM
Luke, you have been given some very good advice here regarding the use of "Option Explicit" at the start of your code. Please try to do so.

lukes123
01-17-2009, 03:16 PM
OK, i'll put this at the top of every page of code, modules etc.

Thanks
Luke

lukes123
01-18-2009, 11:15 AM
Hi
Sorry I didn't listen to you before. I have now got rid of most of the selects, as you state in the example. I have also put Option Explicit at the top of every page of code I can find. If there are any that don't have it then I must have missed them. I still get the error but I was not expecting it to vanish anyway. If I still need to do anything that you want me to then please say.

The only thing I don't understand is that the message is intermittent, I don't know what would be different about the attempts.

Thanks for your time.

Luke

lukes123
02-03-2009, 10:37 AM
Hi

Thanks for your time in helping me.

I have re-uploaded the file since it doesn't seem to be here after the re-hosting.

Thanks

Luke