1004 application error, PLEASE HELP!!

[johnandginalyn]

Hi,
I have a macro that works in the spreadsheet it was written in but when I try to run it in an identical spreadsheet outside of where the macro is stored then I get the 1004 error.
I understand that this is a reference issue with the sheets but I can get rid of an error in the formula line.

Sub LookingUp()
Dim wks As Worksheet
Dim col As Integer
Dim colA As Integer
Dim colB As Integer
Dim rowA As Integer
Dim row As Integer
Dim ref As Range
Dim ts As String
rowA = 1
ActiveWorkbook.Sheets(1).Select
Cells.Find(What:="Asset Id", After:=ActiveCell, LookIn:=xlFormulas, _
      LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False).Activate
colB = ActiveCell.Column
colA = colB + 1
For Each wks In ActiveWorkbook.Worksheets
    
    If Not wks.Name = "List_of_Assets" Then
        wks.Select
        Cells.Find(What:="Asset Id", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        Selection.End(xlDown).Select
        row = ActiveCell.row
        col = ActiveCell.Column
        
        ActiveWorkbook.Sheets(1).Select
        Cells(1, colA).Select
        ActiveCell.EntireColumn.Select
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        
        Cells(1, colB).Select
        Selection.End(xlDown).Select
        rowA = ActiveCell.row
        
        Cells(2, colA).Select
        [COLOR=red]ActiveCell.Formula = "=VLOOKUP(" & Cells(2, colB).Address(False, False) & "," & wks.Name & "!" & Cells(2, col).Address & ":" & Cells(row, col).Address & ",1,0)"[/COLOR]
        Selection.AutoFill Destination:=Range(ActiveCell, Cells(rowA, ActiveCell.Column))
        
        colA = colA + 1
End If
Next wks
End Sub

I'm getting the error on the line in red.
I really hope someone can shine a light on this.

Please help,
John

 

[Norie]

John

If a worksheet name has spaces (or various other characters) you need to enclose it in single quotes for a formula.

ActiveCell.Formula = "=VLOOKUP(" & Cells(2, colB).Address(False, False) & "," & "'"&  wks.Name & "'!" & Cells(2, col).Address & ":" & Cells(row, col).Address & ",1,0)"

That should fix the formula but I'm not 100% sure that's the only problem in the code.

You seem to be missing quite a few worksheet references and all that selecting/activating makes it quite hard to follow.


PS you don't need to specify a sheet name if all the references in the lookup are on the same sheet as the one the formula is going in.

 

[malvdh]

John

If a worksheet name has spaces (or various other characters) you need to enclose it in single quotes for a formula.

I have a similar problem with inserting a formula via vba.
I have tried to put in single quotes but to no avail. Am I totally off here?

    Range("A2").Activate
    ActiveCell.Formula = "=IF(LEFT(C2,8)="Keyword",LEFT(C5,5),A1)"

 

[Norie]

You aren't using a worksheet name.

The problem is the "s around Keyword.

Just double them up.

Range("A2").Formula   = "=IF(LEFT(C2,8)=""Keyword"",LEFT(C5,5),A1)"

 

[malvdh]

That works great! Thanks Norie!