Link to home
Start Free TrialLog in
Avatar of Geekamo
GeekamoFlag for United States of America

asked on

How do I select a specific cell?

I am using the following code:


Sub ConvertToTable()
Dim tbl As Range Dim ws As Worksheet     For Each ws In ActiveWorkbook.Sheets         If ws.Name <> "Summary" Then         Set tbl = ws.Range("A1").CurrentRegion       ws.ListObjects.Add(SourceType:=xlSrcRange, XLListObjectHasHeaders:=xlYes, Source:=tbl).Name = ws.Name     Range("A1").Select         End If     Next ws End Sub

Open in new window


I added: Range("A1").Select


When this workbook is auto-generated, all worksheets have cell A1 selected EXCEPT the last page (which is currently selected on cell J54).


By adding "Range("A1").Select", my goal was to ensure that after the tables were created, it would move the cursor into cell A1.


So that when the code has finished running, if I navigate through each worksheet, A1 is selected on every sheet.


But, it doesn't work... Any ideas why?


(On a side note, if I run that line of code on it's own - it does select the correct cell)

Avatar of Martin Liss
Martin Liss
Flag of United States of America image

It works for me. I added line 10.

Sub ConvertToTable()

Dim tbl As Range
Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Sheets
        If ws.Name <> "Summary" Then
            Set tbl = ws.Range("A1").CurrentRegion
            ws.ListObjects.Add(SourceType:=xlSrcRange, XLListObjectHasHeaders:=xlYes, Source:=tbl).Name = ws.Name
            Range("A1").Select
            Msgbox ActiveCell.Address
        End If
    Next ws
End Sub

Open in new window

Avatar of Geekamo

ASKER

@Martin Liss,

Hmm, when I run your code - each popup does say $A$1. But none of these worksheets end up having A1 selected.

Any ideas? 
Why do you believe they are not selected?
Avatar of Geekamo

ASKER

@Martin Liss,

When I open the workbook:

  1. Accounts (B11 is selected)
  2. Completed (B22 is selected)
  3. Remaining (C15 is selected)
  4. Missing Monthly (J54 is selected)

After I run the code, the same cells are selected.  None of them have cell A1 selected.


How about using
Range("A1").Activate

Avatar of Geekamo

ASKER

@Martin Liss,

User generated image
Avatar of Geekamo

ASKER

@Rob Henson,

No, that doesn't work either. :(
Can you attach your workbook, or a sample workbook that has one sheet of dummy data?
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Geekamo

ASKER

@byundt,

That worked! I used: Application.GoTo ws.Range("A1")

Thank you everyone for your help!
You have to activate the worksheet before selecting a cell on it.
That doesn't seem to be true in this case.
Avatar of Geekamo

ASKER

@Martin Liss,

Are you saying that because it was reporting back each A1 reference in the popup?

(All of this is over my head)
The statement Range("A1").Select only applies to the active worksheet. And you cannot .Select a cell on a non-active worksheet. That’s why I said what I did in my Comment. Application.GoTo is a workaround I frequently use because selecting objects is so slow. 
Sorry byundt and Geekamo, I was wrong.
Avatar of Geekamo

ASKER

@byundt

When you say selecting an object is so slow...

Are you basically saying,... when the command "Range("A1").Select" is executed - before it even has time to complete that step, it's already moved onto the next line of code - therefor it hasn't technically completed that line of code?

But if the same command is performed on the ACTIVE sheet, there's no speed limitations?

(Sorry, I'm just trying to better understand all this and I have no idea if I'm on track or not)


VBA code executes synchronously. One statement has to be completed before the VBA run-time engine processes the next statement. Because of that fact, statements that .Select or .Activate objects are going to slow down the macro considerably. "Slow down" may or may not be a problem for your particular macro--that's for you to judge.

Without a qualification preceding it, e.g. ws.Range("A1"), Range("A1") will always refer to the active worksheet. Your original code never changed the active worksheet, so your original macro just selected the same cell (cell A1 on the active worksheet) say a dozen times. That's why you said the code wasn't working.

If you preceded the statement selecting cell A1 with one that selects or activates worksheet ws, then the active worksheet becomes ws and Range("A1").Select selects a cell on that worksheet.

Using Application.GoTo ws.Range("A1") obviates the need to first select or activate worksheet ws and then select cell A1 on that worksheet because it takes the cursor to ws.Range("A1") directly. I frequently use Application.GoTo to return to the original selected cell when writing code that adds worksheets, opens workbooks or does other things that change the active worksheet.
Dim celHome As Range
Set celHome = ActiveCell
'Do stuff that changes the active worksheet
Application.GoTo celHome
End Sub

Open in new window

Martin Liss and Rob Henson are both VBA experts, and very familiar with these issues. They just temporarily forgot about them when viewing the other stuff in your code. That's why Marty posted such a gracious apology, for an oversight I've made myself many times in the past.
The way I understand it, whichever sheet is Active when the code is executed the Range("A1").Select will be performed on that sheet because no other sheet is activated in the code. So Martin's line of code giving a popup with the range selected will always work because it gets selected on the active sheet on the first run through and then doesn't change so appears to work on the following iterations.
@byundt - looks like our comments were posted at similar times; thanks for the compliment and yes I had missed that point; New Year fog  .🥂🥂
Avatar of Geekamo

ASKER

@byundt

Thank you very much for the thorough explanation!