Excel Macro Help, Copy Cells If Statement 2

[mattloflin]

Hello Everyone!

My task at hand is to copy cells to a different location based off of the information.

I have a template excel file where I copy the data to. The data is two columns and it varies in length.

it looks like:
col1 col2
bk/wh J1-32
wh/bl J2-12
bl/gr J3-27

There are 3 templates J1 Blue in cell B25 J2 Black in B15 and J3 Grey in B1. They all have cells to the right of them labeled 1 to 74.

I need a macro to say:

If col2 = j1 then copy col1 to the corresponding cell beside b25 (which is J1).

So bk/wh would be copied in the J1 template at the 32 number.

I have attached a file so download it and stuff. I can do all the formatting I just can't wrap my mind around the checking the number things.

The ranges can be set and all that however the col 1 will change in length and color and what cells correspond to what so it has to be an if statement or a for loop or something.

- Matt

"Never Give a Sword to a Man Who Can't Dance

 

[mattloflin]

The Ranges that need to be compared against (ex J1 - 36 ) are as follows:

J1-
C26 to V27
D31
G31 to V32

J2-
C16 to V17
D21
G21 to V22

J3-
C2 to V3
D7
G7 to V8

- Matt

"Never Give a Sword to a Man Who Can't Dance

 

[SkipVought]

Matt,

Extremely confusing requirement, is compounded by your lack of consistency, like

If col2 = j1 then copy col1 to the corresponding cell beside b25 (which is J1).

SO, is j1 a CELL REFERENCE or is j1 TEXT?

"So bk/wh would be copied in the J1 template at the 32 number."

What does THAT mean?

Look, you have stuff in your head that you have to be able to state clearly, concisely and completely.

Furthermore, where is ANY code that you have developed in an attempt to solve this mystery?

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[mattloflin]

I haven't created code that has to do with that part of this program I have looked around but all i can come up with is

If the first two letters of the Col2 = (text) J1 then copy the corresponding Col1 to the Range "J1" that has the last two digits

so that'd move

bk/wh J1-32

to the J1 range right about the # 32

- Matt

"Never Give a Sword to a Man Who Can't Dance

 

[SkipVought]

Matt TEXT in code, in exclosed in QUOTES, like ...

If the first two letters of the Col2 = [b]"J1"[/b] Then copy the corresponding Col1 to the Range "J1" that has the last two digits

"...Then copy the corresponding Col1 to the Range "J1" that has the last two digits ..."

The last two digits of WHAT.

common, matt, help us out here!!!

This is as clear as MUD!

Why don't you try to produce some code! Just go ahead and code that you just posted. If you don't know what the copy code looks like, then macro record actually doing what you want!


Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[mattloflin]

Skip you are very difficult but I understand why.

Here it goes:

First.

I need the macro to Search through 2 columns. 73 rows.

That search needs to look at Column 2 and determine if the first two letters of that cell are "J1" "J2" or "J3".

If it is "J1" then it needs to take the last two letters, ex. "J1 33" it would take the "33" and along with that the Col1 contents in the cell next to the "J1 33" and paste it in the J1 template above or below the 33.

If you get the download you can see what I mean by copy and paste it in the corresponding template.

It needs to do that for J1 J2 and J3.

I have tried to Record the macro but it just copys and pastes I can't figure out how to get it to search the criteria based of the Left(string, number) and Right (string, number) from recording the macro.

Does this help settle the mud?

- Matt

"Never Give a Sword to a Man Who Can't Dance

 

[mintjulep]

You don't need a macro for this.

You need some helper columns to parse out the connector ID's and a pivot table.

 

[mattloflin]

Can you go into more detail or message me? I haven't heard of these.

If that will allow me to copy and change info in the 73 rows and they don't always go in order.

Sometimes i'll have 60 sometimes 50 and it will jump around

ex.
J1-33
J1-46
J2-3
J2-12
J3-73

So it sometimes skips numbers and stuff, just 73 is that maximum

- Matt

"Never Give a Sword to a Man Who Can't Dance

 

[mintjulep]

Excel has a help feature. It will teach you what you need to know about pivot tables.

You would be well served to normalize your existing data first.

The text string "J1-33" contains two pieces of information. A connector ID and a pin ID. If you broke this up into two separate columns

Connector Pin wire_color
J1 33 bk/wh

Life would be much easier. By the way, is "bk/wh" a single wire that is black and white stripped, or two wires, one black, one white?

If it is two wires you should break that out into separate columns as well.

You could do this with Data | Text to columns

I can't take you any further, because you still have not done a good job of describing your requirement.

 

[mattloflin]

If you look at the excel spreadsheet I added it has a Finished and a Start

Finish is what the macro needs to make the start look like.

I copy the 73 rows from a "Buildsheet" and then I have to pull all the colors to the corresponding blocks in the corresponding Templates

It takes approximately 15 minutes per harness. So if I can write a program that does it that will be about an hour a week saved.

I can't split them up that'd be more work.

yes bk/wh is a black wire w/ white stripe.

All i need to do is have it search the 73 rows of Col2 and copy that cell in col1 to the right location.

For instance:

if col2 = J1 33 it would take the col1 cell right next to J1 33 and paste it in the J1 template above the number 33. Then it'd move to the next row.

Sounds simple but I cant get macro to do it in excel and I have googled and nothing... That's why i'm asking the experts.

- Matt

"Never Give a Sword to a Man Who Can't Dance

 

[ck1999]

Can you try this

vlastrow = Range("x34").End(xlDown).Row
vcol = Range("x34").Column
For counter = 34 To vlastrow
    vJtype = Left(Cells(counter, vcol), 2)
    Select Case vJtype
        Case "J1":
            vnumber = Right(Cells(counter, vcol), 2)
            If Left(vnumber, 1) = "-" Then vnumber = Right(vnumber, 1)
            Select Case Val(vnumber)
               Case Is < 17: Cells(33, 23 - Val(vnumber)) = Cells(counter, vcol - 1)
               Case Is < 33:  Cells(30, 23 - (Val(vnumber) - 16)) = Cells(counter, vcol - 1)
               Case Is < 53:  Cells(28, 23 - (Val(vnumber) - 32)) = Cells(counter, vcol - 1)
               Case Is < 74:  Cells(25, 23 - (Val(vnumber) - 52)) = Cells(counter, vcol - 1)
             End Select
             
             
     End Select
Next counter

This only does J1 you can modify to do all 3

It however does not color the empty cells in your templates

ck1999

 

[mattloflin]

ck1999 you never cease to amaze me. That is definately talent you surprise me everytime with BLAM code.

So to change that code to work for J2 and J3 I just need to change the J1 to J2 and I don't really understand the

vVal(vnumber) and I am trying to figure out how you get it to correspond to the right cells.

Could you explain how your thinking? If not that's cool I'm just trying to learn.

Also when I ran the code it renamed the Label "J1 Blue" to Vt/Bl

Was that something I did?

- Matt

"Never Give a Sword to a Man Who Can't Dance

 

[ck1999]

When you use left and right this returns portion of strings. SO in order to help use the value of the right side of the cell. You have to convert the string to a number so you use val(string)

I had to use
If Left(vnumber, 1) = "-" Then vnumber = Right(vnumber, 1)
because if the cell = "J1-5" then right would return "-5" and not "5"

TO use for j2 and j3 copy the entire case for j1 to be like the following

Select Case vJtype
Case "J1":
Do Stuff
case "J2":
Do Stuff
case "J3":
Do Stuff
end select

then you will have to change the row references in the Cells(row,col) to reflect the different rows used for J2 and J3.

Not sure about the label changing I will check that out

Hope this helps explain it some

ck1999

 

[ck1999]

Sorry about the label
Change the select to

Select Case Val(vnumber)
Case Is < 17: Cells(33, 23 - Val(vnumber)) = Cells(counter, vcol - 1)
Case Is < 33: Cells(30, 23 - (Val(vnumber) - 16)) = Cells(counter, vcol - 1)
Case Is < 53: Cells(28, 23 - (Val(vnumber) - 32)) = Cells(counter, vcol - 1)
Case Is < 73: Cells(25, 23 - (Val(vnumber) - 52)) = Cells(counter, vcol - 1)
Case Is = 73: Cells(26, 3) = Cells(counter, vcol - 1)
End Select


ck1999

 

[mattloflin]

hey ck1999 it doesn't put the wire color above the number 73 is that because it is a merged cell or something?

- Matt

"Never Give a Sword to a Man Who Can't Dance

 

[SkipVought]

"I have tried to Record the macro but it just copys and pastes I can't figure out how to get it to search the criteria ..."

One step at a time. It's not a good idea, IMHO, to do more than one thing in a macro. Once you can figure out the pieces, it goes together like a building. The idea here is to get you to see how to build a piece and then how it might fit together later.

You can choose to have someone GIVE you a meal, or you can learn to FISH. BTW, I am a decent cook.


Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[mattloflin]

.... haha Skip you say you are a decent cook but out of all my questions I have never got any helpful code out of you, just you complaining about one thing or another.

CK1999 on the other hand is a gourmet chef.

Hey CK instead of it renamed the J1 BLUE it renames the "72" to vt/bl now.

Any ideas?

- Matt

"Never Give a Sword to a Man Who Can't Dance

 

[SkipVought]

"...I have never got any helpful code out of you, just you complaining about one thing or another."

Well, Matt, it was not until I weedled and coaxed and posted the "clear as mud" phrase, that you finally came forward with something to work with.

However, by that time, the whistle had sounded, and I left for the house.

Since that time the thread has increased in size by 200%.

Take it away, ck1999.


Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[mattloflin]

I fixed the error with it going to the wrong places.

Works great!

Only thing left is to search the templates and if it is blank then to color the background light yellow.

how can i do this? can someone show me and explain it at the same time, I'm trying to learn everything and understand it so i don't rely on you guys as much

- Matt

"Never Give a Sword to a Man Who Can't Dance

 

[mattloflin]

what if the original background color is light yellow and when it copies to the cell it recolors it white.

possible?

- Matt

"Never Give a Sword to a Man Who Can't Dance