VBA Change cells' contents in user's selection

[Bowhaven]

Hello!

I am trying to make a sheet that is as easy-to-use as possible for the end user. I've made buttons which I would like to use to add extra information into the selected cells, without deleting any pre-existing information. All info will be text strings. The selection the user is making could be in any column and any row.

Here's my code so far:

Sub MLD()


numRows = Selection.Rows.Count


numColumns = Selection.Columns.Count


Range(Cells(Selection.Row, Selection.Column), Cells(Selection.Row + numRows - 1, Selection.Column + numColumns - 1)).Select


Selection.Value = "MLD "


End Sub

At the moment it works fine for selecting and changing the value of multiple cells to "MLD " but anything that's already in the cell will of course be overwritten.

So I tried this:

Selection.Value = Selection.Value + "MLD "

Which works absolutely fine if only one cell is selected but turns up a "Run-time error '13': Type mismatch" when multiple cells are selected.

I thought about copying any pre-existing data in the user's selection to a separate column, concatenating it with the MLD then putting the resulting string back into the original selection range. Buuut that's super long-winded and I want to try and make it as easy to modify in the future as possible.

Any thoughts would be greatly appreciated, if I haven't been clear at any point I'll happily clarify!

Thanks,

Chris

 

[Joe4]

Try this:

Sub MLD()

Dim cell as Range

For Each cell in Selection
    cell = cell & "MLD "
Next cell

End Sub

 

[Bowhaven]

Try this:

Sub MLD()

Dim cell as Range

For Each cell in Selection
    cell = cell & "MLD "
Next cell

End Sub

Ahh that's amazing! Works a treat, cannot thank you enough!

 

[Joe4]

You are welcome!