Make duplicate cell in the next row

[omegadeltaphi]

I am trying to make a new cell in the same/next row over when the word "student" is in that column B.

Basically making a new column off the "B" column.

I only have 2 columns and many rows.

thx
Steve

 

[repairman615]

Hello,

Welcome to the board!!

Right click the sheet tab and select Veiw Code.
Paste in the following:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><br><SPAN style="color:#00007F">Dim</SPAN> rngBBadr <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">Set</SPAN> rngBBadr = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)<br><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> rngBBadr<br>        <SPAN style="color:#00007F">If</SPAN> UCase(cell.Value) = "STUDENT" <SPAN style="color:#00007F">Then</SPAN> cell.Offset(0, 1).Value = cell.Value<br>    <SPAN style="color:#00007F">Next</SPAN> cell<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Change a cell to test.
Be Sure to test on a copy.

Does that help?

 

[Rick Rothstein]

I am trying to make a new cell in the same/next row over when the word "student" is in that column B.

Basically making a new column off the "B" column.

I only have 2 columns and many rows.

This question is not entirely clear to me. Are you trying to put the word Student in Column A next to (same row) the word Student in Column B? The more details you can give us with your question, the better able we are to help you (otherwise we are just guessing). Also, before and after examples are quite useful too.

 

[omegadeltaphi]

I need to move the contents over from one column to the next column in their respective rows.

col A col B

info info
info info
student>>



student needs to be moved to column B

sorry 4 confusion

thx
Steve

 

[Rick Rothstein]

Give this macro a try....

Sub MoveStudents()
  On Error Resume Next
  With Columns("A")
    .Replace "Student", "=Student", xlWhole
    With .SpecialCells(xlCellTypeFormulas)
      .Offset(, 1).Value = "Student"
      .ClearContents
    End With
  End With
End Sub