VBA If offset condition

[Kra]

Hi all!

I am struggling with macro that checks offset of a cell. I am able to build range and everything, but I have problem with IF AND condition.

So looking at mini sheet:
Macro button in column I
Column F - from row 3 to LastRow - input from user
Range N3:T & LastRow - exported data

If condition:
Checks value from column F in column N.
If column O has "X" and column S is not empty and column T contains anything except "X" (can be empty as well), then count how many values meet these conditions and display number in cell J2 with word "found". Then change cell color in column F to red.



Values C, D and E meet these conditions so they are red.

Book1
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2					Macro button	3 found					Status				Parent	Status
3		A								A	X				AA	X
4		B								B	X				BB	X
5		C								C	X				CC
6		D								D	X				DD
7		E								E	X				EE
8		F								F					FF
9		G								G					GG
10		H								H
11		I								I
12		J								J
13		K								K					KK
14		L								L					LL
15
Sheet1

 

[rollis13]

This macro will work only if you decide which columns are be compared to. As it stands it is according to your layout in attachment Xk2BB and not according to explanations. Adjust the coordinates as needed:

Option Explicit
Sub CountItems()
    Dim x      As Long
    Dim lr     As Long
    Dim count  As Long
    lr = Range("B" & Rows.count).End(xlUp).Row
    For x = 3 To lr
        If Range("B" & x) = Range("J" & x) Then   'supposed to be: Checks value from column F in column N
            If Range("K" & x) = "X" Then          'supposed to be: If column O has "X"
                If Range("O" & x) <> "" Then      'supposed to be: and column S is not empty
                    If Range("P" & x) <> "X" Then 'supposed to be: and column T contains anything except "X" (can be empty as well)
                        count = count + 1         'then count how many values meet these conditions
                        Range("B" & x).Interior.ColorIndex = 3 'supposed to be: Then change cell color in column F to red
                    End If
                End If
            End If
        End If
    Next x
    Range("F2") = count & " found"                'supposed to be: and display number in cell J2 with word "found"
End Sub

 

[Kra]

This macro will work only if you decide which columns are be compared to. As it stands it is according to your layout in attachment Xk2BB and not according to explanations. Adjust the coordinates as needed:

Option Explicit
Sub CountItems()
    Dim x      As Long
    Dim lr     As Long
    Dim count  As Long
    lr = Range("B" & Rows.count).End(xlUp).Row
    For x = 3 To lr
        If Range("B" & x) = Range("J" & x) Then   'supposed to be: Checks value from column F in column N
            If Range("K" & x) = "X" Then          'supposed to be: If column O has "X"
                If Range("O" & x) <> "" Then      'supposed to be: and column S is not empty
                    If Range("P" & x) <> "X" Then 'supposed to be: and column T contains anything except "X" (can be empty as well)
                        count = count + 1         'then count how many values meet these conditions
                        Range("B" & x).Interior.ColorIndex = 3 'supposed to be: Then change cell color in column F to red
                    End If
                End If
            End If
        End If
    Next x
    Range("F2") = count & " found"                'supposed to be: and display number in cell J2 with word "found"
End Sub

Checked and works fine, but there is one small issue - my mistake, I wasn't clear in my post - data in column F doesn't always match the length of data in column N.

 

[rollis13]

Why ? how can the difference be determined. At the moment the macro expects the cells to be identical in content, a simple extra space or upper/lower case determines a difference.
Please attach some 'real' examples and explain what should match and what not.

 

[Kra]

Why ? how can the difference be determined. At the moment the macro expects the cells to be identical in content, a simple extra space or upper/lower case determines a difference.
Please attach some 'real' examples and explain what should match and what not.

Yes, sorry. This is how whole report looks like:

In column F there is a list of values that need to be checked (unique values, no duplicates).
Export is pasted in column AE - AQ (starting from row 3, different length every time users uses this report)
Now I need to check value from column F - find it in column AE and determine if column AH has "X", column AO is not empty and if column AP has anything else except "X" (can be blank, can be B, can be anything but no "X").

If value meets all of these conditions, then it is highlighted red in column F and counter in cell J2 shows how many values are found.

Values in column AE are sometimes duplicated, but if any of them meet the conditions, it needs to be counted as well.



So in this case if you look at row 3 in columns AE - AQ, 9F0001990 has "X" in column AH, column AO is not empty and column AP is not "X". So this value meets all conditions, so it should be highlighted in column F.

I hope everything is clear now.

OB.xlsm
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
1				Materials check	Status																					ZOBSOLETE - STATUS
2				All listed																						Material	Plant	X-plant matl status	Plant-sp.matl status	DF at client level	DF at plant level	Follow-Up Material	Effective-out date	Total Stock	Reserve Quantity	Parent	Parent status	Preceding Material(s)
3	9F0001990		VB02 - LISTING		(9F only)								VD52 - CMIR		(9F only)											9F0001990	4020		X					0	0	9F0006678
4	9F0001991																									9F0001990	9010		X					0	0	9F0006677	X
5	9F0001992																									9F0001991	4020		X					0	0	9F0006677	X
6	9F0001993																									9F0001991	9010		X					0	0
7	9F0001994																									9F0001992	4020		X					0	0
8	9F0001995																									9F0001992	9010		X					0	0
9	9F0001996																									9F0001993	4020		X					0	0
10	9F0001997																									9F0001993	9010		X					0	0
11	9F0001998																									9F0001994	4020		X					0	0
12	9F0001999																									9F0001994	9010		X					0	0
13	9F0002000																									9F0001995	4020		X					0	0
14	9F0002001																									9F0001995	9010		X					0	0
15	9F0002002																									9F0001996	4020		X					0	0
16	9F0002003																									9F0002003	9010		X					0	0
17	9F0002004																									9F0001997	4020		X					0	0
18	9F0002005																									9F0001997	9010		X					0	0
19	9F0002006																									9F0001998	4020		X					0	0
20	9F0002007																									9F0001998	9010		X					0	0	9F0006677	X
21	9F0002008																									9F0001999	4020		X					0	0
22	9F0002009																									9F0001999	9010		X					0	0
23	9P0005223																									9F0002000	4020		X					0	0
24	9P0005224																									9F0002000	9010		X					0	0
25	9P0005225		VK12 - ZPR0, ZVOL, MPG			(9F only)							C223, QSR6 - ROUTINGS			(9F, 9W99, 9W00 only)										9F0002001	4020		X					0	0
26	9P0005226																									9F0002001	9010		X					0	0
27	9P0005227																									9F0002002	4020		X					0	0
28	9P0005228																									9F0002004	9010		X					0	0
29	9P0005229																										4020		X					0	0
30	9P0005230																										9010		X					0	0
31	9P0005231																										4020		X					0	0
32	9P0005232																										9010		X					0	0
33	9P0005233																									9F0002005	4020		X					0	0
34	9P0005234																									9F0002005	9010		X					0	0
35	9P0005235																									9F0002006	4020		X					0	0
36	9P0005236																									9F0002006	9010		X					0	0
37	9P0005237																									9F0002007	4020		X					0	0
38	9P0005238																									9F0002007	9010		X					0	0
39	9P0005239																									9F0002008	4020		X					0	0
40	9W9902361																									9F0002008	9010		X					0	0
41	9W9902362																									9F0002009	4020		X					0	0
42	9W9901597																									9F0002009	9010		X					0	0
43	9W9901523																									9P0005223	4020		X			9P0005223A		0	0
44	9W9901599																									9P0005223	9010		X			9P0005223A		0	0
45	9W9901617																									9P0005223	9040		X					0	0
46	9W9901600																									9P0005224	4020		X					325	0			9P0004940A
47	9W9901601																									9P0005224	9010		X					0	0			9P0004940A
48	9W9901607																									9P0005224	9040		X					0	0			9P0004940A
49	9W9901613																									9P0005225	4020		X					0	0
50	9W9901614																									9P0005226	4020		X					0	0
51	9W9901615																									9P0005226	9010		X					0	0
52	9W9901616																									9P0005226	9040		X					0	0
53	9W9901619																									9P0005227	4020		X					0	0
54	9W9901534																									9P0005228	4020		X					0	0	9F0001990	OB
55	9W9901621																									9P0005228	4020		X					0	0	9W9902648	OB
56	9W9901622																									9P0005229	4020		X					0	0	9W0000238	OB
57	9W9901623																									9P0005229	9010		X					0	0
58	9W9901565																									9P0005229	9040		X					0	0
59	9W9901579																									9P0005230	4020		X					0	0
60	9W0000175																									9P0005230	9010		X					0	0
61	9W0000176																									9P0005230	9040		X					0	0
Set to obsolete

 

[rollis13]

You once again changed the layout of your sheet !!!
Added a new loop to double-check on duplicates in column AE.

Option Explicit
Sub CountItems_Dup()
    Dim x      As Long
    Dim y      As Long
    Dim lrF    As Long
    Dim lrAE   As Long
    Dim count  As Long
    lrF = Range("F" & Rows.count).End(xlUp).Row
    lrAE = Range("AE" & Rows.count).End(xlUp).Row
    For x = 3 To lrF
        For y = 3 To lrAE
            If Range("F" & x) = Range("AE" & y) Then
                If UCase(Range("AH" & y)) = "X" Then
                    If Range("AO" & y) <> "" Then
                        If UCase(Range("AP" & y)) <> "X" Then
                            count = count + 1
                            Range("F" & x).Interior.ColorIndex = 3
                        End If
                    End If
                End If
            End If
        Next y
    Next x
    Range("J2") = count & " found"
End Sub

 

[Kra]

You once again changed the layout of your sheet !!!
Added a new loop to double-check on duplicates in column AE.

Option Explicit
Sub CountItems_Dup()
    Dim x      As Long
    Dim y      As Long
    Dim lrF    As Long
    Dim lrAE   As Long
    Dim count  As Long
    lrF = Range("F" & Rows.count).End(xlUp).Row
    lrAE = Range("AE" & Rows.count).End(xlUp).Row
    For x = 3 To lrF
        For y = 3 To lrAE
            If Range("F" & x) = Range("AE" & y) Then
                If UCase(Range("AH" & y)) = "X" Then
                    If Range("AO" & y) <> "" Then
                        If UCase(Range("AP" & y)) <> "X" Then
                            count = count + 1
                            Range("F" & x).Interior.ColorIndex = 3
                        End If
                    End If
                End If
            End If
        Next y
    Next x
    Range("J2") = count & " found"
End Sub

I am sorry, but what do you mean by once again? I used blank sheet as an example and I pointed to correct columns. Correct me please if I am wrong.

And thank you, this code works for me, thanks for help!

 

[rollis13]

Now I need to check value from column F - find it in column AE and determine if column AH has "X", column AO is not empty and if column AP has anything else except "X" (can be blank, can be B, can be anything but no "X").

This quoted sentence is from your post #5 and it doesn't seem to match your post #7.
Anyway, thanks for the positive feedback, glad having been of some help.