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

wtom0412

Board Regular
Joined
Jan 3, 2015
Messages
168
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
 

hnsd24_CN

Board Regular
Joined
Oct 13, 2020
Messages
78
Office Version
  1. 2016
Platform
  1. Windows
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:

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

wtom0412

Board Regular
Joined
Jan 3, 2015
Messages
168
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
 

hnsd24_CN

Board Regular
Joined
Oct 13, 2020
Messages
78
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,187
Messages
5,546,457
Members
410,741
Latest member
Count25
Top