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

SebastianHuang

New Member
Joined
Dec 5, 2019
Messages
28
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Jun 27, 2011
Messages
1,931
VBA Code:
'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

New Member
Joined
Dec 5, 2019
Messages
28
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Jun 27, 2011
Messages
1,931
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

New Member
Joined
Dec 5, 2019
Messages
28
Office Version
2013
Platform
Windows
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.
 

Forum statistics

Threads
1,081,566
Messages
5,359,635
Members
400,542
Latest member
Fahkeet

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top