Start Macro in different cell each time

[sjp1979]

I have created a macro that copies a concatenated series of cells then runs 'text to columns' on it.

I want to repeat this process using the same cell to copy from (the text will change) but copy into a different cell i.e. the first time I run it I want it to start in E2 but the next time may be in E5 and then E3 etc.

I am pretty new to this and have no clue about the code. What I have is below. I'm guessing it's the 'Offset cell' that I need to be dynamic but I don't know how to do this.

Also, if anyone knows how to run text to columns without it bringing up the warning it would be great to remove that.

Please help!

Thanks

Sub text2columns()
'
' text2columns Macro
'
' Keyboard Shortcut: Ctrl+m
'
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
Range("M24").Select
Selection.Copy
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveCell.Offset(-22, -8).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=".", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True
End Sub

 

[RobertSF]

If the cell you're copying from is always the same, and what changes is the destination cell, you have to tell Excel what that destination cell is. You can't use numbers with Offset because those numbers are going to change depending on where the destination cell is, and if you use variables, you still have to tell Excel what values to put in those variables (values which, again, will change depending on where the destination cell is).

Would it work if you had the user select the destination cell before invoking the macro? That would "tell" Excel where the destination cells is (ActiveCell), and since the source cell is always the same, the same code would always work.

 

[sjp1979]

Hey Robert

That's exactly what I'm after. I just don't know how to change the code to make it do that.

I recorded it and thought it would record that way but it seems to have done it differently.

 

[RobertSF]

I've only been working with VBA for a few months, so I'm not an expert either, so maybe this isn't the best solution, but here's what I got.

From what I understand, you have a string in cell M24 that contains periods. The string might look like this "a.b.c.d.e.f," and the string is always in M24. You would like to place the cursor in any cell (say cell B3), and have the macro break down the string into columns separated at the period and inserted starting at B3. In this example, B3=a, C3=b, D3=c, etc., etc.

Sub text2columns()
    ActiveSheet.Range("M24").Copy ActiveCell
    ActiveCell.TextToColumns , , , , , , , , , "."
End Sub

The macro recorder is often a good start, but it does everything with selections instead of ranges, and it happens that ranges are more efficient than selections in code.

The first line copies the one-cell range M24 to the active cell.
The second line converts the string in the active cell to columns starting at the active cell itself.

As you can see, most of the parameters do not need to be specified. In your case, we just need to specify the period as the delimiting character.

I hope this helps and is what you were looking for. Let me know if I misunderstood a detail in your specification.