• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

NEED HELP WITH IF/THEN STATEMENT

Andrew Quirl

New Member
Hi Guys, this looks like a great forum. I would appreciate any help you can provide on this issue I'm having. I've gone thru several drafts or versions of this particular section of an overall macro project I'm working on. This little section is giving me fits. I have posted this on a couple of other forums, but the conversation is getting long and no results yet. So I decided to streamline my question to just the part I need help with and post it here fresh.

What I'm trying to achieve here is to move some part #s from one column to another based on criteria. I started out trying to do this with a filtering process and moving visible cells after a filter. This had several challenges so now I'm thinking it may be best to have the if/then statement go through row by row applying a set of criteria.

So looking at one row at a time ... (example row 2) and these can be 3 separate subroutines that run in order:

POPULATING BEST MATCH COLUMN R:
-IF K2 has yellow fill RGB(255, 255, 0), THEN copy the value in K2 to R2.
-IF K2 does NOT have yellow fill, and N2 does have yellow fill, THEN copy value in N2 to R2.
-IF K2 does NOT have yellow fill, and N2 does NOT have yellow fill, and K2 has a value that is a part#, THEN copy K2 to R2.
-IF K2 does NOT have yellow fill, and N2 does NOT have yellow fill, and K2 does NOT have a part# as the value, THEN copy whatever value exists in N2 to R2 whether it is a part# or not.

POPULATING UPGRADE COLUMN V:
IF M2 has orange fill RGB(255, 192, 0), and the value in N2 does not already appear in R2, then copy the value in N2 to V2.

POPULATING ECONOMICAL COLUMN T:
IF M2 has green fill RGB(146, 208, 80), and the value in N2 does not already appear in R2, then copy the value in N2 to T2.
 

Attachments

  • WORKSHEET DEVELOPER TEST TAB 2 ONLY.xlsm
    129.3 KB · Views: 3
Last edited:
I have identified a step I originally left out. Could you please modify? The attached document shows changes in red.
 

Attachments

  • CCT2.docx
    12.9 KB · Views: 2
Hi ,

I have changed the code , but am not able to test because the workbook does not have data in the columns referred to in the modified requirement.

Please test it at your end.

Narayan
 

Attachments

  • WORKSHEET DEVELOPER TEST TAB 2 ONLY.xlsm
    137.9 KB · Views: 6
i have altered the test workbook by adding a first sheet (current macro is for sheet2 only) so if you can make the macro dynamic to know sheet2 that would be appreciated.

I've also added data to the two appropriate "notes" columns for testing.
 

Attachments

  • WORKSHEET DEVELOPER TEST.xlsm
    138.8 KB · Views: 3
Hi, Andrew Quirl!

The code is placed in "Competitor Data" worksheet object, so in this instruction:
Code:
    With Me
you're telling to refer to that sheet.

Create a new module, move the code to it, and change that previous sentence to:
Code:
    With Activesheet

Then the macro will act on the actually selected worksheet when you run it (I assume by F8, Execute).

BTW, welcome to Chandoo forums!
Checked this yet?
http://chandoo.org/forum/forums/new-users-please-start-here.14/

Regards!
 
i have altered the test workbook by adding a first sheet (current macro is for sheet2 only) so if you can make the macro dynamic to know sheet2 that would be appreciated.

I've also added data to the two appropriate "notes" columns for testing.
Hi ,

I am not able to understand your requirement.

As it stands , your sheet named Competitor Data has the data on which you wish to run the macro. The macro is also placed in the VBA section pertaining to this sheet. So what is the problem ?

If you run the macro , you will see the results. You do not have to change anything in the macro.

If your requirement is different , please give details.

Narayan
 
You are correct on the question of sheet2, my bad.
However, I just ran a test on the revised macro and none of the revisions are working to pull in 'notes'.
 
Hi ,

See the attached file , which results when I run the macro on your test data.

Is this not what you want ?

Narayan
 

Attachments

  • WORKSHEET DEVELOPER TEST.xlsm
    139.1 KB · Views: 4
Please forgive my ignorances .. you are correct; it works just fine. I had something in the wrong spot when I copied the code over to my personal workbook module. I appreciate your genius :)
 
Back
Top