VBA- Changing cell contents based on another cell in the same row

[KnoxQual]

Hi, I'm very new to VBA and have been learning through books and google for the last week. I've run into a problem with one of my projects, I'm hoping someone can help.

I'm trying to create a code that will look at the value (which will be "A","S", or "R") in a cell in the O column, and change the value in the corresponding B column to what is in the corresponding BD column.

For example, if O10 is A, nothing happens. If O10 is R or S, then B10 will show what is in BD10.

The data is only in Rows 10 - 77, the rest of the sheet has other information so that it can be printed and sent to our customer, so I really need the code to only run on Rows 10-77.

This is what I have, it keeps throwing a Type Mismatch error.

Private Sub ChangeToREJ()

Dim i As Integer


For i = 10 To 77


      If Range("O" & i).Text = "R" Or "S" Then
            Range("B" & 10).Text = Range("BD" & i).Text
      End If
Next i


End Sub

Thanks in advance for any help!

 

[Trevor G]

Try this

Sub ChangeToREJ()
Dim i As Integer
For i = 10 To 77

If Range("o" & i) = "R" Or Range("o" & i) = "S" Then
Range("B" & i) = Range("BD" & i)
End If
Next i
End Sub

 

[KnoxQual]

I'm no longer getting an error, but it also isn't changing the cell contents... Any suggestions to help with that?

Thanks for your help!

 

[KnoxQual]

SOLVED! I realized that it worked if I ran it as a macro, but not automatically. I added it to my Worksheet Change Event code. The code now looks like this, and works perfectly. Thank you for your help.

Private Sub Worksheet_Change(ByVal Target As Range)

'Does the changed range intersect the specified range?
    If Intersect(Target, Range("K10:L77")) Is Nothing Then
    
            'If there is no intersection, skip to next section
            GoTo changeREJ
            
'If there is an intersection, save the workbook
    Else
            ActiveWorkbook.Save
            
'Close If statement
    End If
   
'***Code to change part number to REJ***
'Does the change intersect the Reject cells?
changeREJ: If Intersect(Target, Range("O10:O77")) Is Nothing Then
                  
                  'Exit sub if no intersection
                  Exit Sub
            'If intersection, cycle through all rows and change part number to REJ if rejected or scrapped
            Else
                  Dim i As Integer
                  For i = 10 To 77 '10 to 77 are rows with data entered by operators


                        If Range("o" & i) = "R" Or Range("o" & i) = "S" Then
                        Range("B" & i) = Range("BD" & i)
                        End If
                  Next i
            End If


End Sub