PDA

View Full Version : [SOLVED] Merging cells problem



Sir Babydum GBE
08-16-2005, 07:02 AM
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? :dunno

The Guru

Sir Babydum GBE
08-16-2005, 07:37 AM
OK, got it:

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

Cheers :)

doctordoggie
08-16-2005, 07:58 AM
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
09-09-2005, 03:35 AM
DoctorDoggie

Thanks for this, and apologies for not responding sooner!