ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Finding specific text in ranges (https://www.excelbanter.com/excel-discussion-misc-queries/127489-finding-specific-text-ranges.html)

timmulla
Finding specific text in ranges
 
I have a spreadsheet with data in columns G and H. Can anyone help me create
code that will loop through columns G and H and if it finds specific text in
either column, it will put the text "403 B" in column in column I. To make
things complicated the specific text in column G is just a small peace of
text in a larger sentence. The specific text in column G will be the entire
contents in the cell.

Specific text in column G:

403-B
403B
403(B)(7)
403(B)
403 (B)(7)
403B-7
403B7
403(B)(7)-PERSHING

Specific text in column H:

403-B GROUP
403-B INDIVIDUALLY ESTAB
K through 12 403 (B)
403(B)
403(b) Retirement Plan For Non-Profit Organizations
403-B SALARY REDUCTION
403-B SCHOOL DISTRICTS
501 (C) Other 403 (B)
SPAC 403-B GROUP
Non-Qualified 403(B)
TSA 403 (B)
TSA/403(b)(7)

Any help would be apppreciated.

--
Regards,

timmulla

Gary''s Student
Finding specific text in ranges
 
Try this:

Sub tim()
Dim h(12) As String, g(8) As String
h(0) = "403-B GROUP"
h(1) = "403-B INDIVIDUALLY ESTAB"
h(2) = "K through 12 403 (B)"
h(3) = "403(B)"
h(4) = "403(b) Retirement Plan For Non-Profit Organizations"
h(5) = "403-B SALARY REDUCTION"
h(6) = "403-B SCHOOL DISTRICTS"
h(7) = "501 (C) Other 403 (B)"
h(8) = "SPAC 403-B GROUP"
h(9) = "Non-Qualified 403(B)"
h(10) = "TSA 403 (B)"
h(11) = "TSA/403(b)(7)"

g(0) = "403-B"
g(1) = "403B"
g(2) = "403(B)(7)"
g(3) = "403(B)"
g(4) = "403 (B)(7)"
g(5) = "403B-7"
g(6) = "403B7"
g(7) = "403(B)(7)-PERSHING"

hstop = Range("H" & Rows.Count).End(xlUp).Row
gstop = Range("G" & Rows.Count).End(xlUp).Row
stopp = Application.WorksheetFunction.Max(gstop, hstop)
MsgBox (hstop)
MsgBox (gstop)
For i = 1 To stopp
v = Cells(i, "G").Value
For j = 0 To 7
If InStr(v, g(j)) 0 Then
Cells(i, "I").Value = "403B"
End If
Next

v = Cells(i, "H").Value
For j = 0 To 11
If InStr(v, h(j)) 0 Then
Cells(i, "I").Value = "403B"
End If
Next

Next

End Sub

--
Gary's Student
gsnu200702


"timmulla" wrote:

I have a spreadsheet with data in columns G and H. Can anyone help me create
code that will loop through columns G and H and if it finds specific text in
either column, it will put the text "403 B" in column in column I. To make
things complicated the specific text in column G is just a small peace of
text in a larger sentence. The specific text in column G will be the entire
contents in the cell.

Specific text in column G:

403-B
403B
403(B)(7)
403(B)
403 (B)(7)
403B-7
403B7
403(B)(7)-PERSHING

Specific text in column H:

403-B GROUP
403-B INDIVIDUALLY ESTAB
K through 12 403 (B)
403(B)
403(b) Retirement Plan For Non-Profit Organizations
403-B SALARY REDUCTION
403-B SCHOOL DISTRICTS
501 (C) Other 403 (B)
SPAC 403-B GROUP
Non-Qualified 403(B)
TSA 403 (B)
TSA/403(b)(7)

Any help would be apppreciated.

--
Regards,

timmulla

timmulla
Finding specific text in ranges
 
I'm getting a message box that pops with the number 2940 before the code
executes. Do you know what this is? Once I hit the O.K. button, the code
works great! Thanks for your help.
--
Regards,

timmulla


"Gary''s Student" wrote:

Try this:

Sub tim()
Dim h(12) As String, g(8) As String
h(0) = "403-B GROUP"
h(1) = "403-B INDIVIDUALLY ESTAB"
h(2) = "K through 12 403 (B)"
h(3) = "403(B)"
h(4) = "403(b) Retirement Plan For Non-Profit Organizations"
h(5) = "403-B SALARY REDUCTION"
h(6) = "403-B SCHOOL DISTRICTS"
h(7) = "501 (C) Other 403 (B)"
h(8) = "SPAC 403-B GROUP"
h(9) = "Non-Qualified 403(B)"
h(10) = "TSA 403 (B)"
h(11) = "TSA/403(b)(7)"

g(0) = "403-B"
g(1) = "403B"
g(2) = "403(B)(7)"
g(3) = "403(B)"
g(4) = "403 (B)(7)"
g(5) = "403B-7"
g(6) = "403B7"
g(7) = "403(B)(7)-PERSHING"

hstop = Range("H" & Rows.Count).End(xlUp).Row
gstop = Range("G" & Rows.Count).End(xlUp).Row
stopp = Application.WorksheetFunction.Max(gstop, hstop)
MsgBox (hstop)
MsgBox (gstop)
For i = 1 To stopp
v = Cells(i, "G").Value
For j = 0 To 7
If InStr(v, g(j)) 0 Then
Cells(i, "I").Value = "403B"
End If
Next

v = Cells(i, "H").Value
For j = 0 To 11
If InStr(v, h(j)) 0 Then
Cells(i, "I").Value = "403B"
End If
Next

Next

End Sub

--
Gary's Student
gsnu200702


"timmulla" wrote:

I have a spreadsheet with data in columns G and H. Can anyone help me create
code that will loop through columns G and H and if it finds specific text in
either column, it will put the text "403 B" in column in column I. To make
things complicated the specific text in column G is just a small peace of
text in a larger sentence. The specific text in column G will be the entire
contents in the cell.

Specific text in column G:

403-B
403B
403(B)(7)
403(B)
403 (B)(7)
403B-7
403B7
403(B)(7)-PERSHING

Specific text in column H:

403-B GROUP
403-B INDIVIDUALLY ESTAB
K through 12 403 (B)
403(B)
403(b) Retirement Plan For Non-Profit Organizations
403-B SALARY REDUCTION
403-B SCHOOL DISTRICTS
501 (C) Other 403 (B)
SPAC 403-B GROUP
Non-Qualified 403(B)
TSA 403 (B)
TSA/403(b)(7)

Any help would be apppreciated.

--
Regards,

timmulla

Gary''s Student
Finding specific text in ranges
 
These messages come from the two lines that start with

MsgBox

The macro tells you, before running, how far down it is goingto go. You can
remove these two lines if you want
--
Gary's Student
gsnu200702


"timmulla" wrote:

I'm getting a message box that pops with the number 2940 before the code
executes. Do you know what this is? Once I hit the O.K. button, the code
works great! Thanks for your help.
--
Regards,

timmulla


"Gary''s Student" wrote:

Try this:

Sub tim()
Dim h(12) As String, g(8) As String
h(0) = "403-B GROUP"
h(1) = "403-B INDIVIDUALLY ESTAB"
h(2) = "K through 12 403 (B)"
h(3) = "403(B)"
h(4) = "403(b) Retirement Plan For Non-Profit Organizations"
h(5) = "403-B SALARY REDUCTION"
h(6) = "403-B SCHOOL DISTRICTS"
h(7) = "501 (C) Other 403 (B)"
h(8) = "SPAC 403-B GROUP"
h(9) = "Non-Qualified 403(B)"
h(10) = "TSA 403 (B)"
h(11) = "TSA/403(b)(7)"

g(0) = "403-B"
g(1) = "403B"
g(2) = "403(B)(7)"
g(3) = "403(B)"
g(4) = "403 (B)(7)"
g(5) = "403B-7"
g(6) = "403B7"
g(7) = "403(B)(7)-PERSHING"

hstop = Range("H" & Rows.Count).End(xlUp).Row
gstop = Range("G" & Rows.Count).End(xlUp).Row
stopp = Application.WorksheetFunction.Max(gstop, hstop)
MsgBox (hstop)
MsgBox (gstop)
For i = 1 To stopp
v = Cells(i, "G").Value
For j = 0 To 7
If InStr(v, g(j)) 0 Then
Cells(i, "I").Value = "403B"
End If
Next

v = Cells(i, "H").Value
For j = 0 To 11
If InStr(v, h(j)) 0 Then
Cells(i, "I").Value = "403B"
End If
Next

Next

End Sub

--
Gary's Student
gsnu200702


"timmulla" wrote:

I have a spreadsheet with data in columns G and H. Can anyone help me create
code that will loop through columns G and H and if it finds specific text in
either column, it will put the text "403 B" in column in column I. To make
things complicated the specific text in column G is just a small peace of
text in a larger sentence. The specific text in column G will be the entire
contents in the cell.

Specific text in column G:

403-B
403B
403(B)(7)
403(B)
403 (B)(7)
403B-7
403B7
403(B)(7)-PERSHING

Specific text in column H:

403-B GROUP
403-B INDIVIDUALLY ESTAB
K through 12 403 (B)
403(B)
403(b) Retirement Plan For Non-Profit Organizations
403-B SALARY REDUCTION
403-B SCHOOL DISTRICTS
501 (C) Other 403 (B)
SPAC 403-B GROUP
Non-Qualified 403(B)
TSA 403 (B)
TSA/403(b)(7)

Any help would be apppreciated.

--
Regards,

timmulla


All times are GMT +1. The time now is 04:43 AM.

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