Changing 2 cells based on another cell

  • Thread starter Thread starter Jase4now
  • Start date Start date
J

Jase4now

I have a spreadsheet that has teachers first names in column A and their last
names in column C. Their room number is in column F. I need to change the
teachers' first & last names based on the room number. The problem I am
encountering is there is about 75 teachers, much more than the IF function
will handle. For some reason when we pull our report out of the system to
Excel, the teachers names are not acurrate to the rest of the report.

Please help.

Jase4now
 
I have a spreadsheet that has teachers first names in column A and their last
names in column C. Their room number is in column F. I need to change the
teachers' first & last names based on the room number. The problem I am
encountering is there is about 75 teachers, much more than the IF function
will handle. For some reason when we pull our report out of the system to
Excel, the teachers names are not acurrate to the rest of the report.

Please help.

Jase4now
Click to expand...

Use the VLOOKUP function.

You will have to put the room number in column A though and then look
up the room number and return the teachers name. This is because (I
believe) VLOOKUP can only look left to right and not right to left.

Chrisso
 
Is there any code I can write that would do this automatically? I can't move
the room number, because I send this report to a different system. The
columns must stay where they are.

Thanks for trying though
 
You can write code to do anything. If you are asking someone to write
the code for you then you may be disappointed.

Psuedo code would be:
* walk over every entry in your export extracting the room number
* search for the room number in a lookup list on a seperate book/
sheet
---> the lookup list has the correct room number to teacher
mapping
* when you find the room number row then lookup the correct teacher
* enter this back into the export over the incorrect teacher
* move down a row at a time and repeat
* stop when there is no more data

Here are some code stubs for some of these steps:

Sub Find_Value(sValue As String)
Dim rFinder As Range
Set rFinder = YourSheet.Column(<ROOM NUMBER COLUMN>).Find(sValue)
if rFinder Is Nothing
MsgBox sValue & " cannot be found."
Else
MsgBox sValue & " found on row " & rFinder.Row
End If
End Sub

Sub Loop_Over_Data()
Dim rCell As Range
Set rCell = YourSheet.Cells(1, 1)
Do While rCell.Value <> vbNullString
' do something ====================

' next row of data:
Set rCell = rCell.Offset(1, 0)
Loop
End Sub

Note: this wont compile until you replace "YourSheet" with a valid
Worksheet object and "(<ROOM NUMBER COLUMN>" with a column number

Chrisso
 
Back
Top