Variable area for countifs

[a_driga]

Dear all,

I have a problem regarding the use of formula in which the range of area checking is a variable.

You can open the attachment to be able to go through with me:

In sheet1 I have table with 12 column and 6 rows (just for an example)

From col I to L, there are header namely INSP, LUBE, TIGH, REPL

The problem are as follow:
For example: I need to tag in column LUBE with "L" if there is the exact database from colum A to D plus column F in Sheet LUBE

The way I do at them moment is using the following code:
- Assuming that active sheet is Sheet1
- All dimension has been declared and known (i.e. lr1)

Please Login or Register  to view this content.

I found it to be too long and prone to mistake. Because if column A-E need to be added, then I need to alter the code to incorporate new if-syntax. the Can anyone give me suggestion on how to make the code shorter and more efficient.

[patel45]

You attached an xlsx file without macro, you pasted a not complete code

[AlphaFrog]

Try this...

Please Login or Register  to view this content.

[a_driga]

Try this...

Hi Alpha, thanks for providing a modification for my code. Is your code specifically for Sheet Lube? Is it possible to check the sheet Lube & Repl in one go and not to write your whole code again for different sheet?

Thanks in advance.