Solved: Very hard macro in Excel (copy part of one cell to several areas with logic)

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

NextGen

Thread Starter
Joined
Jun 8, 2011
Messages
3
Hello everyone,

I am completely stumped and I hope to find some help from someone here... I am trying to format an excel spreadsheet that has over 10k records is a very strange format. Please see attachment...

I have created the logic to grab the numbers to the left of the "carrot" in cell E3 and replicate that number in cells B3 B4 B5 B6 B7 B8 B9 B10. That process is replicated to pull the numbers to the left of the "carrot" in cell E13 and replicate that number in cells B13 B14 B15 B16 B17 B18 B19 B20...And so on and so fourth. The problem I am stumped on is how do i replicate this process all the way down the spreadsheet?

One FYI is that each record "bock" is not always (as in the example spreadsheet) 10 lines it can be 11/12/13 or more lines. Basically the record starts at the letters "MSH" (see A1) So I the logic I am thinking of is to push the code in red in the "E" cells to the "B" column after the "EVN" (in cell A2) and to stop before the "MSH" (in cell A11) and continue that process down the spread sheet.

I hope this is not too confusing but I have been banging my head on the wall for hours and just cant figure it out. Thank you all for your help
 

Attachments

Joined
May 26, 2011
Messages
255
NextGen,

How does column "A" get populated? Will there be empty cells in it between the first occurrence of "MSN/EVN"?
Code wise it should not be hard to get the "Number" to the left of the carrot. The problem comes up in populating it to column A not knowing if its populated with or with out blanks.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
In regulr Execl formula you use (Example is Clumn \

In your sample for Row 3

F3=FIND("^";E3;1)
this formula will retrun 5

This indicates that the ^ is in position 5
If the value returned is 1 then there is nothing before the ^ so no number.


Now the problem.

If there is no ^ in the cell then you will get aan error so the correct formula will have to be:


=IF(NOT(ISERROR(FIND("^";E3;1)));FIND("^";E3;1);"")

If you want to do this in a macro, then the formula is


IF instr(1,"^",cellvalue) > 1 then
your code
End If

So all you have to do is start with Row 1 'til the laste row and check column E
and fill the number in column B

You'll have to define a rule to fill all of column B until something triggers the change and I think you are the one that knows which criteria is to be used.
 
Joined
May 26, 2011
Messages
255
Hi,

Here's a copy that will populate the data to column "B". The code is based on the current format of the work sheet. If the format changes the code will not work properly. You may need to change the sheet names. I used "Sheet1" per your example.
 

Attachments

Joined
May 26, 2011
Messages
255
Thanks for letting us know.
If your satisfied please show this thread as "Solved"
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top