Use variable to denote column letter in range
up vote
0
down vote
favorite
Snippet of the code:
I copy from B, paste into A.
Set A = Workbooks("SwbA").Worksheets("SwsA").Range("A1:A10")
Set B = Workbooks("twbB").Worksheets("twsB").Range("G1:G10")
A.value = B.value
I want to be able to declare a variable such as
Dim col AS STRING
col = "A"
So I can do something to this effect -
Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&1:col10)
Please take note, I don't want to do this -
col = "A1:A10" '--> this works, for other reasons I want to manipulate Column letter only
Please help
excel vba excel-vba range
asked Nov 12 at 10:33
Selvam
50661324
add a comment |
up vote
0
down vote
favorite
Snippet of the code:
I copy from B, paste into A.
Set A = Workbooks("SwbA").Worksheets("SwsA").Range("A1:A10")
Set B = Workbooks("twbB").Worksheets("twsB").Range("G1:G10")
A.value = B.value
I want to be able to declare a variable such as
Dim col AS STRING
col = "A"
So I can do something to this effect -
Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&1:col10)
Please take note, I don't want to do this -
col = "A1:A10" '--> this works, for other reasons I want to manipulate Column letter only
Please help
excel vba excel-vba range
asked Nov 12 at 10:33
Selvam
50661324
3
I don't want to add as a full answer as I know this must be a duplicate question. Declare your columns using numbers (col A=1, B=2, etc). An unqualified reference to columnsA:Jwould then be:Debug.Print Range(Cells(1, 1), Cells(1, 10)).EntireColumn.Address. Good practice says to qualify bothRangeandCellswith the workbook & worksheet name which can be done within aWith....End Withblock.
– Darren Bartrup-Cook
Nov 12 at 10:40
Another way would be to take the first column, calculate how many extra columns you need and resize the rangeDebug.Print Columns(1).Resize(, 10).Address.
– Darren Bartrup-Cook
Nov 12 at 10:46
You could useSet B = Workbooks("SwbB").Worksheets("SwsB").Range(A.Offset(ColumnOffset:=6).Address).
– Pᴇʜ
Nov 12 at 11:19
Thank you for your answers. I do not have the expertise to employ your suggestions. I am learning from this.
– Selvam
Nov 13 at 4:47
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
Snippet of the code:
I copy from B, paste into A.
Set A = Workbooks("SwbA").Worksheets("SwsA").Range("A1:A10")
Set B = Workbooks("twbB").Worksheets("twsB").Range("G1:G10")
A.value = B.value
I want to be able to declare a variable such as
Dim col AS STRING
col = "A"
So I can do something to this effect -
Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&1:col10)
Please take note, I don't want to do this -
col = "A1:A10" '--> this works, for other reasons I want to manipulate Column letter only
Please help
excel vba excel-vba range
asked Nov 12 at 10:33
Selvam
50661324
Snippet of the code:
I copy from B, paste into A.
Set A = Workbooks("SwbA").Worksheets("SwsA").Range("A1:A10")
Set B = Workbooks("twbB").Worksheets("twsB").Range("G1:G10")
A.value = B.value
I want to be able to declare a variable such as
Dim col AS STRING
col = "A"
So I can do something to this effect -
Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&1:col10)
Please take note, I don't want to do this -
col = "A1:A10" '--> this works, for other reasons I want to manipulate Column letter only
Please help
excel vba excel-vba range
excel vba excel-vba range
asked Nov 12 at 10:33
Selvam
50661324
asked Nov 12 at 10:33
Selvam
50661324
edited Nov 13 at 2:12
asked Nov 12 at 10:33
Selvam
50661324
asked Nov 12 at 10:33
Selvam
50661324
asked Nov 12 at 10:33
Selvam
50661324
50661324
3
I don't want to add as a full answer as I know this must be a duplicate question. Declare your columns using numbers (col A=1, B=2, etc). An unqualified reference to columnsA:Jwould then be:Debug.Print Range(Cells(1, 1), Cells(1, 10)).EntireColumn.Address. Good practice says to qualify bothRangeandCellswith the workbook & worksheet name which can be done within aWith....End Withblock.
– Darren Bartrup-Cook
Nov 12 at 10:40
Another way would be to take the first column, calculate how many extra columns you need and resize the rangeDebug.Print Columns(1).Resize(, 10).Address.
– Darren Bartrup-Cook
Nov 12 at 10:46
You could useSet B = Workbooks("SwbB").Worksheets("SwsB").Range(A.Offset(ColumnOffset:=6).Address).
– Pᴇʜ
Nov 12 at 11:19
Thank you for your answers. I do not have the expertise to employ your suggestions. I am learning from this.
– Selvam
Nov 13 at 4:47
add a comment |
3
I don't want to add as a full answer as I know this must be a duplicate question. Declare your columns using numbers (col A=1, B=2, etc). An unqualified reference to columnsA:Jwould then be:Debug.Print Range(Cells(1, 1), Cells(1, 10)).EntireColumn.Address. Good practice says to qualify bothRangeandCellswith the workbook & worksheet name which can be done within aWith....End Withblock.
– Darren Bartrup-Cook
Nov 12 at 10:40
Another way would be to take the first column, calculate how many extra columns you need and resize the rangeDebug.Print Columns(1).Resize(, 10).Address.
– Darren Bartrup-Cook
Nov 12 at 10:46
You could useSet B = Workbooks("SwbB").Worksheets("SwsB").Range(A.Offset(ColumnOffset:=6).Address).
– Pᴇʜ
Nov 12 at 11:19
Thank you for your answers. I do not have the expertise to employ your suggestions. I am learning from this.
– Selvam
Nov 13 at 4:47
3
3
I don't want to add as a full answer as I know this must be a duplicate question. Declare your columns using numbers (col A=1, B=2, etc). An unqualified reference to columns
A:J would then be: Debug.Print Range(Cells(1, 1), Cells(1, 10)).EntireColumn.Address. Good practice says to qualify both Range and Cells with the workbook & worksheet name which can be done within a With....End With block.– Darren Bartrup-Cook
Nov 12 at 10:40
I don't want to add as a full answer as I know this must be a duplicate question. Declare your columns using numbers (col A=1, B=2, etc). An unqualified reference to columns
A:J would then be: Debug.Print Range(Cells(1, 1), Cells(1, 10)).EntireColumn.Address. Good practice says to qualify both Range and Cells with the workbook & worksheet name which can be done within a With....End With block.– Darren Bartrup-Cook
Nov 12 at 10:40
Another way would be to take the first column, calculate how many extra columns you need and resize the range
Debug.Print Columns(1).Resize(, 10).Address.– Darren Bartrup-Cook
Nov 12 at 10:46
Another way would be to take the first column, calculate how many extra columns you need and resize the range
Debug.Print Columns(1).Resize(, 10).Address.– Darren Bartrup-Cook
Nov 12 at 10:46
You could use
Set B = Workbooks("SwbB").Worksheets("SwsB").Range(A.Offset(ColumnOffset:=6).Address).– Pᴇʜ
Nov 12 at 11:19
You could use
Set B = Workbooks("SwbB").Worksheets("SwsB").Range(A.Offset(ColumnOffset:=6).Address).– Pᴇʜ
Nov 12 at 11:19
Thank you for your answers. I do not have the expertise to employ your suggestions. I am learning from this.
– Selvam
Nov 13 at 4:47
Thank you for your answers. I do not have the expertise to employ your suggestions. I am learning from this.
– Selvam
Nov 13 at 4:47
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
I can't see your image but you can do
Dim Col As String
Col = "A"
Dim r As Range
Set r = Range(Col & "1:" & Col & "10")
edited Nov 12 at 15:40
Pᴇʜ
20.1k42650
answered Nov 12 at 14:28
Harassed Dad
2,8061612
HD, i added line for missing image. I followed you suggestion - Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&"1:"&col"10"). It copy pastes to ALL columns, not limiting to col: A. But to the correct rows.
– Selvam
Nov 13 at 2:03
1
You're missing an & Should be Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10")
– Harassed Dad
Nov 13 at 13:42
HD, Typo error in my post. My actual code is as you stated - Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10") . This copy paste to all columns for the same rows.
– Selvam
Nov 14 at 1:09
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53260298%2fuse-variable-to-denote-column-letter-in-range%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
I can't see your image but you can do
Dim Col As String
Col = "A"
Dim r As Range
Set r = Range(Col & "1:" & Col & "10")
edited Nov 12 at 15:40
Pᴇʜ
20.1k42650
answered Nov 12 at 14:28
Harassed Dad
2,8061612
HD, i added line for missing image. I followed you suggestion - Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&"1:"&col"10"). It copy pastes to ALL columns, not limiting to col: A. But to the correct rows.
– Selvam
Nov 13 at 2:03
1
You're missing an & Should be Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10")
– Harassed Dad
Nov 13 at 13:42
HD, Typo error in my post. My actual code is as you stated - Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10") . This copy paste to all columns for the same rows.
– Selvam
Nov 14 at 1:09
add a comment |
up vote
0
down vote
I can't see your image but you can do
Dim Col As String
Col = "A"
Dim r As Range
Set r = Range(Col & "1:" & Col & "10")
edited Nov 12 at 15:40
Pᴇʜ
20.1k42650
answered Nov 12 at 14:28
Harassed Dad
2,8061612
HD, i added line for missing image. I followed you suggestion - Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&"1:"&col"10"). It copy pastes to ALL columns, not limiting to col: A. But to the correct rows.
– Selvam
Nov 13 at 2:03
1
You're missing an & Should be Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10")
– Harassed Dad
Nov 13 at 13:42
HD, Typo error in my post. My actual code is as you stated - Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10") . This copy paste to all columns for the same rows.
– Selvam
Nov 14 at 1:09
add a comment |
up vote
0
down vote
up vote
0
down vote
I can't see your image but you can do
Dim Col As String
Col = "A"
Dim r As Range
Set r = Range(Col & "1:" & Col & "10")
edited Nov 12 at 15:40
Pᴇʜ
20.1k42650
answered Nov 12 at 14:28
Harassed Dad
2,8061612
I can't see your image but you can do
Dim Col As String
Col = "A"
Dim r As Range
Set r = Range(Col & "1:" & Col & "10")
edited Nov 12 at 15:40
Pᴇʜ
20.1k42650
answered Nov 12 at 14:28
Harassed Dad
2,8061612
edited Nov 12 at 15:40
Pᴇʜ
20.1k42650
edited Nov 12 at 15:40
Pᴇʜ
20.1k42650
edited Nov 12 at 15:40
Pᴇʜ
20.1k42650
20.1k42650
answered Nov 12 at 14:28
Harassed Dad
2,8061612
answered Nov 12 at 14:28
Harassed Dad
2,8061612
answered Nov 12 at 14:28
Harassed Dad
2,8061612
2,8061612
HD, i added line for missing image. I followed you suggestion - Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&"1:"&col"10"). It copy pastes to ALL columns, not limiting to col: A. But to the correct rows.
– Selvam
Nov 13 at 2:03
1
You're missing an & Should be Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10")
– Harassed Dad
Nov 13 at 13:42
HD, Typo error in my post. My actual code is as you stated - Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10") . This copy paste to all columns for the same rows.
– Selvam
Nov 14 at 1:09
add a comment |
HD, i added line for missing image. I followed you suggestion - Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&"1:"&col"10"). It copy pastes to ALL columns, not limiting to col: A. But to the correct rows.
– Selvam
Nov 13 at 2:03
1
You're missing an & Should be Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10")
– Harassed Dad
Nov 13 at 13:42
HD, Typo error in my post. My actual code is as you stated - Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10") . This copy paste to all columns for the same rows.
– Selvam
Nov 14 at 1:09
HD, i added line for missing image. I followed you suggestion - Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&"1:"&col"10"). It copy pastes to ALL columns, not limiting to col: A. But to the correct rows.
– Selvam
Nov 13 at 2:03
HD, i added line for missing image. I followed you suggestion - Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&"1:"&col"10"). It copy pastes to ALL columns, not limiting to col: A. But to the correct rows.
– Selvam
Nov 13 at 2:03
1
1
You're missing an & Should be Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10")
– Harassed Dad
Nov 13 at 13:42
You're missing an & Should be Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10")
– Harassed Dad
Nov 13 at 13:42
HD, Typo error in my post. My actual code is as you stated - Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10") . This copy paste to all columns for the same rows.
– Selvam
Nov 14 at 1:09
HD, Typo error in my post. My actual code is as you stated - Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10") . This copy paste to all columns for the same rows.
– Selvam
Nov 14 at 1:09
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53260298%2fuse-variable-to-denote-column-letter-in-range%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
I don't want to add as a full answer as I know this must be a duplicate question. Declare your columns using numbers (col A=1, B=2, etc). An unqualified reference to columns
A:Jwould then be:Debug.Print Range(Cells(1, 1), Cells(1, 10)).EntireColumn.Address. Good practice says to qualify bothRangeandCellswith the workbook & worksheet name which can be done within aWith....End Withblock.– Darren Bartrup-Cook
Nov 12 at 10:40
Another way would be to take the first column, calculate how many extra columns you need and resize the range
Debug.Print Columns(1).Resize(, 10).Address.– Darren Bartrup-Cook
Nov 12 at 10:46
You could use
Set B = Workbooks("SwbB").Worksheets("SwsB").Range(A.Offset(ColumnOffset:=6).Address).– Pᴇʜ
Nov 12 at 11:19
Thank you for your answers. I do not have the expertise to employ your suggestions. I am learning from this.
– Selvam
Nov 13 at 4:47