Merging cells problem

[Sir Babydum GBE]

Hi

I have a macro that merges some cells. Currently, the number of cells to merge to the right of, and including the selected cell, is hard-wired into the code like this:

ActiveCell.Range("A1:f1").Select
With Selection
.MergeCells = True
End With

I select a cell, then click a button which executes the macro and the selected cell, as well as well as the five cells to the right are all merged into one.

What I want to do though is to get the macro to read B3 on sheet 2, that will contain a number. I want the macro to calculate the double of that number and merge that number of cells.

E.g. On sheet 1 I select cell H5. I run the macro which looks at B3 on sheet 2. It says ?3.5?. So on sheet 1, cells H5:N5 (7 cells) are merged.

Can anyone help with this please?

The Guru

[Sir Babydum GBE]

OK, got it:

[vba]ActiveCell.Resize(1, Worksheets("Sheet2").Range("B3") * 2).Merge[/vba]
Hope nobody worked too hard on it.

Cheers

[doctordoggie]

Here is the code you need. It assumes the second sheet is called "Sheet2".

I've tested it as far as I can and it works.

I've attached my working version for you.

Doggie

Sub MergeSomeCells()
Dim work As Single
work = Worksheets("sheet2").Cells(3, 2).Value * 2
workrange = "A1:" + NumberToColumn(work) + "1"
ActiveCell.Range(workrange).MergeCells = True
End Sub
 
Function NumberToColumn(ByVal column As Long) As String
'Converts a number (1-255) to a column ("A" - "IV") 
If column > 26 Then
NumberToColumn = Chr(Asc("A") + (column / 26) - 1) + Chr(Asc("A") + (column Mod 26) - 1)
Else
NumberToColumn = Chr(Asc("A") + column - 1)
End If
End Function

[Sir Babydum GBE]

DoctorDoggie

Thanks for this, and apologies for not responding sooner!