Variable Loop (Access to Excel VBA)

[Vagus14]

Hey everyone. I am trying to do the following with the code below:

1. It copies the header of the sheet into SPBName for storage.
2. The code finds YR8 and copies over 7 cells to the right to YR15.
3. The code looks in each column for like *text* and delete the columns.
4. Copies the header it stored in SPBName back to the main sheet at A1 after the column deletion is completed.

This is working in excel but I am trying to make it run in access also.
However, I get an issue with the code in red below.
Run-Time error 13 - Type mismatch
ws.Cells.Find(What:="YR8", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate

Run-Time error 9 - subscript out of range.
Set c = SrchRng.Find("*Text*", LookIn:=xlValues)

Option Compare Database
Public Function DeleteColumns15()
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
Dim wb As Object
Dim ws As Object
Dim Lastrow As Long
Dim c As Object
 
Set wb = .Workbooks.Open("D:\Estimates By CLIN, Activity, and CY - 15 Years.xls")
[COLOR=black]Set ws = wb.Sheets(1)[/COLOR]
[COLOR=red]   ws.Cells.Find(What:="YR8", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _[/COLOR]
[COLOR=red]       xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _[/COLOR]
[COLOR=red]       , SearchFormat:=False).Activate[/COLOR]
    [COLOR=red]wb.Selection.Autofill Destination:=ws.Range("AD2:AK2"), Type:=xlFillDefault[/COLOR]
xlDisplayAlerts = False
ws.Range("A1").Copy
wb.Sheets.Add.Name = "SPBName"
wb.Activesheet.Range("A1").Select
wb.Activesheet.Paste
'Code below finds columns with a text label and deletes them
wb.Worksheets("Customer BOE - Cost Xref").Activate
Set SrchRng = wb.Activesheet.UsedRange
Do
[COLOR=red]Set c = SrchRng.Find("*Text*", LookIn:=xlValues)[/COLOR]
If Not c Is Nothing Then c.EntireColumn.Delete
Loop While Not c Is Nothing
wb.Worksheets("SPBName").Activate
ws.Range("A1").Copy
wb.Worksheets("Customer BOE - Cost Xref").Activate
ws.Cells(1, "A").Select
wb.Activesheet.Paste
wb.Sheets("SPBName").Delete
xlDisplayAlerts = True
End With
End Function

 

[Vagus14]

Been trying and can't seem to figure this out. Anyone have an idea what I may be doing wrong? Thanks!

 

[Rx_]

Sorry I don't have time to look at this in detail:
ws.Cells.Find(What:="YR8", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
wb.Selection.Autofill Destination:=ws.Range("AD2:AK2"), Type:=xlFillDefault
A true/false or constant such as xlNext is understood across the boundry.
Some Objects such as Cells need the object qualifier such as ws.Cells
Look closer at ActiveCell, I think you might need a ws.ActiveCell just as you saw the ws.Range object required a qualifier.

The compiler won't catch this kind of thing on remote automation.
Another key is that a function runs once, then fails the 2nd time.
leavaing off a object qualifier can sometimes run once, leave an orphaned application running, then fail the 2nd time it runs.