Link to home
Create AccountLog in
Avatar of route217
route217Flag for United Kingdom of Great Britain and Northern Ireland

asked on

edit vba macro code

Hi Experts (sending message via iphone) - so cannot edit the data in colm A worksheet "res"

In the attached file - in worksheet "res" the id cannot be repeat you can only have one occurence of the employee id in column A

However, in worksheet "proj" you can have mulitple occurence of the employee id in column A.

I need to edit the current macro so that it looks up the id in worksheet"res" and find the task assoicated with that id in column D worksheet "proj" and past the asnwer in column B worksheet "res". if the employee is exist more then once in worksheet "proj" then also paste the second or third result into col B worksheet "res" against that id.

So based on the attached file.

Worksheet"res"
col A       col b
123         Clean, Task Stock
333         wash up
etc....


Sample.xls
Avatar of Ken Butters
Ken Butters
Flag of United States of America image

Looks to me like the button "Generate", which executes Sub CommandButton1_Click() routine  in your attached spreadsheet already does this.
route217,

In res!B1 put:

=IF(ISERROR(VLOOKUP(A2,proj!$A$2:$D$7,4,0)),"",VLOOKUP(A2,proj!$A$2:$D$7,4,0))

and copy down to the end of the data.

Patrick
Avatar of route217

ASKER

Hi patrick

Not sure that is going to work, because There are multiple occurrence of I'd 123 in worksheet "proj" for one occurrence of I'd 122 in worksheet "res"
Hi buttersk

In worksheet "res" there cannot be multiple I'd in this worksheet.. So I'd 123 can only appear once error in worksheet. But the I'd123 can only have multiple entrants in worksheet "proj"

Hi buttersk

In worksheet "res" there cannot be multiple I'd in this worksheet.. So I'd 123 can only appear once error in worksheet. But the I'd123 can only have multiple entrants in worksheet "proj"

route217,

The code below is in the attached file.

Patrick

Private Sub CommandButton1_Click()
Dim rng1 As Range
Dim rng2 As Range
Dim celle1 As Range
Dim celle2 As Range
Dim str1 As String

With Sheets("res")
    Set rng1 = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
    rng1.Offset(0, 1).ClearContents
End With
With Sheets("proj")
    Set rng2 = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each celle1 In rng1
    For Each celle2 In rng2
        If celle1 = celle2 Then
            str1 = str1 & celle2.Offset(0, 3) & ", "
        End If
    Next celle2
    If Len(str1) <> 0 Then
        celle1.Offset(0, 1) = Left(str1, Len(str1) - 2)
        str1 = ""
    End If
Next celle1

End Sub

Open in new window

Sample-1-01.xls
Hi Patrick

Getting a compile error " invalid next control variable reference"
ASKER CERTIFIED SOLUTION
Avatar of patrickab
patrickab
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Ok thanks Patrick

Just one quick question...if in worksheet I want to change rng1.offset(0, 1).clearcontents to col m then this would be rng1.offset(0, 12).clearcontents ????
>.if in worksheet I want to change rng1.offset(0, 1).clearcontents to col m then this would be rng1.offset(0, 12).clearcontents ????

Yes it would.
route217 - Thanks for the grade - Patrick