Скачать или смотреть VBA Do Loop Formula Advanced Lesson

'COPY CODE FROM VIDEO BELOW OR ON http://vbatutorialcode.com/do-loop-fo...
'1. How to do a Do Until Loop in VBA.
'2. How to do a For Next Loop in VBA.
'3. How to work with Filters, Visible Cells, Formulas, Sheets, Modules, Data Types, and Message Boxes in VBA.

'Check out the article on my website https://VBATutorialCode.com if you want to download this workbook. I also have all the source code pasted 'there.

'How to Enable Developer tab in Excel 2007, 2010, and 2013 (This step is necessary to access Project Explorer
'mentioned 3 lines below :)
'Excel 2007/2010 - File - Options - Developer Enable Developer Tab on the Ribbon
'Excel 2013 - File - Options - Customize Ribbon - Check the Developer on the right-hand side

'How to Access Project Explorer to paste this code in Excel 2007, 2010, and 2013.
'Press Alt + F11 on keyboard to access Project Explorer or another way to access Project Explorer - under Developer tab in Ribbon press 'view code



'paste from here to bottom in Module1
'It names the sheet whatever. Run it multiple times. It puts the value that is NOT in both column A and N on a new sheet that you can name.
'parts of the code are not visible in youtube video at 20:10, 32:20, 23:11.
'Loops, Filter, Visible, NA Excel Sheet Source Code:
Sub module()

Dim endrow As Integer
endrow = Range("N" & Rows.Count).End(xlUp).Row


Range("H2").Select
Range("H2").Formula = "=VLOOKUP(RC[6],C1,1,FALSE)"
Range("H2").Select
Range("H2").AutoFill Destination:=Range("H2:H" & endrow)

Columns("H").Select
Selection.Copy
Columns("H").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Columns("H:H").Select
Selection.Replace What:="#N/A", Replacement:="New Name", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Dim counter As Integer
endrow = Range("H" & Rows.Count).End(xlUp).Row
endrow = endrow + 1
Dim nameholder As String
counter = 2

Do Until counter = endrow
nameholder = Range("N" & counter).Value
If Range("H" & counter).Value = "New Name" Then
MsgBox (nameholder & " is a new name.")
End If
counter = counter + 1
Loop

ActiveSheet.Range("$H$1:$H$2000").AutoFilter Field:=1, Criteria1:="New Name"

Range("N2:N2000").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

Sheets.Add After:=ActiveSheet
Sheets(2).Select
Sheets(2).Name = InputBox("Enter sheet name")
Range("A2").Select
ActiveSheet.Paste
Range("A1").Value = "New Names Column"

Columns("A").Select
Columns("A").EntireColumn.AutoFit

Sheets(1).Select
ActiveSheet.ShowAllData

Sheets(2).Select

End Sub