Using IF in 3 columns

[Mr80s]

Hi Folks,

I'm sure this is pretty basic to some, but I'm trying to confirm the best way to go through all rows of data and if columns F, O and V contain specific strings then write something to column Z. Then if those same columns contain something else, write something to column AA

Basically:

• if F="a value" or "another value" AND O ="one of 6 values" AND V = "one of two values" then place Yes in Z,
• if F="a value" or "another value" or "a third value" AND O ="one of 6 values" AND V = "one value" then place Yes in Z,

ie:

Column F	Column O	Column V	Z	AA
Cat or Dog	Red or Black or Brown	house or condo	Yes
Cat or Snake	Red or Black or Brown	house		Yes

The # of rows will vary so will have to determine rows then search in Column F O and V for values.

Any starting ideas and I'll run with it from there.

Cheers,

 

[Mr80s]

Hey Joe4,

First off, thank you very much for taking the time to show me and point me to where I can learn more. I've actually been off trying to implement this into my larger script for the last couple days (well couple hours each day)

I have a strange problem, when I run the script as a single macro by itself, everything works fine.
When I run it as part of my other macros, it's processes and looks like it runs fine, but it doesn't write any "yes" values.
I then run the single macro on the same output file and it again works fine and writes yes's.

What I'm doing in a part of the script is opening a file, then copying certain columns into a new worksheet, then filter out empty rows and then run the script your wrote...

Everything works, except no Yes's.

When I run just the portion that does the if and's in a separate excel file as a stand alone script (using the output generated) then it runs and writes the yes.

I know very vague or hard to review without all my script pieces...

Just thought I'd ask if you've heard of why it runs fine standalone and seems to run as part of the bigger script but doesn't write the yes's.

Cheers,

 

[Joe4]

I know very vague or hard to review without all my script pieces...

Just thought I'd ask if you've heard of why it runs fine standalone and seems to run as part of the bigger script but doesn't write the yes's.

Yes, it is hard to guess what might be going on without seeing your code and your files.

Once possibility, especially when working with multple files and/or sheets, is that during a certain part of the code, you may not actually be on the sheet (or file) that you expect.
What I would recommend doing is stepping through your code line-by-line, while at the same time watching what happens on the screen. If you have two monitors, put the code on one and the worksheet on the other. If you only have one monitor, then resize your code to about 1/4 the size of the screen, so you can see both at the same time.

This is a good debugging technique that I use a bit. When you see what is actually happening, often times you can see exactly what the problem is.

 

[Mr80s]

Yeah sorry thought it might be like trying to find a needle in a haystack...

I've been debugging with F8 and stepping through. I've also been making sure I'm setting the correct sheets as active. In stepping through, I even add variable to watch and everything seems fine, it loops through the if's on the correct sheet...it just doesn't write the yes's. When I run it as a separate macro it's fine.

Question, is it possible to set an "add watch" to (Cells(r, "C") or a way to see if what is being compared..or perhaps the Cells(r, "G") = "Yes" part...

I appreciate all your help and I'll work away on this to try to figure out why the code runs in a separate macro on the same source data but not when part of a larger script (though it appears to loop through everything fine).

Cheers,

 

[Joe4]

Question, is it possible to set an "add watch" to (Cells(r, "C") or a way to see if what is being compared..or perhaps the Cells(r, "G") = "Yes" part...

I really do not use "watches", so I don't have much to say about that. You could add Message Boxes along the way to return the value at different points.

If you know what part of the code is not working, you could post it, and also mention what is supposed to be happening at that point (ncluding what files/sheets/ranges should be updated).

 

[Mr80s]

Wow what a pain...

Your help was great though, I did use message boxes to track down the issue. The If loop for cells was not reading the current active worksheet even though that sheet is active. I called and confirmed active sheet and used msgbox to show me what sheet was active and it was the right one.

However, the if
If (Cells(r, "A") = "Dog" Or Cells(r, "A") = "Cat") And _ and the "B",etc.. were not returning the value of the cells on the active sheet.

I had to specify which sheet via :

if (Worksheets("SheetName").Cells(r,"A")="Dog" or Worksheets("SheetName").Cells(r, "A") = "Cat")

And same specifically with the "writing of Yes"

Cells(r, "AA") = "Yes" had to be changed to Worksheets("SheetName").Cells(r, "AA")

I certainly put the sheet as active first:

Set sh4 = ThisWorkbook.Worksheets("SheetName")
With Sh4: .Activate

...anyway...hard coding is is ugly but worked.

Thanks

 

[Joe4]

Glad you got it figured out.
Yes, when working with multiple sheets, it is always good to qualify the range with the sheet part of the variable, to ensure it is looking at the right sheet/range.

One thing that often trips people up is looping through sheets. The think that looping through sheets will activate each sheet, but it does not. They either need to include the sheet reference in with the variable, or activate/select the sheet.

For example, run the following code on any workbook that has multiple sheets and watch what happens:

Sub TestMacro1()
    Dim ws As Worksheet
    For Each ws In Worksheets
        MsgBox ActiveSheet.Name
    Next ws
End Sub

It will return the message box the same number of times as there are sheets, but it will keep returning the sheet name of the active sheet when you started the macro.

So, to get it working correctly, you can either activate the sheet within the loop like this:

Sub TestMacro2()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Activate
        MsgBox ActiveSheet.Name
    Next ws
End Sub

or my favorite, instead of activating/selecting the sheet, just use the worksheet object you set:

Sub TestMacro3()
    Dim ws As Worksheet
    For Each ws In Worksheets
        MsgBox ws.Name
    Next ws
End Sub