VBA - Application-defined or object-defined error

CC268

Active Member
Joined
Mar 7, 2016
Messages
326
I know I must be missing something silly here. I keep getting the error message, "Run-time error '1004': Application-defined or object-defined error" on the bolded line below. Can't seem to figure out what I'm doing wrong.


Code:
Sub EEE_Reformat()


  Dim wb1 As Workbook
  Dim cell1 As Range, rng1 As Range, cell2 As Range, rng2 As Range
  Dim Cel As Range
  Dim Sht1 As Worksheet
  Dim Sht2 As Worksheet
  
  Set wb1 = ThisWorkbook
  
  Set Sht1 = wb1.Sheets("Sheet1")
  Set Sht2 = wb1.Sheets("Sheet2")
  
  Set Cel = Sht1.Range("A2")
  Set rng1 = Range(Cel, Cel.Offset(Sht1.Cells.Rows.Count - Cel.Row, 0).End(xlUp))
  Set Cel = Sht2.Range("A2")
  Set rng2 = Range(Cel, Cel.Offset(Sht2.Cells.Rows.Count - Cel.Row, 0).End(xlUp))
       
[COLOR=#ff0000][B]  If Sht1.Cells(i, 13) = "" Then[/B][/COLOR]  'if current cell in column 13 is empty then...
    For Each cell2 In rng2        'for each cell in range 2 defined above (column A in Sheet2)...
      For Each cell1 In rng1      'for each cell in range 1 defined above (column A in Sheet1)...
        If cell2.Value = cell1.Value And cell2.Offset(0, -5) = cell1.Offset(0, -1).Value Then   'if the value of cell2 equals the value of cell1 AND the value of cell2 (offset by 5 columns) equals the value of cell1 (offset by 1 columns) then...
          cell1.Offset(0, -12).Value = cell2.Offset(0, 1).Value    'from to Sheet1 column B to Sheet2 column M
          cell1.Offset(0, -13).Value = cell2.Offset(0, 2).Value    'from to Sheet1 column C to Sheet2 column N
          Exit For
        End If
      Next
    Next
  End If


End Sub
 
Last edited:

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,518
Office Version
  1. 365
Platform
  1. Windows
What is the value of "i"?
Where have you defined it and set it equal to anything?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,523
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You never assigned i a value, so it's being treated as 0 and you can't have row 0.
 

CC268

Active Member
Joined
Mar 7, 2016
Messages
326
What is the value of "i"?
Where have you defined it and set it equal to anything?

I changed it to the following, but now I get the same error on a few lines down

Code:
Sub EEE_Reformat()

  Dim wb1 As Workbook
  Dim cell1 As Range, rng1 As Range, cell2 As Range, rng2 As Range
  Dim Cel As Range
  Dim Sht1 As Worksheet
  Dim Sht2 As Worksheet
  
  Set wb1 = ThisWorkbook
  
  Set Sht1 = wb1.Sheets("Sheet1")
  Set Sht2 = wb1.Sheets("Sheet2")
  
  Set Cel = Sht1.Range("A2")
  Set rng1 = Range(Cel, Cel.Offset(Sht1.Cells.Rows.Count - Cel.Row, 0).End(xlUp))
  Set Cel = Sht2.Range("A2")
  Set rng2 = Range(Cel, Cel.Offset(Sht2.Cells.Rows.Count - Cel.Row, 0).End(xlUp))
       
For i = 2 To 2500
  If Sht1.Cells(i, 13) = "" Then  'if current cell in column 13 is empty then...
    For Each cell2 In rng2        'for each cell in range 2 defined above (column A in Sheet2)...
      For Each cell1 In rng1      'for each cell in range 1 defined above (column A in Sheet1)...
      [COLOR=#ff0000][B]  If cell2.Value = cell1.Value And cell2.Offset(i, -5) = cell1.Offset(i, -1).Value Then[/B][/COLOR]   'if the value of cell2 equals the value of cell1 AND the value of cell2 (offset by 5 columns) equals the value of cell1 (offset by 1 columns) then...
          cell1.Offset(i, -12).Value = cell2.Offset(i, 1).Value    'from to Sheet1 column B to Sheet2 column M
          cell1.Offset(i, -13).Value = cell2.Offset(i, 2).Value    'from to Sheet1 column C to Sheet2 column N
          Exit For
        End If
      Next
    Next
  End If
Next


End Sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,518
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try adding this error debugging code, and let us what message is returned, and what the values are in those two cells:
Code:
Sub EEE_Reformat()

  Dim wb1 As Workbook
  Dim cell1 As Range, rng1 As Range, cell2 As Range, rng2 As Range
  Dim Cel As Range
  Dim Sht1 As Worksheet
  Dim Sht2 As Worksheet
  
  Set wb1 = ThisWorkbook
  
  Set Sht1 = wb1.Sheets("Sheet1")
  Set Sht2 = wb1.Sheets("Sheet2")
  
  Set Cel = Sht1.Range("A2")
  Set rng1 = Range(Cel, Cel.Offset(Sht1.Cells.Rows.Count - Cel.Row, 0).End(xlUp))
  Set Cel = Sht2.Range("A2")
  Set rng2 = Range(Cel, Cel.Offset(Sht2.Cells.Rows.Count - Cel.Row, 0).End(xlUp))
       
On Error GoTo err_chk
For i = 2 To 2500
  If Sht1.Cells(i, 13) = "" Then  'if current cell in column 13 is empty then...
    For Each cell2 In rng2        'for each cell in range 2 defined above (column A in Sheet2)...
      For Each cell1 In rng1      'for each cell in range 1 defined above (column A in Sheet1)...
        If cell2.Value = cell1.Value And cell2.Offset(i, -5) = cell1.Offset(i, -1).Value Then   'if the value of cell2 equals the value of cell1 AND the value of cell2 (offset by 5 columns) equals the value of cell1 (offset by 1 columns) then...
          cell1.Offset(i, -12).Value = cell2.Offset(i, 1).Value    'from to Sheet1 column B to Sheet2 column M
          cell1.Offset(i, -13).Value = cell2.Offset(i, 2).Value    'from to Sheet1 column C to Sheet2 column N
          Exit For
        End If
      Next
    Next
  End If
Next
On Error GoTo 0

Exit Sub

err_chk:
    MsgBox "Error when cell1 address is " & cell1.Address & " cell2 address is " & cell2.Address
    
End Sub
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,523
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You can't offset to the left of column A.

Perhaps you should just explain what you're trying to do?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,655
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Both rng1 & rng2 are column A so you cannot offset to the left.
Should the -5 & -1 be just 5 & 1, ie columns F & B
 

CC268

Active Member
Joined
Mar 7, 2016
Messages
326
You can't offset to the left of column A.

Perhaps you should just explain what you're trying to do?

Sorry...I modified this code from something I previously did and I don't do much VBA. I basically have two Excel sheets populated with data. I'm trying to take the Part Number and Next Higher Part Number in Columns A and B of Sheet 1 and look for those in Columns A and F of Sheet 2 (the values in Sheet 1 and Sheet2 could be in completely different rows). If the two match then I want it to pull over data from Sheet 2 to Sheet 1 (e.g. pull the data in Column B in Sheet 2 to Column M in Sheet 1).
 
Last edited:

CC268

Active Member
Joined
Mar 7, 2016
Messages
326
Try adding this error debugging code, and let us what message is returned, and what the values are in those two cells:
Code:
Sub EEE_Reformat()

  Dim wb1 As Workbook
  Dim cell1 As Range, rng1 As Range, cell2 As Range, rng2 As Range
  Dim Cel As Range
  Dim Sht1 As Worksheet
  Dim Sht2 As Worksheet
  
  Set wb1 = ThisWorkbook
  
  Set Sht1 = wb1.Sheets("Sheet1")
  Set Sht2 = wb1.Sheets("Sheet2")
  
  Set Cel = Sht1.Range("A2")
  Set rng1 = Range(Cel, Cel.Offset(Sht1.Cells.Rows.Count - Cel.Row, 0).End(xlUp))
  Set Cel = Sht2.Range("A2")
  Set rng2 = Range(Cel, Cel.Offset(Sht2.Cells.Rows.Count - Cel.Row, 0).End(xlUp))
       
On Error GoTo err_chk
For i = 2 To 2500
  If Sht1.Cells(i, 13) = "" Then  'if current cell in column 13 is empty then...
    For Each cell2 In rng2        'for each cell in range 2 defined above (column A in Sheet2)...
      For Each cell1 In rng1      'for each cell in range 1 defined above (column A in Sheet1)...
        If cell2.Value = cell1.Value And cell2.Offset(i, -5) = cell1.Offset(i, -1).Value Then   'if the value of cell2 equals the value of cell1 AND the value of cell2 (offset by 5 columns) equals the value of cell1 (offset by 1 columns) then...
          cell1.Offset(i, -12).Value = cell2.Offset(i, 1).Value    'from to Sheet1 column B to Sheet2 column M
          cell1.Offset(i, -13).Value = cell2.Offset(i, 2).Value    'from to Sheet1 column C to Sheet2 column N
          Exit For
        End If
      Next
    Next
  End If
Next
On Error GoTo 0

Exit Sub

err_chk:
    MsgBox "Error when cell1 address is " & cell1.Address & " cell2 address is " & cell2.Address
    
End Sub

Thanks it came with $A$2 and $A$2. Makes sense I had the offset wrong. I'm thinking this code structure isn't going to work for what I am trying to accomplish though. Not sure exactly how to go about it. (See response above to Rory)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,540
Messages
5,529,434
Members
409,876
Latest member
Akash Yadav
Top