Trying to replace values in column B with values from column I

[2Plan]

I'm trying to replace values in Column B with Values from Column I.

Can someone help me debug the code below?

Sub Edit_APC_Data()
For Each c In Worksheets("Sheet1").Range("B2:B14786").Cells
Select Case Range("I2:I14786").Value
Case "UTFIN"
Range(c).Value = "FIN"
Case "UTNOSCH"
Range(c).Value = "NO SCHOOL"
Case "UTREG"
Range(c).Value = "REGIST"
Case Else
Range(c).Value = "WKDAY"
End Select
Next
End Sub

 

[AnotherHiggins]

Unless this is a one-time task that will never be repeated, don't just blindly use Range("B2:B14786"). Instead, you can find the last populated row in column I.

For each loop, you want to look at the value of a cell. "Range("I2:I14786").Value" doesn't make any sense.

Also, you are setting "c" to be a cell. so you need to refer to it as such. "Range(c).Value" should be "c.Value".

I've changed "c" to "rngCell" because it is more descriptive.

Sub Edit_APC_Data()
    Dim rngCell As Range
    
    intLstRow = Range("I" & ActiveSheet.Rows.Count).End(xlUp).Row
    For Each rngCell In Worksheets("Sheet1").Range("B2:B" & intLstRow).Cells
        Select Case rngCell.Offset(, 7).Value
            Case "UTFIN"
                rngCell.Value = "FIN"
            Case "UTNOSCH"
                rngCell.Value = "NO SCHOOL"
            Case "UTREG"
                rngCell.Value = "REGIST"
            Case Else
                rngCell.Value = "WKDAY"
        End Select
    Next rngCell
End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[2Plan]

AnotherHiggins,

Thanks, this code works great!!!