1

Hi guys this is my first post, I'm wondering if you can possibly assist me.

I'd like to write a macro / script that will allow me to put a formula into the column to the right of the currently selected one (for all active rows of the current column) based on what column I've selected. The issue I'm having is that I don't always know the current column letter (as my selection changes from worksheet to worksheet).

To give you an example: One of my columns currently contains dates, that dates are entered in different formats though, some are separated with ".", some with "-", some with spaces and so on. I have a formula that will deal with this so I need to put this formula in the column to the right of the selected column (which has the dates).

I have been able to do this when I specify the column letter, but not if it changes.

Please can you help?

Community
  • 1
  • 1
Danny
  • 13
  • 4

2 Answers2

0

You can use ActiveCell.Offset(0,1).Value = Variable

That means that whetever your current cell is you can move and "select" to put a value to the right cell of the one you have activated. You can move the selection using a loop.

Do
    Workbooks("Yur workbook name").Worksheets(1).Range(Adress you want to start adding).Offset(0, 1).formula = "=FORMULA"

    i = i + 1
    ActiveCell.Offset(1, 0).Activate
Loop While i <= max_row

Edit: 2nd

Put the formula in a cell lets say C1

'Select a range 
Set take = Worksheets(1).Range("C1")
take.Copy 'copy the formula

Worksheets(1).Paste Destination:=Worksheets(1).Range("B1:B10")

That will copy your function whenever you want it to

Blenikos
  • 733
  • 10
  • 19
  • You can combine the 2 methods buy selecting a range and putting to all cells in the range the same formula liek I said in the first solution using .Formula – Blenikos Apr 24 '15 at 11:23
  • I don't thing you need to activate the cell, why not just use `Range(address).formula=sFormula` ? See [SO 10714251: How to avoid using SELECT in excel-vba-macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – Our Man in Bananas Apr 24 '15 at 13:25
0

Give this a go,

Sub SomethingNeat()
    Dim rng As Range, x
    x = Selection.Column
    On Error Resume Next
    Set rng = Columns(x).SpecialCells(xlCellTypeConstants, 23)
    If Not rng Is Nothing Then rng.Offset(, 1) = "'=MyFormula"
End Sub
Davesexcel
  • 6,896
  • 2
  • 27
  • 42
  • Guys thank you all for your assistance but "Davesexcel", you're the biz! Your suggestion worked like a treat. – Danny Apr 27 '15 at 08:28