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

 

[jtemp57]

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

So is there a different route to this?

 

[jtemp57]

3

 

[RoryA]

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.

Change the routine to something like this:

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
        Dim newVal as string
        If IsError(.Range("A" & RowCount).Offset(0, 17).Value) then
           newVal = ""
        Else
          Select Case .Range("A" & RowCount).Offset(0, 17).Value
             Case  "PBB", "EB", "B"
                newVal = "B"
             Case "V"
                newVal = ""
             Case Else
                newVal = .Range("A" & RowCount).Offset(0, 17).Value
           End Select
        End If      
        .Range("A" & RowCount).Offset(0, 18).Value = newVal
    End With
   
End Sub

 

[jtemp57]

Ok I figured out how to stop the error due to the bad value in column R. I changed my formula in column R and added the =IfError statement to the beginning and now it does not show the "#N/A" so; therefore, I no longer get the error "Type Mismatch".

I do still get the "Method 'Range' of object'_Worksheet" failed Error '1004' but only when I remove the contents in the top most line. Just for info purposes. I am dealing with the range "A8:A51", these are the cells that change and affect column R.


Everything else seems to work just as I need except this error when the first line (cell A8) is cleared.

 

[RoryA]

If A8 is empty, you never assign a value to rowCount. You should probably default it to 8.

 

[jtemp57]

If A8 is empty, you never assign a value to rowCount. You should probably default it to 8.

Not sure what you mean by that?

 

[RoryA]

Either start with rowcount = 8 or alter the change event so that it doesn't call the MinIsoMethodSelection routine at all if the cell is blank.

 

[jtemp57]

Either start with rowcount = 8 or alter the change event so that it doesn't call the MinIsoMethodSelection routine at all if the cell is blank.

It worked by starting rowcount = 8! thank you for your help!