Posts by Mark Francis

Re: vba file handling

you the man Dave, that works fine for what I need to do.

Thanks very much

Re: vba file handling

Thanks aadarsh,

I thought it may be a little beyond my ability and that code proves it.

Much appreciated

Hello,

I have come to a point in a VBA program where I could do with improving the code by including some basic file handling. What I basically want is for Excel to count the number of files in a specified directory and then loop that amount of times until all the fully qualified paths are stored in a variable array so the user can access them later in the program.

Any advice is much appreciated

Many thanks

Re: VBA duplicate a row based on cell value

I just have to....

good thinking batman!

Many thanks, it works like a treat. I have programmed before with Pascal and am just getting to grips with VBA. Where did you learn to code like that?

Many thanks again

Mark

Hi guys,

I posted yesterday with a simple request and got two solutions. One thought has since led to another and I have decided to go with the VBA solution, but to get the problem earlier on (I`m learning at last!!!)

This is a description of the problem. I have a spreadsheet full of model and fabric combinations, but some orders will come up as a quantity 2 rather than having two lines. I really need everything to be standardised, which means changing (using VBA) the following . . . (just a small example, red bits are the changes needed)

NPORT FX WOV HESS NEUTRAL 2STR 1
URBNO LR LTR N FN COFFEE 2STR 1
ROSCO FX WOV HESS NEUTRAL CHAIR 1
KINS LR LTHR LUX CHESTNUT LGE 2STR 2

NPORT FX WOV HESS NEUTRAL 2STR 1
URBNO LR LTR N FN COFFEE 2STR 1
ROSCO FX WOV HESS NEUTRAL CHAIR 1
KINS LR LTHR LUX CHESTNUT LGE 2STR 1
KINS LR LTHR LUX CHESTNUT LGE 2STR 1

As you can see, I need every multi order to automatically be on a seperate but identical line to ensure all lines have a single piece.

Help on this is much appreciated, and I promise, I am learning from each solution.

Mark

Re: Multiplying a Text Value

Bill,

That is food for thought, it could in fact change my outlook on the entire system.

Again, much appreciated, thanks very much.

Re: Multiplying a Text Value

Many thanks Jack,

Have a beer on me!

Much appreciated.

Hi,

I am currently working on a spreadsheet and have hit a brief stumbling point. I guess what I`m trying to do is =sum(b1*(text(a1,"")) - which doesn't work (needless to say)

which should multiply my text value as follows

chair 3 chair chair chair
table 2 table table
cushion 3 cushion cushion cushion

Can any of you experts lend a hand??

Many thanks

Mark

Re: dividable formula

SHADES, I`m not exactly sure what your formula is doing. My end result has to be a sheet (indluding a unique order number) for the items ordered in batches of no more than three. for example if i need to get 13 cookies i send out 4 orders for 3 and 1 order for 1.

VB, although your fraction displays what I want in a format that I understand, this is to automate a manual process ( currently scrolling down and changing all values over 3 into 3s and remaining 1s and 2s) for a current system that prints out tickets, for which I need to keep all current formatting. for example the above batch of cookies would print out 5 sheets, 4 of which would have 3 cookie stickers and one would have 1 cookie sticker. Stickers are produced according to the amount of lines in the finished text file export like this

ORDER NUMBER ITEM TYPE QTY
00232444 COOKIE 3
00232445 COOKIE 3
00232446 COOKIE 3
00232447 COOKIE 3
00232448 COOKIE 1

Thanks for the response guys!

Hi Guys,

My brain has stopped working already this week. I need to break down a cumulative orders figure into units of three as the product can only be produced in three's. If there are 16 units orderes, we have to do 5 batches of 3 and 1 batch of 1 if you catch my drift.

I have attached a sample sheet as an example.

Many thanks in advance.

Re: VBA to copy scattered data to single column

WillR,

Your advice was taken into consideration the first time don't worry! Once I saw the piece of code and saw how VBA handles this kind of process, I was able to change the code to do exactly that, - no need for a "clean-up" process!

Many thanks to both of you!

Mark

Re: VBA to copy scattered data to single column

Hmmmm!!

Thank you very much dangelor, this certainly does the job on that sheet I will implement this code onto the system and see what happens. I will make sure I get to grips with VBA asap because in the right hands it's power is unreal!

Much appreciated!

Thanks also for WillR, I agree the ideal solution is always to eliminate the problem and not work around it, but a quick fix of VBA never hurt anybody!

THANK YOU VERY MUCH

Re: VBA to copy scattered data to single column

The code is currently standard, unedited Excel macro recorded code that selects the entire range for the given month and dumps the values in a seperate sheet. Please forgive my current lack of VBA knowledge, I just made the assumption that Excel features could not handle this procedure, I have my crash course and books at the ready for some intensive learning.

The range covers 84 potential coninciding sicknesses times 23 possible days in that month, so I am able to paste the current month into a fixed area of the seperate sheet according to the maximum amount or rows it will use, hence the empty spaces.

*sniff sniff*

Thanks

Re: VBA to copy scattered data to single column

The data is from a Sickness Tracker so I`m sure you can appreciate that the structure has to remain intact at the source, however, there may be an intervention before actually cut and pasting the values in the other sheet like you have suggested.

The tracker works by capturing data from the user in a graphical structure that represents days of the week for a given month. The sicknesses are inputted as a two digit alpha code and are combined with clock number and date to produce the codes in the sheet I attached. The intention is to export these codes to a seperate sheet for reporting, currently cut and pasted directly (values only) into the sheet like you have seen. If you do know a way of collecting each code as it is created and feeding it sequencially into the report sheet, that would be nothing short of amazing!

Many thanks for your attention Will!

Re: VBA to copy scattered data to single column

Thanks Will, was not sure that would work!

looping code?

I have a sheet with values scattered in cells in quite a random fashion. I need a sequence that checks all cells and moves all data to the first column so that all data is listed in one single column.

ex x xxx x x
x x xx x x

to become

x
xxx
x
x
x
x
xx
x
x

if you catch my drift.

If not I posted a more detailed thread but did not produce a title that attracted enough attention a few pages down, this has an example sheet.

Many thanks in advance, really appreciate any help.

Hello, I hope somebody can help.

I have contructed a spreadsheet that pastes some codes into a new sheet. The trouble is, I need all these values to be in one column but they are scattered everywhere with empty columns and rows everywhere!

I tried goto blanks and it only gets rid of entire blank segments where I want all my codes to be in a list in one column so I can then split them up and make reports.

I have attached a one screen example of these scattered codes to give you an idea of the situation!

Many thanks in advance!

Re: List directory sizes

My apologies, where I linked from had all the posts back to front and I spent most of my time simply finding the attachment!

Many thanks to you both!

Re: List directory sizes

Thanks for your efforts but I did already try that link but the code is locked. I need a simple routine for getting directory sizes for an existing spreadsheet!

Sorry!

Okay, here goes my first thread,let see what you guys can do!

I have search the forum for the above but cannot find *exactly what I`m looking for. . .

I want to incorporate some code in an existing spreadsheet that returns the overall size of directories from a given path so I can make a pie chart of a drives contents.

Any help is welcomed as I am just learning VBA and the more angles I learn to approach a problem the better!

Thanks very much!