ActiveCell.Offset(, 1).Resize(1, 100).Select Does not work unless stepping through (1 Viewer)

So, I have been stuck on this code for a few hours now. I know it's something simple and if I had a vacation and come back I would figure it out and end up smacking my head on my desk. However, the crux of the module is to open one excel file from an access database find a value and select the 100 cells toward the right of that selected cell.

Later those cell values will be loaded in an array and then the database will open a template to paste those values for a capability study based off those 100 entries.


Right now I am at the point of selecting those cells, but access keeps giving me the old "Run-time error '91': Object variable or with block not set".

It runs the first time fine then all other times it throws the error. I just need it to select the rows so I can get to the next step which is to load those selected cell values in that array. (suggestions to that point would be great as well)

Here is the full module so far

Option Explicit
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Dim myCellChar, myCellNum, myEndRange As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim RNG As Range


Public Function AccessForm()
Dim myMonth, myYear, myRow, myCol As Integer
Dim myPartFile, myFolder, myFind, myFilePath As String
Dim myLocale As Variant

myMonth = Month(Form_frm_Cap_Study.txt_Begin)
myYear = Year(Form_frm_Cap_Study.txt_Begin)
myPartFile = Form_frm_Cap_Study.cbo_Part & ".xlsm"
myFolder = myYear & "-" & "0" & myMonth ' IF statement to come later for months that are double digits
myFind = Form_frm_Cap_Study.txt_Weld_Number
myFilePath = "Z:\Destruct Data\Data Graphs\Monthly Destruct Data" & myFolder & "" & myPartFile
Debug.Print myFilePath
Set Xl = New Excel.Application
Set XlBook = Xl.Workbooks.Open(myFilePath)
Set XlSheet = Xl.Sheets("Data")

'opens the excel sheet where the data is coming from and finds the cell in which the user inputs the weld number to search for
Xl.Application.Visible = True
Xl.Parent.Windows(1).Visible = True
XlSheet.Activate
Set RNG = XlSheet.Cells.Find(What:=myFind, lookin:=xlValues, Lookat:=xlWhole)
myLocale = RNG.Address

'finds the row and col number
myRow = RNG.Row
myCol = RNG.Column

'selects the found cell
If RNG Is Nothing Then

Else
RNG.Select

End If
'This section selects the data to the right of the selected cell and encompasses 100 cells for max data

ActiveCell.Offset(, 1).Resize(1, 100).Select



Set Xl = Nothing
Set XlBook = Nothing
End Function

At first glance I would say move all your Dim statements so that they are part of the Function.

Your set statements are Setting variables that have not been declared within the same scope...AND...Your set statements at the end of the function are setting something to nothing that has not been set yet.

Bet I could turn that into a country song...

So I tried as you suggested. It still does the run-time error after the first time it's ran. I also took out the last set to nothing statements.

What line is the code breaking on when you debug it?

when I compile the file there is no error. However when the code is ran for the second time or later it stops at

ActiveCell.Offset(, 1).Resize(1, 100).Select

kyprogrammer said:

However when the code is ran for the second time or later...

Click to expand...

So the code runs and returns the expected results the first time it is run?

Sure does. It's crazy! Sometimes it works every other run.

I would try putting your Set to Nothing code back in (I really don't thin that is the issue, just housekeeping)

Try closing the excell worksheet completely in between runs and see if that works. It appears the issue is that the Mico$oft Nazis do not like making another instance of Excel.

You're probably right on that one. Now do you have any suggestion on how to add those selected cells to an array?

OK shew had to grab a mtn dew then it hit me

myCol = 9

For j = 1 To 100

myVals(j) = Cells(myRow, myCol)

myCol = myCol + 1

Next

Sorry, had to do my Son-in-law duties. Seems like you have it figured out?

Hey I understand!

Yea I got what I needed to work finally. I am still having trouble with the every other time it runs it errors out issue. Trying to sift through the internet for a solution on that one.

A quick read of post mentioned Setting to Nothing, so not sure if you also have
Before

sxschech said:

A quick read of post mentioned Setting to Nothing, so not sure if you also have

Code:Copy to clipboard

Xl.Quit

Before

Code:Copy to clipboard

Set Xl = Nothing

Click to expand...

I have tried that it throws the error at the line and putting Xl.quit doesn't have a chance to run
ActiveCell.Offset(, 1).Resize(1, 100).Select

You need a reference to Excel then "ActiveCell" isn't known by MS-Access.