Variable for Excel Columns. Column Assigned to Variable

Question

I have a macro that inserts 2 columns on my current sheet and pastes information from another sheet.

I want to create 2 variables that are assigned to each column that would change the next time I run the macro to paste the information in the next two columns.

Columns("BO:BO").Select

Selection.Insert Shift:=xlToRight

Range("BO2").Select
ActiveCell.FormulaR1C1 = "Feb weekly-wk 2"
Range("BO19").Select
ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(Comparison!RC2,'Jan16 wk4'!R3C15:R34C24,9,FALSE)"
Range("BO19").Select
Selection.AutoFill Destination:=Range("BO19:BO47"), Type:=xlFillDefault
Range("BO19:BO47").Select

Columns("BP:BP").Select

Selection.Insert Shift:=xlToRight
Range("BP2").Select
Selection.Style = "20% - Accent6"
Range("BP2").Select
ActiveCell.FormulaR1C1 = "Diff"
Range("BP19").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"

My idea is to set up a variable that I would replace my current "BO" and "BP" code with.

Dim X as String, Y as String

X = "BO"

y = "BP"

When I run the macro it would change the variable for this example "BO" to "BQ" and "BP" to "BR". Next time I run the macro would change the "BQ" to "BS" and "BR" to "BT".

Alternative to @ScottCraner's suggestion: `Range(Columns(x),Columns(x))`. — BruceWayne, Jan 29 '16 at 22:11
Ah, also, maybe you don't know but you can also refer to a column by it's index (column A = 1, B = 2, etc). I use this a *ton* in loops. You could use a single variable, say `Dim i as Long`, then do a loop: `For i = 1 to 10 ... Columns(i).Select ... next i` will loop through columns A through J, and select them. Or you can use like `Columns(i+1)` where you can't do `Columns("BO"+1)`. — BruceWayne, Jan 29 '16 at 22:17

score 1 · Answer 1 · answered Jan 29 '16 at 23:51

I just cleaned your code a little:

Dim ColBO As Integer
Dim ColBP As Integer

Dim StrBO As String
Dim StrBP As String

StrBO = "BO"
StrBP = "BP"

ColBO = ActiveWorkbook.Range(StrBO & 1).Column 'instead of StrBO you could directly write ("BO" & 1)
ColBP = ActiveWorkbook.Range(StrBP & 1).Column 'Then you wouldnt need these two variables

Columns(ColBO).Insert Shift:=xlToRight
'Columns(ColBO).Select   ' Trying to avoid selection but not sure if this works here...
'Selection.Insert Shift:=xlToRight

Range(1, ColBO).FormulaR1C1 = "Feb weekly-wk 2"
Range(19, ColBO).FormulaR1C1 = "=VLOOKUP(Comparison!RC2,'Jan16 wk4'!R3C15:R34C24,9,FALSE)"
Range(19, ColBO).AutoFill Destination:=Range("BO19:BO47"), Type:=xlFillDefault

Columns(ColBP).Insert Shift:=xlToRight  'Same here as above
Range(2, ColBP).Style = "20% - Accent6"
Range(2, ColBP).FormulaR1C1 = "Diff"
Range(19, ColBP).FormulaR1C1 = "=RC[-2]-RC[-1]"

For the future: If you can, try to avoid .Select/Selection/.Activate if possible. The code can mostly run without such commands and without activating a cell. ;)

Thanks for your help. I will be able to test this in Monday. Do I need to change anything for this part? Range(19, ColBO).AutoFill Destination:=Range("BO19:BO47"), Type:=xlFillDefault I will want the BO19:BO47 to change as well according to the column — MikeB, Jan 30 '16 at 21:04

score 0 · Answer 2 · edited May 23 '17 at 11:44

0

If you are not actually writing BO/BP to the range you are transforming I would go with two ints, stored in a hidden sheet. Read/write each time you run the macro.

This is, in my opinion, the easier solution, other places to go would be global variables or storing it to a file.

edited May 23 '17 at 11:44

Community

1
1

answered Jan 29 '16 at 22:17

cladelpino

337
3
14

score 0 · Answer 3 · answered Jan 30 '16 at 09:02

If you want to use numeric variables you can change approach and use Cells instead of Range:

'You can use the rows below to know the column number
Range("BO1").Activate
ActiveCell.Value = ActiveCell.Column 'This way you get the column number into the cell
ColNum = ActiveCell.Column 'This way you get the column number into the variable
'So now you know that BO column number is 67 and you can use
Cells(1, 67) = "OK"
'Or, using variables:
RowNum = 1
ColNum = 67
Cells(RowNum, ColNum) = "You Got It!"

This makes you able to loop columns simply using a for ... next
If you need to loop from BO to BR you can use

For ColNum = 67 To 70
    Cells(1, ColNum) = "OK"
Next ColNum

Hope it helps.

Variable for Excel Columns. Column Assigned to Variable

3 Answers3