0

I need to find a merged cell in the active sheet and copy the value to the adjacent cell.
I tried to write a macro below but this is not copying the cell value. I am getting Error 424 in the line ActiveSheet.Cells(row, col).Select

Below is my table:

    A       B
 **Merged Cell1**   
 Value1         
 Value2         
 Value3         
 text4          
 text5          
 text6          
 text7          
 text8          
 **Merged Cell3**           
 Value1         
 Value2         
 **Merged Cell4**           
 text4          
 text5          
 text6          
 text1          
 **Merged Cell5**           
 text4          
 text5          
 **Merged Cell5**           
 text

 

Sub TestMacro5()
    Dim rcount As Integer
    Dim row As Integer
    Dim col As Integer
    Dim i As Integer

    ActiveSheet.Select
    col = 1
    row = 1
    i = 1
    rcount = Application.CountA(Range("A:A"))

    For i = 1 To rcount
        ActiveSheet.Cells(row, col).Select
        If Selection.MergeCells Then
            ActiveCell.Offset(1, 5).Value = ActiveCell.Value
            row = row + 1
            Exit Sub
        End If
    Next i
End Sub
Vidya Sahar
  • 11
  • 1
  • 1
    Go through this post [*avoid using select/activesheet*](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) to improve your coding. I think that's the only problem you got. – L42 Jan 28 '15 at 05:45

1 Answers1

0

It is best to stay away from using .Select to deterine the cell or worksheet that you want to deal with.

Dim rcount As Long
Dim rw As Long
Dim cl As Long
With ActiveSheet
    cl = 1
    rw = 1
    rcount = .Cells(Rows.Count, 1).End(xlUp).row
    For rw = 1 To rcount
        If .Cells(rw, cl).MergeCells Then
            .Cells(rw, cl).Offset(1, 5) = .Cells(rw, cl).Value  '1 row down and 5 columns over ?
            Exit For   'why exit here?
        End If
    Next rw
End With

See How to avoid using Select in Excel VBA macros for more methods on dealng with cells, cell ranges and worksheets directly.

Community
  • 1
  • 1