excel vba

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel or VBA

Excel or VBA

resolvedResolved · Medium Priority · Version 2010

Jay has attended:
Excel Intermediate course
Excel Advanced course

Excel or VBA

If i had a row with X amount of columns, and i want to "Spit" that row up into 2 maybe 3,4,5 ect. Keeping some of the columns the same and others which are numbers divided them by how many new rows you are making. (ie - if the total cost on the particular column on that row was £200, and i wanted to spit it into 4 rows, the total cost for each one will then be £50 per row)
If there a way i can make this happen with a click of a few buttons, either by marco or something else.

because inserting copying, pasting, deleting, changing, often takes a long time and is bound for mistakes.

Regards

Jay

RE: Excel or VBA

Hi Jay

Thanks for getting in touch. There are a couple of ways built in to Excel to do this depending on your criteria.

One way is with Text to Columns, which you'll find on the data menu. But if you want to include a calculation and the amount you divide by varies, that would be a VBA solution.

You may be able to use something like the COUNTBLANK function, to count the rows you want to divide by:

http://office.microsoft.com/en-gb/excel-help/countblank-HP005209028.aspx

If you provide a little more detail I might be able to give a more specific formula.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel or VBA

so lets say, this is what we can see :

Order Name Num of items cost per item total cost

271 Fred 4 25 £100


Lets say to be easy 2 Sell

so we would need to "Spit" the row

Order Name Num of items cost per item total cost

271 Fred 2 25 £50
271 Fred 2 25 £50

then i would move the 2 that have sold to the correct sheet using a macro, leaving me with to unsold correctly still there :

Order Name Num of items cost per item total cost

271 Fred 2 25 £50



So the transfer i need from a few click and by manually putting in the amount it needs to be spit up into. because we could have 8, 1 sells so would need 7 and 1, or might have 9 and 5 and 3 sell so would need 5,3,1.

What would you recommended, and can you help me get to it thanks

RE: Excel or VBA

Hi Jay

Thanks for your reply. I was following you until the last paragraph. I thought the value would be split equally among the amount of sales?

"we could have 8, 1 sells so would need 7 and 1, or might have 9 and 5 and 3 sell so would need 5,3,1."

If the value is £8 and 1 sell, the value would be £8?

If the value is £9 and 3 sell, the value would be £3?

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel or VBA

Ok so we have :

Order Name Number of items cost per item total cost

271 Fred 4 £25 £100

the number of items is 4, therefore if one was to sell there would be 3 left. therefore in order to "Spit" the row it would need to be transferred to

Order Name Number of items cost per item total cost

271 Fred 3 £25 £75
271 Fred 1 £25 £25

then i would transfer the 1 to a different sheet leaving me with 3 because one has sold.( i can do that using a macro)

so for the two example i gave it it would look like this :

Order Name Number of items cost per item total cost

271 Fred 8 £62.5 £500

To give me when "Spit" because we have sold 1 :

Order Name Number of items cost per item total cost

271 Fred 7 £62.5 £437.5 271 Fred 1 £62.5 £62.50

then i would transfer the one out...
and the last example would be :

Order Name Number of items cost per item total cost

271 Fred 9 £100 £900

To give me when "Spit" because we have sold 5 and 3 would be:

Order Name Number of items cost per item total cost

271 Fred 5 £100 £500
271 Fred 3 £100 £300
271 Fred 1 £100 £100

so the 5 and 3 would be transferred out leaving one behind (which again i can do)

hope this makes more sense now what im trying to do. im also using a random TC FYG

regards

Jay

RE: Excel or VBA

Hi Jay

I'm sorry but I'm really struggling with the logic of which rows are divided and when. I'm also not clear what the phrase "random TC FYG" means.

I'm guessing you would need to work with loops and a variable, perhaps picking up values to determine how many rows to copy down?

As a suggestion, try reading through our VBA Introduction / Intermediate manual and see if it gives you the hint you need to move your existing macro forward:

https://www.stl-training.co.uk/vba-training-manuals.php

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel or VBA

would it be possible to call me?

RE: Excel or VBA

Hi Jay

Thanks for your reply.

We can certainly talk through the options over the phone but it is worth clarifying that this sort of query takes us beyond the scope of the forum.

In these cases we look at your work and if we can identify a solution we will let you know scope, duration and associated costs.

If you would like to look into this further please do contact our enquiries team.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel or VBA

Well the only reason why i suggested to call, was because you don't understand my question...

Let me try and re-phase it another way...

Say i brought 6 eggs from a supplier "Peter", and i had this logged on an excel sheet the cost of the eggs was £3.00

Columns being :

Brought From
Product
Number of Products
Cost per item
Total cost

And the Eggs would lay out under the columns as :
"Peter" the person i brought it from
Eggs (The product)
6 (The amount of eggs i brought)
0.50p (£3.00 (Cost) / 6 (Number of eggs))
£3.00 Total Cost

So looking at an excel sheet it would lay out like :

Brought From Product Number of Products Cost per item Total cost

Peter Eggs 6 0.50p £3.00


Ok so this is how it is laid out.

Then say "Jay" & "Gary" brought 2 eggs each for £1.00 each (So he made no money on the buying and selling of the eggs)
Therefore
Jay brought 2 eggs costing £1.00
Gary Brought 2 eggs Costing £1.00

And he will be left 2 Eggs in stock still costing £1.00

so as some of these eggs are sold i need to "Split" up the row which currently looks like this:

Peter Eggs 6 0.50p £3.00

to

Peter Eggs 2 0.50p £1.00
Peter Eggs 2 0.50p £1.00
Peter Eggs 2 0.50p £1.00

(The reason why i would need to "split" the rows up is because i haven't sold the whole 6 in one go and i have different buyers.)

So once in this format i would transfer the 2x2 eggs brought by "Jay" and "Gary" to another sheet separate from the eggs that haven't sold yet

So my question is i need a quick way of splitting up :

This

Peter Eggs 6 0.50p £3.00

to

Peter Eggs 2 0.50p £1.00
Peter Eggs 2 0.50p £1.00
Peter Eggs 2 0.50p £1.00

please explain the part you do not understand because the principle behind it is very spit and would apply to the biggest and smallest companies who don't sell all there stock to one person.



RE: Excel or VBA

Hi Jay

Thanks for taking the time to clarify.

You will need some input from the user for the quantities purchased. You could retrieve this with an InputBox (within a loop) or custom UserForm. You will need to know the quantities and the unit price paid (assuming that can vary per sale).

These entries are best stored in variables, which your code can then iterate through a line dividing the quantity item by the amount specified. A final running calculation could then tally the remainder unsold.

The VBA techniques required would be: userforms, variables, FOR NEXT loops and calculations.

I hope that gives you some inspiration.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel or VBA

I am the only user, i input the products brought, and split the rows when sold.

Then ...

You could retrieve this with an InputBox (within a loop) or custom UserForm. You will need to know the quantities and the unit price paid (assuming that can vary per sale).

These entries are best stored in variables, which your code can then iterate through a line dividing the quantity item by the amount specified. A final running calculation could then tally the remainder unsold.

The VBA techniques required would be: userforms, variables, FOR NEXT loops and calculations.

...this part

i need some help in as im really unsure

RE: Excel or VBA

Hi Jay

Unfortunately that's a fairly substantial piece of code and is outside of the scope of this forum.

If you're able to have a go at it we can recommend where your code has gone wrong or how to improve it.

If this is a critical piece of your workflow I strongly recommend you attend our 2 day VBA Intro Intermediate course where you'll be shown all of these principles.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel or VBA

Jens said he is going to get back to me tomorrow

RE: Excel or VBA

Hi Jay,

Thank you for the forum question.

I have created a userform which will do what you want, but it will only work in the attached workbook. In the visual basic editor you can find the userform. If you double click the run button in the visual basic editor you can find the code. You need VBA knowledge to change it to fit your needs.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

stock.xlsm

RE: Excel or VBA

I will have a look thanks! where is the attached workbook?

RE: Excel or VBA

sorry just found it

RE: Excel or VBA

wow that is amazing! well done impressive!
thanks very much i can do the rest from here i think!

thanks very much

RE: Excel or VBA

Hi Jay,

I am happy that you can use the code.

Good luck changing it for your data.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel or VBA

Hi Jens,

I have attached a document, i was wondering if you could edit and change a few things.

1. The "in stock" is text rather than numbers when you put in the stock form and run it. So i need it to produce numbers rather than text number if you run you will understand because it comes up with an error.

2. I have added a Total cost on the end which i would need to also spit up when the stock form is run. which maybe could be worked out by the "Quantity Sold" which you put in, times the price per product, take that away the total cost would leave you with how much it costs for the X amount that has sold and leaving the remaining. Splitting that up in the marco so this would happen automatically when you put in the Product ID and the Quantity Sold.

Hope you understand my questions

Regards

Jay

RE: Excel or VBA

Hi Jay,

Thanks for the reply.

There was no file attached to the forum post.

Please find attached file. It is the same file as sent to you earlier. I have just added the word value in the code to take care of the number format issue.

About the total cost. You do not need any code for this. See attached file.

It is outside the scope for this forum to do developing work and setup clients workbooks, but I will suggest and strongly recommend you attend our 2 day VBA Intro Intermediate course where you'll be shown all of these principles as Gary suggested.



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

stock.xlsm

RE: Excel or VBA

Hi Jens,

I have tried the new one you have sent me and it looks like when you put the product in it does not do that product it does the product number below, i asked my team to have a look but we cant seem to figure it out.

Also at the end of Total cost can you add another 2 columns on the end with the same principle as the total when it divides by the number you put in. this then matches ours because we have 3 different total costs that need to be divided

regards

Jay

RE: Excel or VBA

Hi is it possible to get a reply to my last email thanks

RE: Excel or VBA

Hi Jay

Thanks for your post and sorry for the delay in getting back to you.

As I wrote in my answer 28 June that this sort of query takes us beyond the scope of the forum.

If you want us to do developing work we can look at your work and if we can identify a solution we will let you know scope, duration and associated costs.

If you would like to look into this further please do contact our enquiries team.

Kind regards


Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel or VBA

well i'm disappointed how i have gone on 2 courses to find a particular answer, and still cant get that answer and now i might have to pay additional fees to find it out.
Like i said in my previous email, there doc you sent me is not working in the right way as the first one did it only picks up one row regardless of what you put in and as its your code its hard to pick up which part you have changed to make this go like this. if you had a look you see straight away what i mean.

regards

RE: Excel or VBA

Hi Jay,

I am sorry that you are disappointed. I did spent some time doing the VBA I sent you and you replied that it was working Tuesday 24 June.

What you want I cannot do without having your workbook and specific knowledge about exactly what you want and as I understand it; it is not an easy fix.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel or VBA

Dear Jay,

Thank you for attending your Excel Intermediate and advanced level training with us recently.
I notice that you have been making use of our forum quite extensively, which is good to see.

I’m sorry you feel disappointed by the expectations you have regarding our forum. The forum is designed to support questions related to the training that you have received and not high level VBA consultancy requiring several hours of development. Our trainers have gone beyond the call of duty and scope of the forum in answering your requests for VBA level solutions. We have, in fact, provided a fair and accurate response to your original question, which you were happy with and have then added to the scope and are now demanding a response.

The forum is not designed or intended to provide development and consultancy level solutions, but is a support to the training you have received so far.

In order to progress your request, we would need to have you consider the time and financial investment that we would need to make in order to fully and completely provide you with a commercial solution to your spreadsheet development. This is a normal process for a business solutions provider to follow and if you would like to make use of our expertise in this area, we would be happy to have a conversation as to what you need and the associated costs for our time. For this kind of development we regularly charge our customers £1200 per day.

I hope this addresses your concerns and please let us know if we can assist you and we can arrange a phone call to discuss the details further.

Kind Regards,

Richard

________________________
Richard Cox
General Manager

Tel: 0207 987 3777

Best STL
https://www.stl-training.co.uk

Sat 5 Jul 2014: Automatically marked as resolved.

 

Training courses

 

Recommend training98.7 %
recommend us
More live stats...

Training information:

See also:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

See also:
Hints and tips
Forum topics

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel articles

Please browse our web site to find out more about
application development excel and other training courses.

Excel tip:

Wrapping Text in a Cell in an Excel 2010 Workbook

When you have a lot of text you want to put in a particular cell but you can't decrease the font size to fit because the text will then become ineligible, then manually wrap the text in a cell by simply pressing ALT+ENTER.

View all Excel hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.07 secs.