VBA search partial text and copy to a variable number of columns

ilikered100

New Member
Joined
May 29, 2013
Messages
46
OK... so here's what I need that I can't figure out. Below in Column A is a list of Students with their teacher's name. In column G is a list of Teacher's names for this worksheet. In H2, I have a counta formula which tells me the number of teachers for this grade.

I need a macro that will search column A, starting in row 2, for a teacher's name (partial text **) and paste that cell into it's corresponding column. so Jeff Doe/Brown and Patty Cake/Brown will be copied into column B (cells B2 and B3 respectively). Sally Seashell/Lewis and Jenny Jam/Lewis will be pasted into column C (cells, C2 and C3 respectively)... and so forth until it has done it the number of times that equals the variable in H2.

Also, I would like the search for partial text (Teacher Name in Row 1) to reference the text in cells B1, C1, D1, etc. instead of searching for the actual text of the teacher's name.

Lastly, I'm not sure the counta formula is needed. There may be a better way to do this, I'm just not sure what it is.

I need to accomplish this on several different spreadsheets with varying numbers of students and teachers. Any help is much appreciated.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Student/Teacher[/TD]
[TD]Brown[/TD]
[TD]Lewis [/TD]
[TD]Peterson [/TD]
[TD]Smith[/TD]
[TD][/TD]
[TD] Teacher List[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John Doe/Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Brown[/TD]
[TD]=counta(F2:F10)
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jeff Doe/Brown[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Lewis[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Suzie Que/Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Peterson[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sally Seashell/Lewis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Smith[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Jenny Jam/Lewis[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Helen Hill/Peterson[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Patty Cake/Brown[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Try this
Code:
Sub test()
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To 5
    x = Len(Cells(1, i))
    counter = 0
    For k = 2 To lr
        If Right(Cells(k, 1), x) = Cells(1, i) Then
            counter = counter + 1
        End If
    Next k
    Cells(i, 8) = counter
Next i
End Sub
 
Upvote 0
Thanks for the response. I know some about VBA, but am less comfortable with variables in VBA. Could you explain the above code and variables so that I can adapt to my needs? The fake example I gave is a much simplified version of what I will actually be working with. Some of it I understand, but some of it I don't.

For instance... why is the Len function needed?

Thanks again for your response.
 
Upvote 0
I used len to find the number of characters in the teacher name so I could look at that many characters from the right of student/teacher in order to compare them.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top