Set WorkSheet not Working

After spending a lot of time reviewing answers on Experts Exchange (and select other Excel VBA Forums) I am attempting to rewrite existing code to eliminate the use of Select and Activate and incorporate "Cells" rather than always needing to convert a numeric value for a column to an alpha character.

My code module is developed for the explicit purpose of rolling out a new version of code.  The program builds a list of a user's existing files and then copies all pertinent data into the new program file.  While the code technically worked as it existed, the numerous times I used either "Select" or "Activate" I now know is not efficient code and also caused significant screen refreshes.

Without getting too detailed, the data table in each user's file can have a different number of columns with different data types by column.  So, as part of the code to migrate old data to the new program file I need to determine the last column and last row (of contiguous input, not last row on the sheet).  With the following code, I am attempting to change the WorkSheet for the file I am importing.  I have used the Set command to change the value of my WorkSheet variable "wsI".  However, when the code runs to establish the last column and last row values it is giving me the values from the previous WorkSheet.  

The variable "ImportWbk" is set to the name of the file from which I am copying data.  Using Debug.Print I have verified that ImportWbk is set to the correct filename.  After setting "wsI" (defined as WorkSheet variable) to "Analysis" I have confirmed via the Debug.Print command that it is indeed now set to "Analysis".

Can someone help me to get this corrected?

    Set wsI = ImportWbk.Worksheets("Analysis")
    Debug.Print wsI.Name
    With ImportWbk
        ImportLC = Sheets(wsI).Cells(21, Columns.Count).End(xlToLeft).Column
        ImportLR = Sheets(wsI).Range("A22").End(xlDown) - 1
    End With

Open in new window

LVL 1
Frank BatusicAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try
    Set wsI = ImportWbk.Worksheets("Analysis")
    Debug.Print wsI.Name
  '  With ImportWbk
        ImportLC = wsI.Cells(21, Columns.Count).End(xlToLeft).Column
        ImportLR = wsI.Range("A22").End(xlDown) - 1
  '  End With

Open in new window

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank BatusicAuthor Commented:
Rgonzo,

Thank you for the very quick response.  I made the changes you suggested and had partial success.  The variable ImporLC is now correctly set to 10.  However, ImportLR is still set to 216 which is still from the previous WorkSheet that wsI was set to.  

Here is my updated code:
    Set wsI = ImportWbk.Worksheets("Analysis")
    Debug.Print wsI.Name
    With ImportWbk
        ImportLC = wsI.Cells(21, Columns.Count).End(xlToLeft).Column
        ImportLR = wsI.Range("A22").End(xlDown) - 1
    End With

Open in new window

0
Rgonzo1971Commented:
without a file it's difficult to have an opinion
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Is all this happening inside a For Loop?
Also are you using On Error Resume Next somewhere?
Try using
Set wsI = Nothing before the Next of For loop.
0
Rgonzo1971Commented:
is the -1 one row less then try
ImportLR = wsI.Range("A22").End(xlDown).Offset( -1)

Open in new window

0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
As per the variable ImportLR, it is supposed to hold the row number so the syntax should be like this...

ImportLR = wsI.Range("A22").End(xlDown).Row - 1
0
Frank BatusicAuthor Commented:
Neeraj,

Adding the ".Row" fixed the issue.  Thank you for your assistance.

Frank
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Frank! Glad I could help.
0
Frank BatusicAuthor Commented:
The modifications suggested by Rgonzo and Neeraj resolved by issue.  Thank you to both for lending your expertise!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.