CODEWITHSUNDEEP
excelvba
aMadNoob
aMadNoob

Reputation: 25

VBA Code to change cell values to Search function..?

I'm looking to change the values of a range of cells (Column D) to have the Search function for a certain string of characters in the cell next to it (Column C). My current code doesn't seem to work as it only works if the cell in Column D equals the exact string of characters rather than just contains it.

The original formula that I'm trying to write up in code:

=IF(ISNUMBER(SEARCH("1150",$C11)), "Dozer", IF(ISNUMBER(SEARCH("1650",$C11)),"Dozer", IF(ISNUMBER(SEARCH("2050",$C11)), "Dozer", IF(ISNUMBER(SEARCH("850",$C11)), "Dozer", IF(ISNUMBER(SEARCH("750",$C11)), "Dozer", IF(ISNUMBER(SEARCH("650",$C11)), "Dozer"))))))

My current code:

Function CalcValue(pVal As String) As Long



   If InStr(pVal, "2050") <> 0 Then
      CalcValue = "Dozer"

   ElseIf InStr(pVal, "1650") <> 0 Then
      CalcValue = "Dozer"

   ElseIf InStr(pVal, "1150") <> 0 Then
      CalcValue = "Dozer"

   ElseIf InStr(pVal, "850") <> 0 Then
      CalcValue = "Dozer"

   ElseIf InStr(pVal, "750") <> 0 Then
      CalcValue = "Dozer"

   ElseIf InStr(pVal, "650") <> 0 Then
      CalcValue = "Dozer"


   Else
      CalcValue = "TLB"
   End If

End Function

Upvotes: 0

Views: 87

Answers (2)

tigeravatar
tigeravatar

Reputation: 26640

If you wanted to keep using native Excel formulas instead of introducing VBA, then this formula is a condensed and more easily update-able version of your original:

=IF(SUMPRODUCT(--ISNUMBER(FIND({650,750,850,1150,1650,2050},$C11)))>0,"Dozer","TLB")

If it must be VBA, you can implement a loop instead of manually writing out each possibility:

Public Function CalcValue(ByVal arg_sText As String) As String

    Dim aSearchValues() As Variant
    aSearchValues = Array(650, 750, 850, 1150, 1650, 2050)

    Dim vSearchVal As Variant
    For Each vSearchVal In aSearchValues
        If InStr(1, arg_sText, vSearchVal, vbBinaryCompare) > 0 Then
            CalcValue = "Dozer"
            Exit Function
        End If
    Next vSearchVal

    CalcValue = "TLB"

End Function

Upvotes: 1

aMadNoob
aMadNoob

Reputation: 25

Fixed code with the help of @BigBen

Public Function CalcValue(pVal As String) As String



   If InStr(pVal, "2050") <> 0 Then
      CalcValue = "Dozer"

   ElseIf InStr(pVal, "1650") <> 0 Then
      CalcValue = "Dozer"

   ElseIf InStr(pVal, "1150") <> 0 Then
      CalcValue = "Dozer"

   ElseIf InStr(pVal, "850") <> 0 Then
      CalcValue = "Dozer"

   ElseIf InStr(pVal, "750") <> 0 Then
      CalcValue = "Dozer"

   ElseIf InStr(pVal, "650") <> 0 Then
      CalcValue = "Dozer"


   Else
      CalcValue = "TLB"
   End If

End Function

Upvotes: 0

Related Questions