How do I Check value of Active cell against column of vaules in another sheet

[b1ackcr0w]

A colleague created the following within a Macro...

If ActiveCell.Offset(0, 4) = "Altran GmbH & Co KG" Or ActiveCell.Offset(0, 4) = "SOCATA S.A.S." Or ActiveCell.Offset(0, 4) = "ALTEN" Or ActiveCell.Offset(0, 4) = "LABINAL GMBH" Or ActiveCell.Offset(0, 4) = "Spirit Aerosystems E" Or ActiveCell.Offset(0, 4) = "ALTRAN INNOVACION S." Then
            '
            If InStr((ActiveCell.Offset(0, 3).Value), "airbus") Or InStr((ActiveCell.Offset(0, 3).Value), "Airbus") Or InStr((ActiveCell.Offset(0, 3).Value), "external") Or InStr((ActiveCell.Offset(0, 4).Value), "AEROTEC") Or InStr((ActiveCell.Offset(0, 3).Value), "AEROLIA") Then
              '
              ActiveCell.Offset(0, 7).Select
              ActiveCell.Value = "E1-EU"
              ActiveCell.Offset(0, -7).Select
            '
            Else
              ActiveCell.Offset(0, 7).Select
              ActiveCell.Value = "EE-EU"
              ActiveCell.Offset(0, -7).Select
            End If

This Macro is kept in another file seperate to the data.

My colleague was expecting not to have to add lots more or conditions to the first line. Now, as you can tell, its grown to the point where we should have a list of company names on a seperate worksheet for maintainability.

I have pulled all the company names into a worksheet within the file containing the data (.sheet17 fyi). That list is now in the A column of the new worksheet.

What I can't for the life of me code is how to just check if the activecell.offset(0, 4) is matched anywhere in .sheet17 column A of that other worksheet.

Ideas anybody?

[mike7952]

Can upload a workbook? Would be easier for us to help?

[b1ackcr0w]

Sorry Mike,

The InString part of the If looks up within a list of email addresses, and the workbook is dotted with data I could litereally get sacked for uploading to a public area. I've got to be careful with this particular file.

[mike7952]

Thats understandable, how about making up a mock workbook with the data structure your working with.

[b1ackcr0w]

OK - I've had a chance to cleanse out the sensitive info.

Note there are two worksheets, one with the data I'm working with, another contain the Macros only, no data.

First, what am I looking to do?

In the data, I need to make sure there is an entry for every row in sheet1 column "H", based on what is entered for column "E". If you look in the Macro my colleague wrote, the was he's done this is a bit clumsy and hard to maintain (see Sub setRegion). The problem is he's written the list of companies in as if conditions. Where I want to get to, is an If statement that compares the Activecell.Offset, to the list in sheet3 column. As you can imagine, this would be a lot easier to maintain.

Many thanks in advance for your help.

[b1ackcr0w]

Are we allowed to bump threads? I didn't see it prohibited in the rules

[mike7952]

@b1ackcr0w

Sorry I've been away for the last few days. I dont see your list of Companies anywhere? Will the VBCode workbook need to open the data workbook or will the data be maintained in the coded workbook?

[b1ackcr0w]

I'll Check it in a bit. But I'll probably keep it in the data Workbook.