0

I am trying to take my current code, that uses a Find function to search for the value in cell "H4" (this value is in the worksheet where the code is) and find it in a second workbook searching from Columns A3-A100000 (Find functions start at the next row after the first, hence why in my code I have written A2:A100000 as my Find range). Once the value is found, I would like to transpose the resize values of the Find match in the second workbook (with an offset of 5 columns to start transposing values from column F ).

My code works but I am trying to further simplify my code and possibly speed it up by not declaring so many variables and eliminating stepping through so much code. I currently have:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet
  Dim Key As Range

  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual

  If Not Intersect(Target, Range("E4:E5")) Is Nothing Then

  Application.EnableEvents = False

  Set sh1 = ThisWorkbook.Worksheets("Operator")
  Set wb2 = Workbooks.Open(Filename:="\\schaeffler.com\stratford\DATA\NEA-FBA-P\Projects\SetupSheets\Databases\Database_IRR 200-2S.xlsm", Password:="123")
  Set sh2 = wb2.Sheets("Changes")
  Set Key = sh2.Range("A2:A100000").Find(sh1.Range("H4"), , xlValues, xlWhole)
  sh1.Unprotect "123"

  If Not Key Is Nothing Then

    sh1.Range("N31").Resize(85).Value = Application.Transpose(Key.Offset(, 5).Resize(, 85).Value)
  Else

    sh1.Range("N31").Resize(85).ClearContents

  End If

  sh1.Protect "123"
  wb2.Close False

  End If

  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True
  Application.ScreenUpdating = True

End Sub

I am trying to replace the above code with the following below, but it's producing the following error:

Run-time error '91': Object Variable or With block variable not set

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual

  If Not Intersect(Target, Range("E4:E5")) Is Nothing Then

  Application.EnableEvents = False
  Sheet1.Unprotect "123"
Sheet1.Range("N31").Resize(85).Value = Application.Transpose(Workbooks.Open(Filename:=" \Databases\Database_IRR 200-2S.xlsm", Password:="123").Worksheets("Changes").Range("A2:A100000").Find(sh1.Range("H4"), , xlValues, xlWhole).Offset(, 5).Resize(, 85).Value)
  Sheet1.Protect "123"
  
  End If
  
  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True
  Application.ScreenUpdating = True

End Sub

I have also posted this on another forum to get their opinion as well: https://www.mrexcel.com/board/threads/using-find-and-resize-to-transpose-values-error.1140796/

Handreen
  • 77
  • 11
  • 2
    When you chain call like this, it's difficult so see where the error is. For example Workbooks.Open used to always return the last opened file, not the one given in parameter (not sure if this was corrected or not in the last versions). If the file is already opened, your function may fail. See https://stackoverflow.com/questions/52168437/ . – Vincent G Jul 22 '20 at 14:54
  • You also have a space before the first \ in the path. – Vincent G Jul 22 '20 at 14:56
  • 1
    Eliminating variables will not speed things up, since you're opening a file (which you do not close BTW): that file operation will by far outweigh anything you do to optimize your code, and as Vincent notes makes it impossible to debug. – Tim Williams Jul 22 '20 at 16:34
  • @VincentG I believe that in the last version this was updated as when I have the second workbook open, the code doesn't crash and still executes the desired output correctly. I will stick with my first code as you and Tim have made some good points. Also the "\" is just because I was taking out the beginning of the actual filepath I'm using for privacy of course. Are there any suggestions or improvements to the first code you could recommend? – Handreen Jul 22 '20 at 19:43
  • @TimWilliams is there any other alternative to calling cells from another workbook that is quicker than what I am currently doing? – Handreen Jul 22 '20 at 19:44
  • When doing this type of thing (and you're not just leaving the workbook open) it might be quicker to open the workbook only once per session, copy the data to a hidden sheet in the workbook (or read it into a Global array) and then use that copy to run your lookups. Depends on how static the lookup table is and how many lookups you expect to be doing. Or look at using the Excel SQL driver to query the file, instead of opening it for a `Find()` – Tim Williams Jul 22 '20 at 19:58
  • @TimWilliams The lookup table is not static and is going to be having rows added to it, but it won't happen at the same time as this code executes as the lookup table gets data added to it from this workbook as well via a different macro when a new entry is required. I tried reading it into a global array but I struggled figuring it out – Handreen Jul 22 '20 at 20:02
  • If you have 100k rows and 85 columns then maybe easier just to leave the file open (but beware of the bug Vincent mentioned) – Tim Williams Jul 22 '20 at 20:30

0 Answers0