VBA 'if/then' Code Error

[kelsy]

Hello!

Today is my first day exploring VBA coding, and I'm having trouble with the below 'if/then' VBA code (also attached a screenshot).

Private Sub Test()


If Range("D2:D200").Value = "FedEx" Then
Range("E2:E200").Value = "Marketing"


End If

End Sub

In short, what I need is for data in Column E to automatically populate according to what is in Column D. For example, if any rows in Column D read "Fedex", then the corresponding rows in Column E will populate with the word "Marketing."

The error message I keep receiving is "Run-time error: Type '13': Type mismatch", so I thought it would be better to reach out for help rather than continue screwing up on my own.

If it's helpful, I'm currently doing this from a 2019 Macbook Pro, and my version of Excel is 16.68, licensed through Microsoft 365.

Thank you in advance for any help whatsoever!

 

[MARK858]

If you were a bit more advanced I would use something like the autofilter so it didn't loop, but just as a basic try...

Private Sub Test()
    Dim mycell As Range
    Application.ScreenUpdating = False
   
    For Each mycell In Range("D2:D200")
        If mycell.Value = "FedEx" Then mycell.Offset(, 1).Value = "Marketing"
    Next
   
    Application.ScreenUpdating = True
End Sub

 

[kelsy]

If you were a bit more advanced I would use something like the autofilter so it didn't loop, but just as a basic try...

Private Sub Test()
    Dim mycell As Range
    Application.ScreenUpdating = False
  
    For Each mycell In Range("D2:D200")
        If mycell.Value = "FedEx" Then mycell.Offset(, 1).Value = "Marketing"
    Next
  
    Application.ScreenUpdating = True
End Sub

That worked! Thanks so much for your help, I really appreciate it.

I have another question, if you have the time––when it comes to uppercase/lowercase (or in the case of the word "FedEx", all of the above), is there a specific code that can be added into something like this to make the case of X word irrelevant? e.g., instead of the code only responding to "FedEx", it would also pick up on and correct "fedex"/"FEDEX"/"feDeX"

 

[MARK858]

Private Sub Test()
    Dim mycell As Range
    Application.ScreenUpdating = False
   
    For Each mycell In Range("D2:D200")
        If LCase(mycell.Value) = "fedex" Then mycell.Offset(, 1).Value = "Marketing"
    Next
   
    Application.ScreenUpdating = True
End Sub