How to Construct Auto Boolean Strings using VBA

[satish78]

Hi Friends,

I tried looking mrexcel posts and google sources for query to find VBA answer.
Here what I am looking for is:

Sheet1 ColumnA has data of boolean strings like attached in the document.
The row numbers varies every time whenever I change the data.
Boolean example has shown in image attached.

I need a VBA to do the job.
Let me know if you have questions.

Hightail Spaces

Hightail Spaces lets you send unlimited files, of any size, to anyone. Collaborate with your team and evolve your work.

spaces.hightail.com

 

[mse330]

Try the below code

Sub findNot()

For x = 1 To ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    c = 1
    If InStr(Cells(x, 1), "NOT") > 0 Then
        For y = 1 To UBound(Split(Cells(x, 1), " "))
            c = c + Len(Split(Cells(x, 1), " ")(y - 1)) + 1
            If Split(Cells(x, 1))(y) = "NOT" Then Cells(x, 1).Characters(c, 3).Font.Color = vbRed
        Next y
    End If
Next

End Sub

 

[YasserKhalil]

Can you explain the logic with more details? The sample file is different from the snapshot attached.

 

[mse330]

Hala Yasser ... I have added explanation after each line

Sub findNot()

For x = 1 To ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row '<--- identify the range to be searched
    c = 1
    If InStr(Cells(x, 1), "NOT") > 0 Then '<--- if the cell doesn't include the word NOT then skip it
        For y = 1 To UBound(Split(Cells(x, 1), " ")) '<--- looping through each world in the cell identified by space separation
            c = c + Len(Split(Cells(x, 1), " ")(y - 1)) + 1 '<--- checking the total number of charecters in the cell, so far + 1 for the proceeding space
            If Split(Cells(x, 1))(y) = "NOT" Then Cells(x, 1).Characters(c, 3).Font.Color = vbRed '<--- checking if the word is "NOT" to color it red c=where to start coloring from & 3 # of letters of the word NOT
        Next y
    End If
Next

End Sub

 

[YasserKhalil]

Thanks a lot for the explanation but I don't mean your code (I can get it well). I think the OP needs to construct and build strings based on the values of column A and get the desired output as in the picture. I am not sure

 

[mse330]

My bad I didn't check the file the OP posted ?

 

[satish78]

Thanks a lot for the explanation but I don't mean your code (I can get it well). I think the OP needs to construct and build strings based on the values of column A and get the desired output as in the picture. I am not sure

You are right YesserKhalil. I want to construct and build strings based on the values of ColumnA and get the desired output as shown in the picture.

 

[mse330]

Can you elaborate more on how cells in column A become the desired output in column B based on your excel file & the posted picture

Boolean String (1).xlsx
	A	B
1	Data	Expected output
2	Java	A and B and C and D and E and F
3	python	A and B and C and D and E and NOT F
4	j2ee	A and B and C and D and NOT E and F
5	jsp	A and B and C and NOT D and E and F
6	jdbc	A and B and C and NOT D and NOT E and F
7	jvm	A and B and C and D and NOT E and NOT F
8	angularjs	A and B and C and NOT D and NOT E and NOT F
9	html
10	css
11	xml
Sheet1

 

[satish78]

Can you elaborate more on how cells in column A become the desired output in column B based on your excel file & the posted picture

Boolean String (1).xlsx
	A	B
1	Data	Expected output
2	Java	A and B and C and D and E and F
3	python	A and B and C and D and E and NOT F
4	j2ee	A and B and C and D and NOT E and F
5	jsp	A and B and C and NOT D and E and F
6	jdbc	A and B and C and NOT D and NOT E and F
7	jvm	A and B and C and D and NOT E and NOT F
8	angularjs	A and B and C and NOT D and NOT E and NOT F
9	html
10	css
11	xml
Sheet1

Using AND NOT Operators.

 

[mse330]

I am sorry but I'm still not able to understand ! Perhaps you can give more details of how jvm becomes A and B and C and D and NOT E and NOT F or maybe some other forum members could understand something that I am missing