Problematic Code

silentJ

New Member
Joined
May 29, 2021
Messages
6
Office Version
  1. 2007
Platform
  1. Windows
I am hoping that some one could help me out with the following code.
I am getting an error 1004 - application defined or object defined error but Iv no idea why. Its showing on the line...

VBA Code:
Set srchheader = Worksheets("Data").Range("G2:G" & lastrow).Find(Worksheets("Stats").Range(mycell.Offset(0, 1)).Value, LookAt:=xlWhole)

Its like its finding it because on debug the yellow highlight shows the search value am looking for. I think the problem is something to do with the "srcheader" part?
The larger part of the code is below

VBA Code:
Dim mycell As Range
Dim rng2 As Range
Dim league As String
Dim match As String
Dim srchheader As Range
Dim srchheader2 As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set rng2 = Worksheets("Stats").Range("A5:A100")
For Each mycell In rng2
    If mycell.Value <> "" Then
        match = mycell.Value
        
        lastrow = Workbooks("NX01.xlsm").Worksheets("Data").Range("G" & Rows.Count).End(xlUp).Row
        Set srchheader = Worksheets("Data").Range("G2:G" & lastrow).Find(Worksheets("Stats").Range(mycell.Offset(0, 1)).Value, LookAt:=xlWhole)
        If Not srchheader Is Nothing Then
            Worksheets("Stats").Range(mycell.Offset(0, 52)).Value = srchheader.Offset(0, -1).Value
        End If
        lastrow = Workbooks("NX01.xlsm").Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Row
        Set srchheader2 = Worksheets("Data").Range("A2:A" & lastrow).Find(What:=srcheader, LookAt:=xlWhole)
        If Not srchheader2 Is Nothing Then
            Worksheets("Stats").Range(mycell.Offset(0, 53)).Value = srchheader2.Offset(0, 1).Value
        End If

Thanks in advance
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,877
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
Set srchheader = Worksheets("Data").Range("G2:G" & lastrow).Find(mycell.Offset(0, 1).Value, LookAt:=xlWhole)
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,877
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,148,008
Messages
5,744,324
Members
423,862
Latest member
ExcelUserX

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