Loop through an array on one sheet and loop through another sheet and paste

wtom0412

Board Regular
Joined
Jan 3, 2015
Messages
180
Office Version
  1. 365
Platform
  1. Windows
Hello All,

I have been trying for hours to find a solution to my problem.

Earlier, Puertorekinsam helped me a lot, but his solution threw up errors in my sheet that he couldn't replicate (my Worksheet is very complicated so I'm sure it was stuff that I didn't tell him). So I have created a dumbed down version of my Worksheet in an attempt to make this easier to explain.

I have two worksheets: Change and RawData (I have added formulas that I am using into square brackets).

RawData contains all of the data in a long list for 200 people (about 1000+ rows of data). Each person can have up to 30 scores against their name - some have as few as one score, others can have the full 30, and others anything in between.

I use Column B and C in both sheets to provide me with a Unique Helper Cell top assist with VlookUp.

A​
B​
C​
D​
NameLine numberUnique Helper Cellscore
Fred11Fred [=B2&A2]100
Fred22Fred101
Fred33Fred102
Fred44Fred103
Fred55Fred104
Fred66Fred105
Mary11Mary106
Mary22Mary107
Mary33Mary108
Mary44Mary109
Mary55Mary110
Mary66Mary111
Mary77Mary112
Mary88Mary113
Mary99Mary114
Andrew11Andrew115
Andrew22Andrew116
Andrew33Andrew117
Julie11Julie118
Julie22Julie119
Julie33Julie120
Julie44Julie121
Julie55Julie122


Change uses a VlookUp to get the score from RawData Column D and uses a dropdown (name - cell E1) as the reference. I am sure there are much easier ways to pull in the data into this sheet, but it works!!

A​
B​
C​
D​
E​
Name​
Fred​
Change ValueLine Number (Hidden)Unique Helper Cell (Hidden)Score
20011Fred [=B4&$E$1]100 [=IFERROR(VLOOKUP(C4,Score!$C:$D,2,0),"")]
20122Fred [=B5&$E$1]101 [=IFERROR(VLOOKUP(C5,Score!$C:$D,2,0),"")]
20233Fred [=B6&$E$1]102 [=IFERROR(VLOOKUP(C6,Score!$C:$D,2,0),"")]
20344Fred [=B7&$E$1]103 [=IFERROR(VLOOKUP(C7,Score!$C:$D,2,0),"")]
20455Fred [=B8&$E$1]104 [=IFERROR(VLOOKUP(C8,Score!$C:$D,2,0),"")]
20565Fred [=B9&$E$1]105 [=IFERROR(VLOOKUP(C9,Score!$C:$D,2,0),"")]
77Fred [=B10&$E$1]"" [=IFERROR(VLOOKUP(C10,Score!$C:$D,2,0),"")]
88Fred
99Fred
1010Fred
1111Fred
1212Fred
1313Fred
1414Fred
1515Fred
1616Fred
1717Fred
1818Fred
1919Fred
2020Fred
2121Fred
2222Fred
2323Fred
2424Fred
2525Fred
2626Fred
2727Fred
2828Fred
2929Fred
3030Fred

Using Fred as the reference, the Vlookup brings in six scores, so what I would like to do is enter new values in to A4:A9 of this sheet, press a button, and paste those values from Change A4:A9 into in RawData D2:D7 (using the Unique Helper Cell in Column C on both sheets).

Similarly, if I choose Andrew, only three scores get pulled in so I change the values in A4:A6, press the button and paste the values in RawData D17:D19.

I hope all of this makes sense!!? I have found some code on the net, and Puertorekinsam added a lot to it, but it relates to my original worksheet, and as previously mentioned it throws error messages, so I'm not sure there is much point pasting it here? Happy to amend it to relate to this sheet and post it if that would be useful?

I would really appreciate any assistance you can give.

Cheers, Toby
 
I made some adjustments to make the code simpler. But when there is too much data, it will reduce the efficiency of operation. Of course, your data is not much, so it has little impact.
I don't quite understand“it pastes the data one cell down on the Change TAB in Column D for the first name only”,especial"the first name",do you mean the first score or only one score on the Change TAB in Column D.So I don't know what went wrong
VBA Code:
Private Sub CommandButton1_Click()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim i As Integer
    Dim n As Integer
    Dim s As Integer
    Dim r As Integer
    Dim t As Integer
    Set ws1 = Worksheets("Score")
    Set ws2 = Worksheets("Change")
    r = ws2.Range("A65536").End(xlUp).Row
    s = ws1.Range("C65536").End(xlUp).Row
    If r < 4 Then
        MsgBox "No score was changed"
        exit sub
    Else
        t = 0
        For i = 4 To r
            If ws2.Range("A" & i) <> "" Then
               n = 3
               Do Until n = s + 1
                  If ws2.Range("C" & i) = ws1.Range("C" & n) Then
                     ws1.Range("D" & n) = ws2.Range("A" & i)
                     t = t + 1
                     Exit Do
                  End If
                  n = n + 1
               Loop
            End If
        Next
        MsgBox t & " scores was changed"
    End If
End Sub
 
Last edited:
Upvote 0

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hi, did you get the sheet I emailed?

If you run the code with Fred as the selected name, you will see that the value in D4 doesn't change. It works perfectly for the rest of the names, but not for the first name in the drop down at E1?

Cheers. Toby
 
Upvote 0
Hi, did you get the sheet I emailed?

If you run the code with Fred as the selected name, you will see that the value in D4 doesn't change. It works perfectly for the rest of the names, but not for the first name in the drop down at E1?

Cheers. Toby
I think I know what's going on, you should check the name "Fred", it may contain invisible characters, such as the space "Fred ", this is very important
When you select Fred, enter: =len(E1) in any cell, check the length of the string, I think the result maybe >4.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top