Mismatch error based on cell value help needed

[brolsen]

Hello,

This is driving me crazy and I cannot figure out what I am doing wrong. I have a code that checks a column for a value, once it locates that value it places some text in an adjacent cell 3 rows earlier. I have used the code before so I know it usually works, but in this one is not working and I cannot figure out what I need to do to adjust the code:

Sub Comments()'
' Comments Macro
'


'
      Dim Comments As String
      Dim msg As String
  msg = "Type in Comment -- To make blank select cancel."
      Comments = InputBox(msg)
  Sheets("Active_Teams").Select
      Dim LR As Long, i As Long
    With Sheets("Active_Teams")
        LR = .Range("F" & Rows.Count).End(xlUp).Row
        For i = 1 To LR
            With .Range("F" & i)
                If .Value = Range("H9").Value Then
                    .Offset(0, -3).Select
                    Selection.Value = Comments
                    Sheets("Program Change Distribution").Select
                    Exit Sub
                End If
            End With
        Next i
    End With
End Sub

The line that is showing mismatch error is:
If .Value = Range("H9").Value Then

Comments are string and the cell range H9 is a string so I cannot figure out why I keep getting the mismatch error or how to resolve this issue.
If someone can help me, I would really appreciate it!

 

[JackDanIce]

Try:

Sub Comments_v1()

' Comments Macro
Dim Comments    As String
Const msg1      As String = "Type in Comment -- To make blank select cancel."
Dim LR          As Long
Dim rng         As Range
Dim rngMatch    As Range

Comments = InputBox(msg1)

With Sheets("Active_Teams")
    LR = .Range("F" & .Rows.Count).End(xlUp).Row
    Set rngMatch = .Range("H9").Value
    With .Range("F1").Resize(LR)
        On Error Resume Next
        Set rng = .Find(what:=rngMatch, LookIn:=xlValues)
        On Error GoTo 0
    End With
End With

If Not rng Is Nothing Then
    rng.Offset(, -3).Value -Comments
    Sheets("Program Change Distribution").Select
End If
    
Set rng = Nothing: Set rngMatch = Nothing

End Sub

 

[brolsen]

Hi JackDanIce,

Thanks so much for writing me and attempting to resolve. When I run that, I get an error that says "Compile error: Invalid use of property" on this line:
rng.Offset(, -3).Value -Comments

Any ideas?

 

[brolsen]

Nevermind it's because it was -Comments instead of = Comments

YOU ARE THE MAN! THANK YOU!

 

[brolsen]

Hi JackDanIce,

I am getting a different error even after I fixed the -Comments to = Comments

I am getting "Run-time error '424': Object required" to the following:
Set rngmatch = .Range("H13").Value

Any ideas why?

 

[brolsen]

*Bump*

Just hoping someone can look at this today and maybe help? Can anyone alter either my code or JackDanIce's code to have this work? I would really appreciate any help here

 

[JackDanIce]

Is it H9 or H13? Your original code says H9. Anyway, try this (still as H9):

Sub Comments_v1()

' Comments Macro
Dim Comments    As String
Const msg1      As String = "Type in Comment -- To make blank select cancel."
Dim LR          As Long
Dim rng         As Range
Dim rngMatch    As Range

Comments = InputBox(msg1)

With Sheets("Active_Teams")
    LR = .Range("F" & .Rows.Count).End(xlUp).Row
    Set rngMatch = .Range("H9")
    With .Range("F1").Resize(LR)
        On Error Resume Next
        Set rng = .Find(what:=rngMatch, LookIn:=xlValues)
        On Error GoTo 0
    End With
End With

If Not rng Is Nothing Then
    rng.Offset(, -3).Value = Comments
    Sheets("Program Change Distribution").Select
End If
    
Set rng = Nothing: Set rngMatch = Nothing

End Sub