Previous Topic Next Topic

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

Closed
almacgtz Posts 2 Registration date Friday 16 January 2015 Status Member Last seen 21 January 2015 - 16 Jan 2015 à 15:11
 RayH - 21 Jan 2015 à 15:57


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 response

Réponse 1 / 1
RayH
16 Jan 2015 à 19:57
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
almacgtz Posts 2 Registration date Friday 16 January 2015 Status Member Last seen 21 January 2015
21 Jan 2015 à 11:55
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
RayH > almacgtz Posts 2 Registration date Friday 16 January 2015 Status Member Last seen 21 January 2015
21 Jan 2015 à 15:57
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

Similar discussions

Hi Guys how to create an account for facebook with my company em
Ramu -
Ahmadzai -

2 responses
If FB account is disabled, can I create a new one?
tuckerdognc -
hamsheena -

7 responses
Why can't I bridge connect?
ryno -
Qiqi -

15 responses
Do users get notified when I add them to a group?
maicart -
dwebb -

2 responses
How to create a .bin file?
lwm1017 -
dwebb -

3 responses