Search 2 values in one row vba

  • Hello everyone,
    First I have to tell that there are maybe mistakes in my English. English isn't my first language and is sometimes difficult for me.

    I'm working on an Excel worksheet, and I have troubles with a vba for this sheet.

    The mean of this code is that it looks for 2 different values in cell D6 and D8, if both cells found in one row in another sheet then it have to change some cells. If both cells not found in a row, then it have to do someting else.

    I hope you understand this. I use now this code. It goes on the red row in the code. Here it is search for the cell D6. But it has to also search to the cell D8

    I hope there is someone who can helps me with this problem.

  • Hello there, for this part of the code

    Set klant = .Range("A:P").Find((Range("D6")), after:=Sheets("tarieflijst").Range("A2"), SearchOrder:=xlByRows, searchdirection:=xlPrevious)

    Is there a reason you set the parameters as "SearchOrder:=xlByRows, SearchDirection:=xlPrevious"? It seems weird you are running the find by rows method from row 2 but in reverse order... perhaps you can use this instead?

    Set klant = .Range("A:P").Find(what:=(Range("D6"), after:=Sheets("tarieflijst").Range("A2"), SearchOrder:=xlByRows, searchdirection:=xlNext)


  • Thanks for the answers. I have here the sheet. It goes on the sheet Klant toevoegen. Behind this one is the code. I have rewrite the code and it search on the two cells D6 and D8. But the code after that isn't working anymore and I don't know how to do it correct.

    The code now, the red one isn't working anymore. The rest works at the moment.

    Private Sub CommandButton1_Click()
    Dim i As Long

    With Sheets("Tarieflijst")

    If WorksheetFunction.CountIfs(.Columns(1), Sheets("Klant toevoegen").Range("D6"), _
    .Columns(14), Sheets("Klant toevoegen").Range("D8")) > 0 Then
    Dim rij As Long

    .Cells(rij, 16) = Range("D27").Value - 1
    .Cells(rij, 10) = .Cells(rij, 16).Value - .Cells(rij, 15).Value + 1
    .Cells(rij, 11) = (.Cells(rij, 5).Value + .Cells(rij, 7).Value + .Cells(rij, 9).Value) / 12 * .Cells(rij, 10).Value
    .Cells(rij, 12) = .Cells(rij, 11).Value * 0.21
    .Cells(rij, 13) = .Cells(rij, 11).Value + .Cells(rij, 12).Value

    For i = 2 To 1048576
    If .Cells(i, 2) = "" Then Exit For
    Next i
    BewerkKlant i
    MsgBox ("Klant toegevoegd en bijgewerkt")

    'Nieuwe klant toevoegen
    If MsgBox("Nieuwe klant toevoegen?", vbYesNo) = vbYes Then
    For i = 2 To 300
    If .Cells(i, 2) = "" Then Exit For
    Next i
    BewerkKlant i

    MsgBox ("Klant toegevoegd")
    End If

    End If

    End With

    End Sub

  • Can you try this on a copy of your original.

    I don't understand what needs to happen with this. Can you explain in detail, alhoewel "Nieuwe klant toevoegen" duidelijk is.
    If MsgBox("Nieuwe klant toevoegen?", vbYesNo) = vbYes Then

    For i = 2 To 300
    If .Cells(i, 2) = "" Then Exit For
    Next i
    BewerkKlant i
    MsgBox ("Klant toegevoegd")
    End If
  • Or should it be like this?
    If it is close, make sure to check the results as I guessed at quite a few lines.
    Again, test it on a copy of your original. There is no "Undo" for code results.

  • I don't know what is going wrong. I have try some options, if the client is new, it is no problem. If it is in the second row, it is also no problem. But it is the third one then it says add client Yes or no. But the client is already there and the year also. In other words: the first client (which number doesn't matter) is no problem and the second also. But if I changed for the third time with the same client and year it doesn't works. It has to changed the last row from under that has the two values.

  • In your first Post you say:
    Quote: "The mean of this code is that it looks for 2 different values in cell D6 and D8, if both cells found in one row in another sheet then it have to change some cells. If both cells not found in a row, then it have to do someting else."
    This is what the code supplied does.
    In your attached file you have several duplicates where both cells that you originally mentioned to look for are the same. That was never the intend of the code.
    If you now want something different, explain in detail with all possible scenarios what you want.

    It also looks like some data could be the same for several rows. How do you want to distinguish between these rows if you want to change data in that row?

  • In Sheet "Tarieflijst" you could have, by the looks of your attachment, multiple rows with the same data.
    This version will let you select which Row to change if that is what is required.
    If you would like to stick with the other version, we need to have cells that are guaranteed to be different for comparison. [ATTACH]n1207247[/ATTACH]

  • The code does what it has to do. The problem is that every time that the client comes in the list, it is called a sort of contract. So it can happens that this contract a many times changed in a year. So there is at one time a list with a lot of clients. In the code that you have it only changed one time. If I added for the third time a "contract" than it isn't changed the second row. It changed like nothing, only added the row. The code in the startpost is good, the only problem with that code was that it wasn't looking at two variable. But the code does exactly what I want. Only not with the year. That was the only problem.

    I hope I'm clear now, it sometimes also difficult for me to explain it in English. In the meantime I also try to change the code till working.

  • Thank you for the help.

    Wat er moet gebeuren is inderdaad dat of het een of het ander moet gebeuren. En dus inderdaad 2 opties.
    optie 1 is:
    Als het klantnummer en het jaar er niet in voorkomen moet de klant worden toegevoegd, als alleen het klantnummer voorkomt met een ander jaar. Moet deze ook gewoon nieuw toegevoegd worden.

    optie 2 is:
    Als het klantnummer en het jaar erin voorkomen, moet deze de laatste regel (die onderaan dus in de rij voorkomt) aanpassen die aan deze voorwaarde voldoet. EN het moet deze ook toevoegen alsof het een nieuwe klant is.
    Eigenlijk moet je het zien als een soort contract met een klant die door het jaar heen wijzigt. Met behulp van iedere regel, wordt uiteindelijk een factuur of creditnota naar de klant toegevoegd worden.
    Het kan dus zo zijn dat er op een bepaald moment 5x dezelfde klant met hetzelfde jaar voorkomt in de lijst. Alleen de laatst toegevoegde regel moet gewijzigd worden en niet meerdere.

    Op die manier krijg uiteindelijk met de maand begin en maand eind van dezelfde klant de aansluiting.
    Dus klantnummer 52 is klant geworden in januari. Dus van januari tot december loopt het contract. Vervolgens wordt het contract bijvoorbeeld vanaf maart gewijzigd en er moet bijvoorbeeld vanaf dan meer betaald worden.
    Dan moet het het oude contract wijzigen van januari tot en met februari en een nieuwe toevoegen die loopt vanaf maart tot en met december. En dit kan dus in een willekeurige maand weer gewijzigd worden.

    De code die je had gemaakt, doet precies eigenlijk wat het moet doen. Alleen als er een derde "contract" van dezelfde klant wordt toegevoegd, veranderd de code niet meer de laatst toegevoegde contract en ziet de code het als een nieuwe. En dat is eigenlijk niet de bedoeling.
    De code in de openingspost doet het eigenlijk precies wat het moet doen, alleen deze zoekt niet met het jaar erbij.

    Ik hoop dat het nu duidelijk is geworden :)

  • Thank you for the answer, when I run the code. It says that the variable is not defined. That is with this part of the code by the word "clientrow"

    [VBA].Offset(, 9).Value = sh2.Cells(clientrow, 16).Value - sh2.Cells(clientrow, 15).Value + 1[/VBA]

    I have try to add this to the code: dim clientrow as long
    and I have try to put i on this place. When I do the last one, then changes everything that is equal to the clientnumber and the year.

  • Re: Thanks you again for helping, but it changing everything with the same clientnumber and year. It has only changed the last one
    I understood that only the last, of possibly several, entry with the same client number and the same year needed to be changed.
    Do you want to change all where the client number and the year are the same?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!