1

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".

Improve this question
3
  • 1
    then you would use it like this Columns(x & ":" & x) Commented Jan 29, 2016 at 22:06
  • Alternative to @ScottCraner's suggestion: Range(Columns(x),Columns(x)). Commented Jan 29, 2016 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). Commented Jan 29, 2016 at 22:17

3 Answers 3

Reset to default
2

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. ;)

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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
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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.