VBA Excel Error 91, error while setting an object

0
votes

I'm having a problem with my VBA Excel code, I'm trying to merge selected cells and put the data that is behind it where the words were merging. (Deleting the cell didn't work.)

Here is an example (| is the separator for the cells):

|Stack|Overflow|100|None|Something|

Run the code and it should look like this:

|Stack Overflow|100|None|Something|

But I'm getting "Error 91 Object variable or With block not set," here is the code:

Dim Cells As Range
Dim CellsContents As String
Dim RangeCells As Range
Set RangeCells = Application.Selection

For Each Cells In RangeCells
    CellsContents = CellsContents & Cells.Value & " "
Next
RangeCells.Value = VBA.Left(CellsContents, VBA.Len(CellsContents) - 1)
Range(Cells(ActiveCell.Row, ActiveCell.Column + Selection.Count), Cells(ActiveCell.Row, 20)).Copy Destination:=Cells(ActiveCell.Row, ActiveCell.Column + Selection.Count - 1)

The error is happening on the last line of the code. I tried to put Set before it with an object variable but then I got an error on the destination. If you want some more information on this because I know it's a little confusing just let me know.

2
vbaexcel

2 Answers

1
votes

Your problem is that in the last line the variable Cells is no longer in scope.

Hence you get the error you describe "object variable not set".

0
votes

You cannot use "cells" this way. You need to reference a worksheet object before: ie. activesheet.cells or worksheet("SheetName").cells

replace the last line with the below and it should work.

Hope this helps.

Range(ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column + Selection.count), ActiveSheet.Cells(ActiveCell.Row, 20)).Copy ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column + Selection.count - 1)