0

On the if statement, on the i=2 iteration this code if giving me

Runtime Error 9

I am running this from another workbook. WBK is not open yet.

For reference the sheet has 300,000 rows, so I declared 'i' as a Long instead on Int.
I am trying to see if each value in my table's column can be found in WBK Sheet 1 Column A, and if so to change the value to "Sale Complete".

For i = 2 To CLng(Rows.Count):
    If Cells(i, 3) = Application.VLookup(Cells(i, 3).Value2, Workbooks("WBK").Worksheets("Sheet1").Columns(1), 1, False) Then
        Cells(i, 3).Value = "Sale Complete"
    End If
Next
Community
  • 1
  • 1
VBoi
  • 1
  • 1
    That means you have no workbook named "WBK" open, or that said workbook has no worksheet named "Sheet1". – BigBen Jul 29 '21 at 19:32
  • 2
    Side notes: `CLng` is unnecessary, and `Rows.Count` is *every single row* on the ActiveSheet. Perhaps [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) first. Also, better to use `Not IsError` on the result of `Application.Vlookup`. Your `If` statement as-is is prone to blow up. `If Not IsError(Application.Vlookup(....)) Then`. – BigBen Jul 29 '21 at 19:36
  • 1
    Probably you mean `WBK.Worksheets("Sheet1")`, I'm guessing `WBK` is a variable. – BigBen Jul 29 '21 at 19:55
  • 1
    As far as I understand you are using VLOOKUP to check whether a certain value is in a list of 300,000 rows. I would suggest you to use MATCH that may perform faster with such large amount of data. Your first part of IF statement could be like this: `if IsNumeric(Application.Match(Cells(i, 3).Value, Workbooks("WBK").Worksheets("Sheet1").Columns(1), 0)) Then`. Please consider the other indications of @BigBen about workbook and worksheet references. – Michele Jul 29 '21 at 23:13
  • Are you getting the error on first iteration (ie. i=2)? Are you running this inside the WBK workbook or from another workbook that expects WBK to be open? – dbmitch Jul 30 '21 at 01:47
  • @dbmitch Yes, I'm getting the error on the i=2 iteration and I am running this from another workbook. WBK is not open yet..should it be? – VBoi Jul 30 '21 at 02:47
  • Yes it should be open, otherwise as mentioned in the first comment you'll get this error. – BigBen Jul 30 '21 at 04:01

0 Answers0