Run-time error 1004 Application-defined or object-defined error vba

Question

I'm using the code that is column letter agnostic. It searches for the column header by name:

Dim ws As Worksheet
Dim column1, column2 As String
Dim lr, lc As Long
Dim col1, col2 As Range
Set ws = ThisWorkbook.Sheets("Sheet1")
lc = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

With ws
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
    lr = .Cells.Find(What:="*", _
        After:=.Range("A1"), _
        LookAt:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Row
    Else
        lr = 1
End If
End With

column1 = "Column 1"
column2 = "Column 2"

Set col1 = ws.Rows(1).Find(What:=column1, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, 
    SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

MsgBox(col1.Column)

Set col2 = ws.Rows(1).Find(What:=column2, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, 
    SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Column

MsgBox(col2.Column)
With ws.Range(.Cells(2, 1), .Cells(lr, lc))
    ' The line below this generates a Run-time error 1004: 
    ' Application-defined or Object-defined error with or without 
    ' the ws.Range(ws.Cells(#, #), ws.Cells(#, #) qualifier.
    .Range(.Cells(2, col1.Column), .Cells(lr, 
    col1.Column)).FormulaR1C1 = 
    "=IF(LEN(RC[col2.Column])>1,RC[col2.Column]*1,"""")"
End With

Column 2 has dates, some with leading zeros, that I want to remove. I plan on placing these dates in Column 1 after applying the above formula. Then:

With ws.Range(.Cells(2, 1), .Cells(lr, lc))
    .Range(.Cells(2, col1.Column), .Cells(lr, col1.Column)).Copy
    .Range(.Cells(2, col1.Column), .Cells(lr, 
    col1.Column)).PasteSpecial xlValues
    .Range(.Cells(2, col2.Column), .Cells(lr, 
    col2.Column)).FormulaR1C1 = "=RC[col1.Column]"
End With

^ I'm guessing that this will also generate a Run-time error 1004: Application-defined or Object-defined error.

Any ideas to correct this will be greatly appreciated! Thanks in advance! Mods - PLEASE note that this question is NOT answered anywhere on StackOverflow. Linked answers have been applied, and they fail to solve this issue.

In addition to the linked duplicate it is a good idea to test that `find` actually found something https://stackoverflow.com/questions/29287975/vba-excel-error-1004-when-using-range-find-method — Warcupine, Nov 09 '22 at 19:02
@Warcupine Thanks! Linked is not a duplicate, as the answer it provides, ie qualifying Cells() with ws.Cells(), does not solve my issue. However, I did test .Find code. A MsgBox(col1), which returned column number "1 " as expected. I'm still getting a Run-time error 1004: Application-defined or Object-defined error. — exceluser, Nov 09 '22 at 20:11
the likely issue is you're applying the `.column` in the same line, if `find` doesn't find anything it is an empty object which has no `.column` property. You'll need to change the variables to Ranges to avoid the type mismatch and use `set`. — Warcupine, Nov 09 '22 at 20:23
@Warcupine Thanks! Find is working, as the MsgBox proves, so .column does have a property. I edited the code above after changing col1 and col2 Dim As Range and applying Set. I still get a Run-time error 1004: Application-defined or Object-defined error. — exceluser, Nov 10 '22 at 00:12
@norie - this line of code generates the error: ws.Range(ws.Cells(2, col1.Column), ws.Cells(lr, col1.Column)).FormulaR1C1 = "=IF(LEN(RC[col2.Column])>1,RC[col2.Column]*1,"""")" — exceluser, Nov 10 '22 at 16:13
You need to replace `col1.column` and `col2.column` with the actual values, Once you've dont that you'll probably need to check you are getting the correct formula. — norie, Nov 10 '22 at 18:09
@norie Range(Cells(#, #), Cells(#, #) eliminates the need for actual values. Using actual range values like Range(A1:B10) is not an option as the workbook is extremely dynamic. Column A could be located in column AC next week. — exceluser, Nov 10 '22 at 18:26
I meant you need to replace `col1.column` and `col2.column` in the formula. — norie, Nov 10 '22 at 18:32
@norie Again, not possible. The workbook is too dynamic. MsgBox (col1.Column) and MsgBox (col2.Column) prove that col1.Column and col2.Column are returning numerical values, which is what .Range(.Cells(#, #), .Cells(#, #) is expecting. — exceluser, Nov 10 '22 at 19:16
So, you literally want to put this exact formula `"=IF(LEN(RC[col2.Column])>1,RC[col2.Column]*1,"""")"` into the cells? Rather than replacing `col1.Column` and `col2.Column` with their actual values? If you do then Excel will be looking for two named ranges called `col1.Column` and `col2.Column`. — norie, Nov 10 '22 at 19:28
@norie Yes. If this problem was ever solved on StackOverflow (I don't believe that it has been, despite the linked answers which do not work in my example), I could apply the above formula into col1, then col1.Copy, col1.PasteSpecial xlPasteValues, and finally transfer those col1 values back to col2. — exceluser, Nov 10 '22 at 19:42
Have you tried replacing `col1.Column` and `col2.Column` in the formula with their values? e.g. `"=IF(LEN(RC[" & col2.Column & "])>1,RC[" & col2.Column & "]*1,"""")"` — norie, Nov 10 '22 at 19:46
@norie Thanks! "=IF(LEN(RC[" & col2.Column & "])>1,RC[" & col2.Column & "]*1,"""")" is the first suggestion that has not resulted in an error. The only issue is that the macro is not multiplying col2 values *1. It's pulling in an unrelated, undefined column, which is odd since you don't appear to be referencing a wild card. — exceluser, Nov 11 '22 at 18:45

Run-time error 1004 Application-defined or object-defined error vba

0 Answers0