0

Currently I am busy on an excel macro that can lookup a keynumber in my main excel and find this value in a second workbook. The second workbook is a single column excel file containg a string with data in the per row. I need to return the row where this keynumber is found

I have confirmed that I have a connection to the second workbook however i am unable to search it.

The code I am using to find the keynumber goes as follows:

Dim activeWB As Workbook
Dim activeWS As Worksheet

Dim discountWB As Workbook
Dim discountWS As Worksheet

Dim sTmp As String
Dim discountPath As String
Dim discountGrp As String

Dim row As Range

Dim x As Long
Dim rowCount As Long
Dim index As Long

x = 1
define = Module1.DefineHeaders()

'Retrieve the discount dataset and open this in a hidden excel application
discountPath = Application.GetOpenFilename(Title:="Chose the supplier specific discount dataset")

'Define the workbook containing the discount info
Set discountWB = Workbooks.Open(discountPath)
Set discountWS = discountWB.Worksheets(1)

'Create link to active workbook
Set activeWB = ThisWorkbook
Set activeWS = activeWB.Sheets(1)

activeWS.Activate

rowCount = activeWS.Cells(activeWS.Rows.count, "A").End(xlUp).row

For Each row In activeWS.Rows
    'Skip first Row
    If x > 1 Then

        discountGrp = activeWS.Cells(x, iKortingsgroep).Value

        discountWS.Activate

       'returns a value
        MsgBox "Debug" & discountWS.Cells(1, 1).Value

        '**This causes an error**
        index = discountWS.UsedRange.Find(discountGrp).row
        ***********************************************
        MsgBox "Debug1"


        sTmp = discountWS.Cells(indexKorting, 1).Value
        sTmp = Right(sTmp, Len(sTmp) - 90)
        sTmp = Left(sTmp, 5)

    End If

    x = x + 1

Next row

wb.Close

The error I am getting is : Objectvariable or blockvariable With is not set

I have read just about any stackexchange on similair topics and don't know where my issue is originating. I use the same line to search for my headers at the start of macro so am quite conviced that this should support the functionality that I am looking for and I am certain that I have the proper worksheet activated but just can't figure out why I can't search it.

Hopefully someone here has a solution or some thoughts.

thordijk
  • 15
  • 4
  • 3
    You need to specify more parameters for `Find`... `LookIn` and `LookAt` especially. – BigBen Jun 12 '20 at 14:48
  • 4
    Then see [this](https://stackoverflow.com/questions/1589939/how-to-detect-whether-vba-excel-found-something). – BigBen Jun 12 '20 at 14:52
  • Thank you very much. Also needed LookAt:=xlPart in the end but thank you very much. I was at a loss. – thordijk Jun 15 '20 at 06:11

0 Answers0