CODEWITHSUNDEEP
vbaexcel
Çova
Çova

Reputation: 13

Excel Macro -Cells() why fail?

I am very new to macro . Iam using this code for concatenating two column values into one column. This code today failed , for 10 first rows of the sheet , and it worked for the rest of the rows.Why happened like this , i havent changed anything at all ! Thanks.

Sub FixCrossSell()
    Dim wb As Workbook
    Dim lr As Long

    Set wb = ThisWorkbook
    wb.Worksheets("CrossSell").Activate

    Cells(2, 1).Value = "=B2&E2"

    lr = Cells(Rows.Count, 2).End(xlUp).Row
    Range("A2").Select
    Selection.Copy

    Range("A3:A" & lr).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

    Application.Calculate

    Range("A2:A" & lr).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

Upvotes: 0

Views: 55

Answers (2)

Davesexcel
Davesexcel

Reputation: 6984

Probably just count the cells in column B, then place the Formula in Column A

  Sub Button1_Click()
    Dim LstRw As Long, Rng As Range, Sh As Worksheet

    Set Sh = Sheets("CrossSell")
    With Sh
        LstRw = .Cells(.Rows.Count, "B").End(xlUp).Row
        Set Rng = .Range("A2:A" & LstRw)
        Rng = "=B2&E2"
    End With
End Sub

Ah.. I see someone else answered this while I was thinking about it.

Upvotes: 0

Olly
Olly

Reputation: 7891

Try this:

Sub FiXCrossSell()
    Dim lr As Long
    With Worksheets("CrossSell")
        lr = .Cells(Rows.Count, 2).End(xlUp).Row
        With .Range("A2:A" & lr)
            .FormulaR1C1 = "=rc2&rc5"
            .Value = .Value
        End With
    End With
End Sub

Upvotes: 1

Related Questions