Forum Discussion
Selecting an Excel Range in VBA
I have code in a Command Button which is in Worksheet "Weekly".
The code in the command button is trying to select a range in another worksheet in the same workbook.
The other worksheet is named "Groups"
This code works:
'Current active sheet is "Weekly up to here"
Sheets("Groups").Select
Sheets("Groups").Range("B2:L34").Select ' I need to use cells to select the range instead of "B2:L34"
'I need to select ranges with numeric rows and columns as below in the code that does not work
This code does not work:
'Current active sheet is "Weekly"
Sheets("Groups").Select
Sheets("Groups").Range(Cells(DateR, NameC), Cells(LastNameR, GFriIndexC)).Select
'DateR is an integer = 2
'NameC is an integer =4
LastNameR is an integer =34
GFriIndexC is an integer = 12
13 Replies
WallisMcMath by "not work" what do you mean? do you get an error? the wrong location is selected? something else?
Also, maybe you could/should attach your workbook or paste your actual code (maybe redacting parts if needed) but little things like curved vs straight quotation marks are common problems that we wouldn't know if you retype or transcribe your code here.
I have attached the spreadsheet.
Go to the "Weekly" sheet and click on the "Import Print Data" command button to see the error occur
If I comment the line of code that causes the error and un-comment the line of code above the error line, the code works as wanted.
Sheets("Groups").Range(Cells(2, 2), Cells(34, 12)).Select ' this is the line of code that gets the error.
I need to be able to select a range using numeric rows and columns instead of numeric rows and alpha columns.
I have placed comment lines above and below the code with rows of asterisks
WallisMcMath as I suspected the "cells" references are referring back to the Weekly sheet instead of the Groups sheet. Besides, your technique of using .select to continuously change focus throughout your code is not a good idea for a number of reasons. after a brief look at your code you should declare a variable for those sheets (e.g. Dim wstGroups as worksheet) and then set wstGroups = Sheets("Groups") and then just use that variable to find areas and such.
Also as previously mentioned, you can just take action of those areas instead of doing the .select
I this example code I am using the "with" statement to make life easier and prefix the "cells" with a period to say use the "Groups" sheet instead of whatever sheet it wants (and it worked for me but it hit another error later in your code):
With Sheets("Groups")
.Range(.Cells(2, 2), .Cells(34, 12)).Copy
End With
Sheets("Weekly").Range("A1").Select 'xx
ActiveSheet.Pictures.Paste(Link:=True).Select
'************************************************************************************************************
If you just want to select B2:L34 in the Groups sheet as you describe, the following code lines will work.
DateR = 2 NameC = 2 LastNameR = 34 GFriIndexC = 12 Sheets("Groups").Range(Cells(DateR, NameC), Cells(LastNameR, GFriIndexC)).SelectWhat you want to do with it, once selected (copy, clear, delete, change format etc.), is another issue.
Thanks much for the response, but what you listed is what I tried.
The code is in a CommandButton click event which is in the worksheet "Weekly" and the area to be selected is in Worksheet "Groups"
The following code Works
Worksheets("Groups").Activate
Sheets("Groups").Range("B2:L34").Select
The following does not work
Worksheets("Groups").Activate
Sheets("Groups").Range(Cells(2,4), Cells(34,12)).Select '(error occurs on this line of code)
(I removed the variable names and replaced with numbers)
The error message is Application-defined or object-defined error
WallisMcMath But it worked for me when I took the code and created a Sub with it. See attached!
This code will not work as this is not a best practice to use .select
May I know what you want to achieve.
IF you want to copy the range from another sheet. you can do it with this code.
Sheets("Groups").Range("A2:A5").copy Sheets("Weekly").Range("A1")
A1 is a destination where you want to paste
Now if you want to use your integers. you can do it like this:
Sheets("Groups").Range("A" & DateR & ":" & "A" & Date R).copy Sheets("Weekly").Range("A1")
Application.cutcopymode =false
Considering DateR is a row
Else you can use very simple trick:
Sheets("Weekly").Range("A1").value = Sheets("Group").Range("A2").value
Or else
Sheets("Weekly").Range("A" & DateR).value = Sheets("Group").Range("A" & DateR).value
you can modify your code accoridngly. Right part is destination and Left part from where you want to copy the value
You should write VBA code what you want to achieve without selecting the range
