Previous Topic Next Topic

Trying to see if I can even create a Macro! Please help!

Closed
almacgtz
Posts
2
Registration date
Friday January 16, 2015
Status
Member
Last seen
January 21, 2015
 - Jan 16, 2015 at 03:11 PM
 RayH - Jan 21, 2015 at 03:57 PM


I want to know if its even possible to create a Macro whenever I copy/paste information that is provided to me weekly, which in this case is in the right, for the Macro to see through the information in the left and copy and paste it in the correct row.

For example, the student #1 on the right is already on the information of the left or for it to do something indicating that the row on the left has something already. Then Copy/paste the information of the students 5,6,7, which are not in the system (name/age/height/grade) to the correct row on the right.

If somebody could help me with this I would really appreciate it.

Thank you!

1 reply

Answer 1 / 1
RayH
Jan 16, 2015 at 07:57 PM
Give this a go. 


Sub copystudents()

Let LastRowH = Range("H" & Rows.Count).End(xlUp).Row
Let LastRowA = Range("A" & Rows.Count).End(xlUp).Row

For Each c In Range("H3:H" & LastRowH)

On Error GoTo notfound
    x = Rows(Application.Match(c.Value, Range("A1:A" & LastRowA), 0)).Row
    Range(c.Address & ":L" & c.Row).Copy Destination:=Range("A" & x & ":E" & x)
notfound:
Next
End Sub
0
almacgtz
Posts
2
Registration date
Friday January 16, 2015
Status
Member
Last seen
January 21, 2015
Jan 21, 2015 at 11:55 AM
Thank you very much Ray this is exactly what I was looking for!!

The code worked perfectly fine; however, if a student number is already on the left side is there any way to highlight the information on the right side without replacing the information on the left side.

Best regards,

Alma Gtz
0
RayH > almacgtz
Posts
2
Registration date
Friday January 16, 2015
Status
Member
Last seen
January 21, 2015
Jan 21, 2015 at 03:57 PM
This modification to the code highlights the row on the right in blue if it exists on the left. I'm using the name for comparison.
I am highlighting in red where the value on the right is not found.
For any others the colors stay as they are. 


Sub copystudents()

Let LastRowH = Range("H" & Rows.Count).End(xlUp).Row
Let LastRowA = Range("A" & Rows.Count).End(xlUp).Row

For Each c In Range("H3:H" & LastRowH)

On Error GoTo notfound
    x = Rows(Application.Match(c.Value, Range("A1:A" & LastRowA), 0)).Row
    If Range(c.Address).Offset(0, 1) = Range("B" & x) Then
        Range(c.Address & ":L" & c.Row).Interior.ColorIndex = 8
    Else
        Range(c.Address & ":L" & c.Row).Copy Destination:=Range("A" & x & ":E" & x)
    End If
nextone:
Next

Exit Sub

notfound:
Range(c.Address & ":L" & c.Row).Interior.ColorIndex = 3
GoTo nextone
End Sub

0