How can I copy data into a variable number of cells based on how many times a certain field has data?

[MikeyW1969]

Hi all,
I may have made my topic confusing, I'm not sure how to explain what I need to do in the limited space of a subject line.

Here's a brief overview of my project:
I currently have an Excel doc that lets me take a single line from a master log of work tickets each day, plug the relevant information in the correct spaces on my template, and save the document with that specific ticket number as the name. At the end of day, the boss wants the tickets back into the original log format it was in(Which doesn't work well for running notes, but is good for his overview of all tickets).

The problem that I am trying to figure out is parts. For each part used, the EOD log needs to have a duplicate row, with all of the data the same, except for the specific part info. I have the parts info in separate little boxes, and we just scan the data. Some parts have serial numbers, and some don't. They all have an 'Order Number' though, so what I'm trying to figure out how to do is how to get my data back out at the end of day, with a line for each part used. If I installed 2 hard drives, I'd have two identical rows, with the exception of the different fields. If I installed 8 DIMMs, I'd have 8 rows, and so on.

I'm not sure how to go about this. Like I said, all parts lists have 'Order Number" in common. One approach was to run a loop that counted how many there were, and make that many lines. Another idea I had was to make a default number of lines, and then have it delete any that didn't have anything in the 'Order Number' field. The problem is that I'm not even sure how to do that correctly. I was wondering if this is the kind if data that fits in a table in some way, and if that gives it the correct properties to plug into the right fields. When this is all done, we will have a working document that will have multiple tabs, one for each ticket #, they will save to the extra tab with the work log on it, and at the end of day, I will export the data on the log page and plug it into the correct template.

God I hope I didn't make this a confusing mess, any help would be greatly appreciated, and I could post samples of the layout in question or screenshots of the template if that would help.

Thanks!

 

[MikeyW1969]

So after further searching, I may have found my starting point... I think that I need to combine a Named Range and a Dynamic Range to get the results I need. I would define the cells that have the Order Number field filled and then populate the total number of rows in the final document based on how many there are. I'm starting with this:

[TABLE="width: 583"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]ORDER #:[/TD]
[TD]XXXXXXX[/TD]
[/TR]
[TR]
[TD]PART #:[/TD]
[TD]YYYYYYY[/TD]
[/TR]
[TR]
[TD]OLD PART SERIAL #:[/TD]
[TD]ZZZZZZZ[/TD]
[/TR]
[TR]
[TD]NEW PART SERIAL #:[/TD]
[TD]BBBBBBB[/TD]
[/TR]
[TR]
[TD]PPID:[/TD]
[TD]CCCCCCC[/TD]
[/TR]
[TR]
[TD]AWB:[/TD]
[TD]DDDDDDD

[/TD]
[/TR]
</tbody>[/TABLE]

And would fill a form that looked like this With the relevant data, and the total number of rows based on how many parts I used(From Above):

[TABLE="width: 668"]
<colgroup><col><col><col span="3"><col></colgroup><tbody>[TR]
[TD]ORDER #[/TD]
[TD]PART #[/TD]
[TD]OLD PART SERIAL #[/TD]
[TD]NEW PART SERIAL #[/TD]
[TD]PPID #[/TD]
[TD]AWB TRACKING #[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[/TR]
</tbody>[/TABLE]


There is more to the second part than just the columns I showed, but this is just for reference. Does this make it a little clearer? My first post was a little muddy, didn't know exactly how to ask what I needed.

 

[MikeyW1969]

OK, I was able to get the number of "Order #" fields using the WorksheetFunction.CountA function. If anyone comes to this thread looking for a way to do that, here is what I have:

PartsCount = Application.WorksheetFunction.CountA(Range("B43, B50, B57, B64, B71"))
MsgBox PartsCount

I have just specific cells being scanned, but this would work for a range of scans "A2:A17", for example. And the second line 'MsgBox' was just my way to test the code. It will tell you what the number it returns is. You can delete that part.

Hope I was able to help someone else.