Loop for text check in three colums and set a text code based on thisin another column
View Single Post
April 17th 19, 07:27 AM posted to microsoft.public.excel.programming
external usenet poster
First recorded activity by ExcelBanter: Apr 2011
Loop for text check in three colums and set a text code based on this in another column
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.
Dim LRow As Long, i As Long
Dim varData As Variant, varOut() As Variant
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"
varOut(i - 1) = "C"
.Range("Z2").Resize(UBound(varOut) + 1) = Application.Transpose(varOut)
Reply With Quote
View Public Profile
Find all posts by Claus Busch