Header info on excel list

[gentle2005]

HI

I need to insert header info into excel list on each row as shown in the attached file. This code will save my tons of work. can somebody tryout?

[p45cal]

Is it really as haphazard as that?: compare A5:B6 for example and A22:A23.
Are you importing this data into excel?
There's probably a more reliable way - can you give details?

[gentle2005]

Thank you p45cal.Yes I imported from word file. It is haphazard but if we loop thro column A for product name and batchNo we can paste it in G10:H21 before the next occurrence of product name and Batch No in column A. Even if the code delivers the following, it would help me to save time a lot as I have 8000+rows of data like this.

=If (B5<>"",B5,A5) and =if(B6<>"",B6,A6) implied result Product name = ":G5" and batch No =" :X " in G10:H21

=if(B22<>"",B22,A22) and =if(B23<>"",B23,A22) implied result Product name ="PRODUCT NAME: G5" and batch No ="BATCH No.: X" in G26:H32.

Once I get the above result I can do the rest in excel as proper database. Thanks in advance.

[p45cal]

I will do this with VBA later today or tomorrow, but realise that the rest of the data is all over the place too (C11 can't be right, surely?)
I note that A22:A23 is probably just a continuation of A5:A6 on the next page of the Word document.
Are there tables in the word document? Could I see a sample?
Is the Word document created by a system that can export something else?

It's just that with 8k+ rows of data like this it's a heck of a lot of work and subject to human error and I can't help thinking there's got to be a more robust way.

[p45cal]

In the attached is a button on the Existing sheet. Clicking it runs macro blah which copies the Exsting sheet and adds data.
It's probably more convoluted than it needs to be but I coded for some eventualities which may never happen.

[gentle2005]

Thank you for your valuable time p45cal. I am sorry the code didn't do the trick in real file. The code didn't go through entire 8000+rows instead it struck with error code" object variable or with block variable not set"

Set Destn = Intersect(searchArea, TestRng.Offset(1).Resize(searchArea.Rows.Count - 1)).Offset(, 5)


it is not pasting product name and batchno in correct rows instead it starts middle of the report.


Please guide me.

[p45cal]

I don't have much time in the next 3 days or so - I can check messages periodically for messages though.
The problem with debugging like this is that I can't quickly find out what's going on so I'm confined to guessing and will probably guess wrongly. My first questions might be along the lines of: How far does it get down the document? What's different about the live spreadsheet v. the sample spreadsheet? Is searchArea a valid range? Is TestRng a valid range? There are just so many possibilties that without the real spreadsheet it will take an age.
In the code there are several .Select lines commented-out, you could un-comment them and step through the code with F8, watching what's being selected; do this on the sample file first in order to see how it should work, then do it on the problem sheet and see the difference.
If you want, you can ask me via Private Message here for an email address to send the real spreadsheet (ad/or the Word document) to (in case there is sensitive data in the files).
I would guess also that the import-from-Word routine is not as robust as it could be. (You could answer some of my questions in msg#4 - it could be an easier route to take.)

One other possibility is to have a TeamViewer session, which might be the quickest way. PM me here if you're willing. I'm in UK, 4.5 hours later then India.

[gentle2005]

yes for team viewer

[gentle2005]

dear p45cal, superb your code does wonders!!!!! Great job done. Magic!!! I have gone thro the code I need to change the "find" as per my real file. Done. Good great work. Thank you very much.