Run time error for Worksheet Change in VBA

[jtemp57]

I am trying to auto populate cells in column "R" based on the value in column "A". So if cell "A8" = "ABC" then cell "R8"= "XYZ". If cell "A8" is cleared then cell "R8" is cleared too. I have a dropdown data validation list in Cell "A8". My code is working halfway but gives me a couple of errors.

I get "Type Mismatch" when I put "Valve" in cell "A8"
I also get "Method 'Range' of object'_Worksheet" failed Error '1004'
Both errors occur on the same line....

Here is what I have thus far.....

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RowCount As Long, Sht As Worksheet
Set Sht = Sheets("WorkSheet-1")

If Not Intersect(Target, Range("A8:A51")) Is Nothing Then
   MinIsoMethodSelection
   End If
If Target.Value = "" And Target.Cells.Count = 1 And Not Intersect(Target, Range("A8:A51")) Is Nothing Then
   Sht.Range("S" & Target.row).ClearContents
    End If



End Sub


Private Sub MinIsoMethodSelection()
Dim RowCount As Long, Sht As Worksheet
Set Sht = Sheets("WorkSheet-1")

    With Sht
          
        If .Range("A8").Value <> "" And .Range("A9").Value = "" Then
            RowCount = 8
        ElseIf .Range("A8").Value <> "" And .Range("A9").Value <> "" Then
            RowCount = .Range("A8").End(xlDown).row
        End If
        
        If .Range("A" & RowCount).Offset(0, 17).Value = "PBB" Then 'Where Errors occur
        .Range("A" & RowCount).Offset(0, 18).Value = "B"
        ElseIf .Range("A" & RowCount).Offset(0, 17).Value = "AG" Then
        .Range("A" & RowCount).Offset(0, 18).Value = "AG"
        ElseIf .Range("A" & RowCount).Offset(0, 17).Value = "EB" Then
        .Range("A" & RowCount).Offset(0, 18).Value = "B"
        ElseIf .Range("A" & RowCount).Offset(0, 17).Value = "B" Then
        .Range("A" & RowCount).Offset(0, 18).Value = "B"
        ElseIf .Range("A" & RowCount).Offset(0, 17).Value = "CD" Then
        .Range("A" & RowCount).Offset(0, 18).Value = "CD"
        ElseIf .Range("A" & RowCount).Offset(0, 17).Value = "ARP" Then
        .Range("A" & RowCount).Offset(0, 18).Value = "ARP"
        ElseIf .Range("A" & RowCount).Offset(0, 17).Value = "SV" Then
        .Range("A" & RowCount).Offset(0, 18).Value = "SV"
        ElseIf .Range("A" & RowCount).Offset(0, 17).Value = "V" Then
        .Range("A" & RowCount).Offset(0, 18).Value = ""  'equal nothing
        End If
 
        
    End With
    
    
End Sub

 

[rlv01]

Both errors occur on the same line....

Which line?

 

[Jeffrey Mahoney]

This is how I would change the First SUB. What line were you getting the error?

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim RowCount As Long, Sht As Worksheet
  Set Sht = Sheets("WorkSheet-1")
 
  If Not Intersect(Target, Range("A8:A51")) Is Nothing Then
     MinIsoMethodSelection
    
    If Target.Cells.Count = 1 Then
      If Target.Value = "" Then
        Sht.Range("S" & Target.Row).ClearContents
      End If
    End If
  End If

End Sub

 

[jtemp57]

Which line?

If .Range("A" & RowCount).Offset(0, 17).Value = "PBB" Then

 

[jtemp57]

This is how I would change the First SUB. What line were you getting the error?

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim RowCount As Long, Sht As Worksheet
  Set Sht = Sheets("WorkSheet-1")
 
  If Not Intersect(Target, Range("A8:A51")) Is Nothing Then
     MinIsoMethodSelection
   
    If Target.Cells.Count = 1 Then
      If Target.Value = "" Then
        Sht.Range("S" & Target.Row).ClearContents
      End If
    End If
  End If

End Sub

Thanks for the response..I'll give it a try....I am getting the error in the other Sub on this line

If .Range("A" & RowCount).Offset(0, 17).Value = "PBB" Then

 

[RoryA]

There is probably an error value in the cell being processed.

 

[jtemp57]

There is probably an error value in the cell being processed.

Yes, that seems to throw the flag.....how do i work around that?

 

[RoryA]

Test for that first. What do you want to happen in that case?

 

[jtemp57]

Test for that first. What do you want to happen in that case?

Example:
I need cell S8 to populate based on cell R8. Cell R8 is changed based on what is changed in cell A8. (R8 is a formula) My code works for all except when "V" is entered into cell A8. This flags the formula in cell R8 with #N/A because the other factors are not met yet for that formula.

So i would just like cell S8 to be blank when "V" is entered into cell A8.

This is why I tried this code in the If statements:

ElseIf .Range("A" & RowCount).Offset(0, 17).Value = "V" Then
        .Range("A" & RowCount).Offset(0, 18).Value = ""  'equal nothing

 

[Jeffrey Mahoney]

My bet is this line is producing the last row in the sheet
RowCount = .Range("A8").End(xlDown).Row