CODEWITHSUNDEEP
vbaexcel
L.Dutch
L.Dutch

Reputation: 966

Write a string when content of two other cells matches a reference

I want my macro to check if the content of two cells matches a reference, and when it happens write a certain text into a third cell.

This is the code I wrote

lastRow = Sheets("AAA").Range("B1000").End(xlUp).Row
If Sheets("AAA").Range("E5") = "" Then
    id = 1 'identifier
    For i = 5 To lastRow
        sampleFunction = Sheets("AAA").Range("B" & i).Value
        For j = 5 To lastRow
            sampleFailure = Sheets("AAA").Range("C" & j).Value
            For m = 5 To lastRow
                counter = 0
                For n = 5 To lastRow
                    If Sheets("AAA").Range("B" & m).Value = sampleFunction And Sheets("AAA").Range("C" & n).Value = sampleFailure And Sheets("AAA").Range("J" & n) = "" Then
                        Sheets("AAA").Range("J" & n) = ("DDF" & id)
                        counter = counter + 1
                    End If
                Next n
                If counter > 0 Then
                    id = id + 1
                End If
            Next m
        Next j
    Next i
End If

The weird thing is that in some cases I get a correspondence where it is not present, like in the example below for line 5 and 10 enter image description here

I have the impression the problem is in

If Sheets("AAA").Range("B" & m).Value = sampleFunction And Sheets("AAA").Range("C" & n).Value = sampleFailure And Sheets("AAA").Range("J" & n) = "" Then

But I am not able to spot it.

Upvotes: 0

Views: 31

Answers (1)

Vityata
Vityata

Reputation: 43595

sampleFunction and sampleFailure should be in inverted commas like this:

If Sheets("AAA").Range("B" & m).Value = "sampleFunction"

Otherwise Excel thinks that they are variables. If they are variables, then try to put parenthesis around your code, it may work better. Like this:

If (condition) And (condition) And (condition) then

Last case, debug.print each condition before checking it. Like this: debug.print Sheets("AAA").Range("B" & m).Value = sampleFunction

Upvotes: 1

Related Questions