ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Loop for text check in three colums and set a text code based on thisin another column (https://www.excelbanter.com/excel-programming/454269-loop-text-check-three-colums-set-text-code-based-thisin-another-column.html)

[email protected] April 16th 19 06:41 PM

Loop for text check in three colums and set a text code based on thisin another column
 
Simple loop required for handling a enormous amount of data :)
If somebody got a free moment,
thx

For a long list with data in column A till Z I want to set a text in column Z based on a part of text in three other columns. The macro should find the text within text in a cell.

In honkiedonkie explanation it is like;

-----------

Loop in column A for all rows from second to last

If (li, co+13) contains text "AAA"
and (li, co+21) contains text "BBB"
and (li, co+22) contains text "CCC"
then set (li,co+25) the text "A"

Else
If (li, co+13) contains text "DDD"
and (li, co+13) does not contains text "EEE"
and (li, co+22) contains text "FFF"
then set (li,co+25) the text "B"

end if

If (li, co+25) is empty
then set (li, co+25) the text "C"
else
end if

next li

-------------

regards, Johan




Claus Busch April 16th 19 07:27 PM

Loop for text check in three colums and set a text code based on this in another column
 
Hi Johan,

Am Tue, 16 Apr 2019 10:41:42 -0700 (PDT) schrieb :

Loop in column A for all rows from second to last

If (li, co+13) contains text "AAA"
and (li, co+21) contains text "BBB"
and (li, co+22) contains text "CCC"
then set (li,co+25) the text "A"

Else
If (li, co+13) contains text "DDD"
and (li, co+13) does not contains text "EEE"
and (li, co+22) contains text "FFF"
then set (li,co+25) the text "B"

end if

If (li, co+25) is empty
then set (li, co+25) the text "C"
else
end if


I don't know if I understood you correctly.
If the following code does not work for your problem explain a little
more.

Sub Test()
Dim LRow As Long, i As Long
Dim varData As Variant, varOut() As Variant

With ActiveSheet
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
varData = .Range("A2:Y" & LRow)
ReDim varOut(UBound(varData) - 1)
For i = LBound(varData) To UBound(varData)
If varData(i, 14) & varData(i, 22) & varData(i, 23) = "AAABBBCCC" Then
varOut(i - 1) = "A"
ElseIf varData(i, 14) & varData(i, 23) = "DDDFFF" _
And varData(i, 22) < "EEE" Then
varOut(i - 1) = "B"
Else
varOut(i - 1) = "C"
End If
Next
.Range("Z2").Resize(UBound(varOut) + 1) =
Application.Transpose(varOut)
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

[email protected] April 16th 19 08:10 PM

Loop for text check in three colums and set a text code based onthis in another column
 
Thanks!.

The thing thats goes wrong now is that the text in the mentioned columns isn't only the letters I wrote. Those letters are a part of the text within that specific cell.
So the text in li,co+13 could be "Blabla AAA blabla"
The text in li,co+22 could be "BBB Bla bla"
The text in li,co+23 could be "Bla CCC"

The macro has to check if the mentioned text is a part of the whole text in the cell. If in all three columns that specific text is found then...... it results in a code.

regards (and thanks!), Johan

Claus Busch April 17th 19 07:27 AM

Loop for text check in three colums and set a text code based on this in another column
 
Hi Johan,

Am Tue, 16 Apr 2019 12:10:45 -0700 (PDT) schrieb :

The thing thats goes wrong now is that the text in the mentioned columns isn't only the letters I wrote. Those letters are a part of the text within that specific cell.
So the text in li,co+13 could be "Blabla AAA blabla"
The text in li,co+22 could be "BBB Bla bla"
The text in li,co+23 could be "Bla CCC"

The macro has to check if the mentioned text is a part of the whole text in the cell. If in all three columns that specific text is found then...... it results in a code.


then try:

Sub Test()
Dim LRow As Long, i As Long
Dim varData As Variant, varOut() As Variant

With ActiveSheet
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
varData = .Range("A2:Y" & LRow)
ReDim varOut(UBound(varData) - 1)
For i = LBound(varData) To UBound(varData)
If InStr(varData(i, 14), "AAA") 0 And InStr(varData(i, 22), "BBB") 0 _
And InStr(varData(i, 23), "CCC") 0 Then
varOut(i - 1) = "A"
ElseIf InStr(varData(i, 14), "DDD") 0 And InStr(varData(i, 23), "FFF") 0 _
And InStr(varData(i, 22), "EEE") = 0 Then
varOut(i - 1) = "B"
Else
varOut(i - 1) = "C"
End If
Next
.Range("Z2").Resize(UBound(varOut) + 1) = Application.Transpose(varOut)
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

[email protected] April 17th 19 07:33 PM

Loop for text check in three colums and set a text code based onthis in another column
 
Hello Claus,

Sorry delay reaction. Long working day :)
Tried and a bit changed to fulfill my wishes.
It works fantastic. Also the way you created it for simple understanding.
Many many many thanks !!!

regards, Johan


All times are GMT +1. The time now is 09:00 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
ExcelBanter.com