Hey all,
I'm new to vba and I've successfully done a few macros to simplify some processes at work. However currently I'm struggling to create one for this scenario as it gives a "Block If without End If" error;
The aim of this code is to open a file which will sort the colomn of names into "lastname, firstname" and then compare that to the source document. If the names match, 9 cells will be copied then pasted into a targeted area of the source document. It will then repeat till the end of the target document. Any missing names will be not inputted.
I'm not sure what I did wrong, or what else I need to add. Could I get some guidance?
I'm new to vba and I've successfully done a few macros to simplify some processes at work. However currently I'm struggling to create one for this scenario as it gives a "Block If without End If" error;
VBA Code:
Sub SAFESections()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename(Title:="Browse for your FIle & Import Range", FileFilter:="Excel Files (*.xls*), *xls*")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
Range("EU2").Select
ActiveCell.FormulaR1C1 = _
"=CONCAT(RIGHT(RC[-146],LEN(RC[-146])-FIND("" "",RC[-146])),"", "",LEFT(RC[-146],FIND("" "",RC[-146])-1))"
Range("EU2").Select
Selection.AutoFill Destination:=Range("EU2:EU1244")
Range("EU2:EU1244").Select
OpenbookLastRow = OpenBook.Sheets(1).Range("EU2" & Rows.Count).End(xlUp).Row
MasterBookLastRow = ThisWorkbook.Worksheets("NSW Data").Range("C4" & Rows.Count).End(xlUp).Row
For r = 2 To OpenbookLastRow
For m = 4 To MasterBookLastRow
If OpenBook.Sheets(1).Range("EU2" & r).value = ThisWorkbook.Worksheets("NSW Data").Range("C4" & r).value Then
OpenBook.Sheets(1).Range("AQ", "BC", "BN", "BX", "CJ", "CT", "DI", "DT", "EJ").Copy
ThisWorkbook.Worksheets("NSW Data").Activate
lastRowRpt = ThisWorkbook.Worksheets("NSW Data").Range("O4" & Rows.Count).End(xlUp).Row
ThisWorkbook.Worksheets("NSW Data").Range("O4" & lastRowRpt + 1).Select
ActiveSheet.Paste
Else
End If
Next m
Next r
End SubThe aim of this code is to open a file which will sort the colomn of names into "lastname, firstname" and then compare that to the source document. If the names match, 9 cells will be copied then pasted into a targeted area of the source document. It will then repeat till the end of the target document. Any missing names will be not inputted.
I'm not sure what I did wrong, or what else I need to add. Could I get some guidance?
