0

I am creating a userform to pull data from two workbooks on new sheets, vlookup for references on my initial data, and delete the repeats before I combine all sheets. Seemingly, the code completely skips from the point it should inject the vlookup and autofill.

Private Sub runform_Click()
Application.ScreenUpdating = False
Dim rowcount As Integer, dltcount As Integer
Dim trackwb As Workbook, uswb As Workbook, cawb As Workbook
Dim sht1 As Worksheet, sht2 As Worksheet, sht3 As Worksheet, sht4 As Worksheet   

Set trackwb = ActiveWorkbook
    trackwb.Sheets.Add.Name = "US_SQL"
    trackwb.Sheets.Add.Name = "CA_SQL"
Set sht1 = Worksheets("Tracking report")
Set sht2 = Worksheets("Completed")
Set sht3 = Worksheets("US_SQL")
Set sht4 = Worksheets("CA_SQL")

Set uswb = Workbooks.Open(Filename:=usfilebox)
    uswb.ActiveSheet.Cells.Copy
        sht3.Paste
        Application.CutCopyMode = False
    uswb.Close
Set cawb = Workbooks.Open(Filename:=cafilebox)
    cawb.ActiveSheet.Cells.Copy
        sht4.Paste
        Application.CutCopyMode = False
    cawb.Close
' The code seems to stop here and skip straight to the last 3 lines

rowcount = Application.CountA(sht3.Range("A:A"))
dltcount = Application.CountA(sht3.Range("A:A"))
rowx = 2

With sht3.Range("BT2")
    .FormulaR1C1 = "=IF(isna(Vlookup(RC[-70], contrng, 1, False)),""Keep"",""DELETE"")"
    .AutoFill Destination:=.Parent.Range("BT2:BT" & rowcount), Type:=xlFillDefault
        Do While rowx <= dltcount
            If Range("BT" & rowx).Value = "DELETE" Then
                Rows(rowx).EntireRow.Delete
            dltcount = dltcount - 1
            Else
                rowx = rowx + 1
            End If
        Loop
    Range("BT:BT").Delete
End With

'  code resumes
Unload Me
Application.ScreenUpdating = True
End Sub
Arktik
  • 17
  • 6
  • You really need to qualify those `Cells` and `Range` and `Rows` calls with the Workbook/Worksheet in question. Plus you should [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Jul 13 '22 at 20:40
  • `Cells(2, 72).Select` - this does not refer to `With sht3` at all, unless you put a `.` before `Cells`. – BigBen Jul 13 '22 at 20:42
  • I returned it to my original code before fiddling with it, without select. Take a gander now pls. @BigBen – Arktik Jul 13 '22 at 20:44
  • Still has some unqualified references, such as `Range("BT" & rowx)` and `Rows(rowx)` and `Range("BT:BT")`. – BigBen Jul 13 '22 at 20:45
  • Added dim rowx as integer and added sht3. to qualify, she's running now. Thank you @BigBen – Arktik Jul 13 '22 at 20:52
  • Change that to `Dim rowx As Long`, btw, see [this](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long) why. – BigBen Jul 13 '22 at 20:52

0 Answers0