CODEWITHSUNDEEP
excelstringvbacompare
RoboNerd
RoboNerd

Reputation: 25

excel string comparison issue

So I am trying to create a pretty simple macro that loops through a certain row looking for a value which is identified in a different sheet. Once that value is found it starts to look in that resultant column for a text string. If the text string is found, it grabs the value within the same row but a couple columns over and writes it to the sheet which has the user defined look up value. My problem is that for some reason when I am trying to compare the strings my conditional if statement is not being achieved, meaning that the part which writes the final value is never being evaluated.Here is the code and thanks in advance. 

Private Sub valchange()
Dim keycells As Range
Set keycells = Worksheets("Dashboard").Range("B1")
Dim k As Integer
k = 5
        For I = 1 To 50
            If Worksheets("Rework List").Cells(3, I).Value = keycells.Value Then
                For j = 14 To 50
                    If UCase(Worksheets("Rework List").Cells(j, I)) = "*PENDING*" Then
                            Cells(j, 3).Value = Worksheets("Dashboard").Range("F" & k)
                            k = k + 1
                    End If
                Next j
            End If
        Next I
End Sub

Upvotes: 1

Views: 98

Answers (2)

user3598756
user3598756

Reputation: 29421

other than what @vacip already told you, I'd point out the following

you wrote:

it grabs the value within the same row but a couple columns over and writes it to the sheet which has the user defined look up value

then I think:

Cells(j, 3).Value = Worksheets("Dashboard").Range("F" & k)

would do quite the opposite (provided the active sheet at macro start is "Rework List")!

moreover its' better to:

  • declare rows index variables as of Long type

    though not strictly necessary in your specific case (they range to a maximum of 50, which is handled by an Integer type), it's quite common you would eventually face some 33k+ rows, and that's where Long type variable must enter the scene

  • store the keyword value instead of its range, and avoid all subsequent accesses to it with keycells.Value

  • always adopt fully qualified range references up to the worksheet object at least (if not the workbook one)

so you could try this:

Option Explicit

Private Sub valchange()
    Dim keyVal As String
    Dim k As Long, i As Long, j As Long

    keyVal = CStr(Worksheets("Dashboard").Range("B1").Value)
    k = 5
    With Worksheets("Rework List")
        For i = 1 To 50
            If .Cells(3, i).Value = keyVal Then
                For j = 14 To 50
                    If InStr(.Cells(j, i), "pending", vbTextCompare) > 0 Then
                        Worksheets("Dashboard").Range("F" & k) = .Cells(j, 3).Value
                        k = k + 1
                    End If
                Next j
            End If
        Next i
    End With
End Sub

Upvotes: 1

vacip
vacip

Reputation: 5406

You can't use joker characters in string comparisons in VBA like that.

Instead, use the Instr() function to search for a string within another string.

If instr(1,Worksheets("Rework List").Cells(j, I),"pending",vbTextCompare)>0 Then

If the search string can be found within the second string, instr returns the first character's position. If it can't be found, it returns a 0. So if instr()>0 that means the string is there.

vbTextCompare makes sure casing (upper/lower case) is ignored. The 1 at the beginning specifies the starting position for the search.

Note that this is not the fastest thing in the world...

Upvotes: 0

Related Questions