VBA to detect change in cell value

[YOO629]

Hi guys,

I'm new to VBA so this is way beyond what I'm capable of so I wanted to ask you guys for some help if it's possible.

What I want this macro to do is to search column B for a change in cell value, then copy and paste up to the cell that has a different value into another sheet using transpose paste.

eg. I have a column where cells b2 to b32 are "abc1", b33 to b40 is "abc2", and b41 to b60 is "abc3". I want the macro to see that b33 is different from b2 to b32 and copy & transpose b2 to b32 into another sheet at (for example) b1. Then I want the macro to continue to copy b33 to b40 and transpose those values at (for example) b2. The macro will do the same for b41 to b60 and will continue until it sees a blank cell.

Any ideas?

[Mumps1]

Try:

Please Login or Register  to view this content.

[YOO629]

Thanks Mumps1,

I tried your code and it works fine, but the macro is pasting the last cell of one type (the last abc1) in a new row. I've uploaded a screenshot so it's more clear what I'm talking about.

Untitled.jpg

[Mumps1]

Oops! Change:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Another:

Please Login or Register  to view this content.

[YOO629]

Thanks John, that one works well also.

I thought I could learn a few things by tinkering around with both codes, but I'm afraid it's a little too much for me. The project I'm working on focuses on transforming a file like this:

2.jpg


Into this:


3.jpg


The first step is to go through column B of Sheet1 and have only one of each unique value. I'm thinking this can be done through removing duplicates and copy+pasting to Sheet2 at A2. The downside to this is that I want to keep Sheet1 in its original state so removing duplicate values poses sort of a problem.

The second step would go through Column C and see that the values loop from a-e. I need to select (in this case) cells C2 to C6 because C7 marks the beginning of another loop through values a to e. EDIT: in the picture it doesn't loop, but just pretend it does :P

The third step is to go through the values in row F based on the values in Row B. Notice in the pictures that all the values associated with "abc1" in Sheet1 Column F are in the same row as "abc1" in Sheet2. Similarly, all the values associated with "abc2" in Sheet1 are in the same row (in order) as "abc2" in Sheet2.

This is asking a lot (in my opinion) but any help given would be much appreciated.

[JOHN H. DAVIS]

Try:

Please Login or Register  to view this content.

[YOO629]

Thanks John,

I tried running your code but I get "Run-time error '1004': Application-defined or object-defined error"

[JOHN H. DAVIS]

I tested it with sample data I made up. Can you provide a sample which errors? Which line is highlighted when it errors?

[YOO629]

Sorry John,

I tried it again and for some reason it didn't error out this time. It runs fine. However, the macro copies and pastes the entire Column C when I need it to recognize that the values in Column C loop


1.jpg


In this example, I want the macro to recognize that a,b,c,d,e,f,g are unique values and copy/transpose only those unique values to the next sheet.

[Mumps1]

@YOO629: I was wondering if you tried my suggested macro.

Please Login or Register  to view this content.

[YOO629]

Hi Mumps1,

I did try your code and it worked great. Sorry I didn't include that in my messages earlier. There were a couple more things that I wanted the macro to do and couldn't figure it out on my own so I continued the thread.

[Mumps1]

Glad it worked out.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[YOO629]

Hi John,

I tried that code and I'm getting two types of errors depending on how I initiate macro. If I start the macro from excel (not vba) it'll give me this error:

1.jpg


And if I start the macro from VBA it'll give me this error:

2.jpg



As far as I can tell from the workbook and the code, you added this section:

Please Login or Register  to view this content.

The results I'm getting are the same as your suggestion earlier today. Everything is fine, I just need to get rid of/prevent duplicate values in row 1. I'm guessing that this part of the code that you added will go through Row 1 and delete the duplicate values. I'm also guessing that since the results look the same as when I put in your code that you suggest earlier today, that the macro is having trouble with this part of the code.

I've tried manually to record a macro and have it remove duplicate values from Sheet2 Row 1 but it seems that Excel will only remove duplicate values from a column and not a row. Any ideas?

EDIT: Also, when I get the two errors no part of the code is highlighted.

[JOHN H. DAVIS]

Can you attach a sample which produces the error's? I can't duplicate them when I test them, so I don't know what the problem may be.

[JOHN H. DAVIS]

I added a Dim statement for z. See if that helps.

Please Login or Register  to view this content.

[YOO629]

Hi John,

I'm not sure how to attach files onto here but I've uploaded it to my Dropbox. Here's the link to the file:

https://www.dropbox.com/s/lk9j2rqqyf...est1.xlsm?dl=0