Please help me break down the code one line at a time.

[SebastianHuang]

I must thank this person whoever that wrote the code for me. I have forgotten his name. It is someone from this forum. But can someone else help explain to the codes one line at a time. It basically extracts columns of information according to the names in a another spreadsheet lined up in in a column and puts the information into another spreadsheet. I would really appreciate it if you could explain to me line by line. Thank you for your time and effort. There's nothing i can do in return but thank you for your effort. Thank you again.

Dim sh1 As Worksheet, sh2 As Worksheet, f As Range, i As Long

Set sh1 = Sheets("Sheet1")

Set sh2 = Sheets("Sheet2")

For i = 2 To sh2.Range("A" & Rows.Count).End(xlUp).Row

Set f = sh1.Rows(1).Find(sh2.Cells(i, "A"), , xlValues, xlWhole)

If Not f Is Nothing Then sh1.Columns(f.Column).Copy Sheets("Sheet3").Cells(1, i - 1)

Next

End Sub

 

[gallen]

'create variables for use in the sub
Dim sh1 As Worksheet, sh2 As Worksheet, f As Range, i As Long

    'Variable 'sh1' is to denote worksheet named "Sheet1"
    Set sh1 = Sheets("Sheet1")
    'Variable 'sh2' is to denote worksheet named "Sheet2"
    Set sh2 = Sheets("Sheet2")
   
    'Loop through every value in Column 'A' of sh2 (sheet2) starting at row 2
    For i = 2 To sh2.Range("A" & Rows.Count).End(xlUp).Row
        'if the value in column A of sh2 is found in the first row of sh1 then set the 'f' variable to that cell
        Set f = sh1.Rows(1).Find(sh2.Cells(i, "A"), , xlValues, xlWhole)
        'if 'f' is set, copy it and paste it to sheet 3. If 'f' isn't set then it nothing was found
        If Not f Is Nothing Then sh1.Columns(f.Column).Copy Sheets("Sheet3").Cells(1, i - 1)
    Next

 

[SebastianHuang]

Sorry to bother you again. I know this is a loop, but it is beyond my knowledge.
"For i = 2 To sh2.Range("A" & Rows.Count).End(xlUp).Row"
-sh2.Range("A" & Rows.Count), Does "A" mean column and Rows.count counts the number of rows in that column? What about End(xlUp).Row. What does that do? I am new to this.

Thanks. I may have to ask you the other lines as well.

 

[gallen]

In English, that line says, "go to the very last row of column A on sheet 2 and then move up until you get to a cell that has a value." It's a standard way of ensuring you know the very last used row in the column

 

[SebastianHuang]

In English, that line says, "go to the very last row of column A on sheet 2 and then move up until you get to a cell that has a value." It's a standard way of ensuring you know the very last used row in the column

Ok. So part loops through the whole column and skip the last used row. I think that is what's going on.

Let me go to the next line.
Set f = sh1.Rows(1).Find(sh2.Cells(i, "A"), , xlValues, xlWhole)

I see that f is set as range. What does that mean? according to the above code, F seems to be a variable that finds what's in sheet 1 in sheet 2. Then there's two commas after that. What does xLvalues, xlWhole) mean? Thanks for answering. I know it is a lot of questions.

 

[gallen]

I'll try to keep it simple..

'f' is declared as a range object.

The range object has a 'Find' method that has some required arguments and many optional ones. The 'Find' method returns a range object.

Below shows all the arguments. Note that any argument in square brackets is OPTIONAL and can be left blank. So the 'What' argument is the only mandatory argument

Therefore the code below skips some optional arguments by leaving them blank. The commas separate each argument.

In reality this

Set f = sh1.Rows(1).Find(sh2.Cells(i, "A"), , xlValues, xlWhole)

Can also be written as this which is much easier to read:

Set f = sh1.Rows(1).Find(what:=sh2.Cells(i, "A"), LookIn:=xlValues, LookAt:=xlWhole)

And in English that line says " Search the first row of sh1 and try to find the value currently held Cell Ai of sh2. If the value is found set 'f' to be the found cell."

Remember that a range object can be 1 or more cells.