CODEWITHSUNDEEP
vbaruntime-error
Trefl
Trefl

Reputation: 27

Visual Basic Macro error

I have a problem with my macro do Microsoft Excel. I want to make macro that will check if in one row there is word that its the same word that user inputed in cell, and than it have to check the whole row to compare other informations. In ex. User inputs Adam Smith 12.05.2016 and macro has to find name Adam in name column and than check if this Adam has surname Smith and date 12.05.2016. If yes print whole row. I have created the whole macro but I got error1004 Application-defined or object-defined error.

    Sub Test()
 Dim r As Integer
 Dim c1 As Integer
 Dim c2 As Integer
 Dim i As Integer
 r = 0
 c1 = 0
 c2 = 0
 i = 0
 ' Iterates through name column
 Do Until Cells(r, c1).Value = ""
    ' If program finds the name in name row equal to name in (6, G) returns true
        If Cells(r, c1).Value = Cells(6, G).Value Then
            ' Iterates through surname row
            Do Until Cells(r, c2).Value = ""
                ' If surname is the same and date is on the right of the surname Return tur
                If Cells(r, c2).Value = Cells(6, H).Value And Cells(r, c2 + 1).Value = Cells(6, i) Then
                    Do Until i <= 0
                    ' Output whole data from the row
                    Cells(10, H + i).Value = Cells(r, c1 + i).Value
                    i = i + 1
                    Loop
                End If
            c2 = c2 + 1
            Loop
        End If
r = r + 1
Loop
End Sub

Upvotes: 0

Views: 48

Answers (1)

Nathan_Sav
Nathan_Sav

Reputation: 8531

You can do this with an array formula, a normal formula, but works on many cells, so you need to CTRL SHIFT ENTER, and it will be this

=MAX(IF(CONCATENATE($I$1:$I$3," ",$J$1:$J$3)=L1,ROW($I$1:$I$3)))

My first name was in I, last name in J and the data entry in L1, this gives the row number of the occurrence, then you can use INDEX to get the values, or OFFSET

https://support.office.com/en-gb/article/INDEX-function-a5dcf0dd-996d-40a4-a822-b56b061328bd

Upvotes: 1

Related Questions