Code is giving an debug error while initiation

Shazir

Banned - Rules violations
Joined
Jul 28, 2020
Messages
94
Office Version
  1. 365
Platform
  1. Windows
Code gives debug error now, it was working well before i do not know why. Can someone please solve the issue. All the folders and sheets are with same name as it was before but the error is that files or location has been removed or renamed. Looking forward to your response.

VBA Code:
Public fName As String, _
fd As FileDialog, _
wbOne As Workbook, wbTwo As Workbook, tw As Workbook, NewBook1 As Workbook, NewBook2 As Workbook, _
shOne As Worksheet, shTwo As Worksheet, sh As Worksheet, actOne As Worksheet, actTwo As Worksheet, _
shResult As Worksheet, actResult_1 As Worksheet, actResult_2 As Worksheet, _
fChosen As Integer
Sub ExecuteBtn()
Dim linkOne As String
Dim linkTwo As String
Dim nrRowsOne As Long
Dim nrRowsTwo As Long
Dim nrRowsStart As Long
Dim nrRowsResult1 As Long
Dim nrRowsResult2 As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set tw = ThisWorkbook
Set sh = tw.Worksheets("Start")
nrRowsStart = NrRows(sh, 1)

For j = 2 To nrRowsStart
    fName = sh.Cells(j, 1).Value
    
    linkOne = ThisWorkbook.Path & "\cell are codes\" & fName & ".csv"
    linkTwo = ThisWorkbook.Path & "\Landline Area codes\Landline Area codes Prefixes-" & fName & ".csv"
    
    Set wbOne = Workbooks.Open(linkOne)
    Set actOne = wbOne.Worksheets(1)
    nrRowsOne = NrRows(actOne, 1)
    
    Set wbTwo = Workbooks.Open(linkTwo)
    Set actTwo = wbTwo.Worksheets(1)
    nrRowsTwo = NrRows(actTwo, 1)

    nrRowsResult1 = 2
    nrRowsResult2 = 2
    If nrRowsOne <= 1048576 Then
        If nrRowsOne = nrRowsTwo Then
        If nrRowsOne <= 524288 Then AddNew1 (tw.Path & "\Result\Result" & fName & "_1" & ".xlsx")
        If nrRowsOne > 524288 Then AddNew1 (tw.Path & "\Result\Result" & fName & "_1" & ".xlsx"): AddNew2 (tw.Path & "\Result\Result" & fName & "_2" & ".xlsx")

            For i = 2 To nrRowsOne
                If i <= 524288 Then
                    actResult_1.Range("A1:G1").Value = actOne.Range("A1:G1").Value
                    actResult_1.Range("A" & nrRowsResult1 & ":G" & nrRowsResult1).Value = actOne.Range("A" & i & ":G" & i).Value
                    actResult_1.Range("A" & nrRowsResult1 + 1 & ":G" & nrRowsResult1 + 1).Value = actTwo.Range("A" & i & ":G" & i).Value
                    nrRowsResult1 = nrRowsResult1 + 2
                    
                Else
                    actResult_2.Range("A1:G1").Value = actOne.Range("A1:G1").Value
                    actResult_2.Range("A" & nrRowsResult2 & ":G" & nrRowsResult2).Value = actOne.Range("A" & i & ":G" & i).Value
                    actResult_2.Range("A" & nrRowsResult2 + 1 & ":G" & nrRowsResult2 + 1).Value = actTwo.Range("A" & i & ":G" & i).Value
                    nrRowsResult2 = nrRowsResult2 + 2
                End If
            Next i
        Else
            MsgBox "The files have different number of rows !", vbCritical
        End If
    Else
        MsgBox "Excel cannot handle more than 1,048,576 rows !", vbCritical
    End If
    If nrRowsOne <= 524288 Then
        NewBook1.SaveAs Filename:=tw.Path & "\Result\Result" & fName & "_1" & ".csv", FileFormat:=xlCSV, CreateBackup:=False
        NewBook1.Close SaveChanges:=False
        Kill (tw.Path & "\Result\Result" & fName & "_1" & ".xlsx")
    End If
    If nrRowsOne > 524288 Then
        NewBook1.SaveAs Filename:=tw.Path & "\Result\Result" & fName & "_1" & ".csv", FileFormat:=xlCSV, CreateBackup:=False
        NewBook1.Close SaveChanges:=False
        Kill (tw.Path & "\Result\Result" & fName & "_1" & ".xlsx")
        
        NewBook2.SaveAs Filename:=tw.Path & "\Result\Result" & fName & "_2" & ".csv", FileFormat:=xlCSV, CreateBackup:=False
        NewBook2.Close SaveChanges:=False
        Kill (tw.Path & "\Result\Result" & fName & "_2" & ".xlsx")
    End If
    wbOne.Close SaveChanges:=False
    wbTwo.Close SaveChanges:=False
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
Next j
MsgBox "Done !", vbInformation
End Sub
Sub AddNew1(locationPath As String)
Set NewBook1 = Workbooks.Add
    With NewBook1
         .SaveAs Filename:=locationPath
        Set actResult_1 = .Worksheets(1)
    End With
End Sub
Sub AddNew2(locationPath As String)
Set NewBook2 = Workbooks.Add
    With NewBook2
        .SaveAs Filename:=locationPath
        Set actResult_2 = .Worksheets(1)
    End With
End Sub
Function NrRows(sh As Worksheet, ColNumber As Integer) As Long
    NrRows = sh.Cells(Rows.Count, ColNumber).End(xlUp).row
End Function
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Watch MrExcel Video

Forum statistics

Threads
1,122,658
Messages
5,597,403
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top