Application Error

[Programs1234]

I keep getting this error in my code:

Worksheets("Buffer Audit Report").Range(Cells(6, ***), Cells(6, *** + 1)).Merge

Can anyone find anything wrong with the code above? thanks!

[mancubus]

is it really 3 *'s or did you obfuscate a variable?

should be Cells(row reference, column reference)

[p45cal]

both instances of Cells need qualifying:
Cells(6, ***)
to:
Worksheets("Buffer Audit Report").Cells(6, ***)

You might get away with un-qualifying .Range

[p45cal]

is it really 3 *'s or did you obfuscate a variable?vBulletin probably 'sanitized' it, I suspect it was 3 xs. Lets have a go:
***
Yup. that was 3 xs.

[mancubus]

vBulletin probably 'sanitized' it, I suspect it was 3 xs. Lets have a go:
***
Yup. that was 3 xs.

if so, i wonder why the OP wants to do that...
and why merging cells as well...:dunno



Dim RowRef As Long, ColRef As Long

RowRef = 6
ColRef = 3

Worksheets("Buffer Audit Report").Range(Cells(RowRef, ColRef), Cells(RowRef, ColRef + 1)).Merge

[Programs1234]

I wanted to run a loop method that would merge 30 pairs of cells together.

[p45cal]

if so, i wonder why the OP wants to do that...
and why merging cells as well...:dunno



Dim RowRef As Long, ColRef As Long

RowRef = 6
ColRef = 3

Worksheets("Buffer Audit Report").Range(Cells(RowRef, ColRef), Cells(RowRef, ColRef + 1)).Merge

Sure, but
Worksheets("Buffer Audit Report").Range(Cells(RowRef, ColRef), Cells(RowRef, ColRef + 1)).Merge
probably needs to be:
Worksheets("Buffer Audit Report").Range(Worksheets("Buffer Audit Report").Cells(RowRef, ColRef), Worksheets("Buffer Audit Report").Cells(RowRef, ColRef + 1)).Merge
or maybe even:
Range(Worksheets("Buffer Audit Report").Cells(RowRef, ColRef), Worksheets("Buffer Audit Report").Cells(RowRef, ColRef + 1)).Merge

Any one of the commented-out lines should work in the following snippet:
For ColRef = 10 To 70 Step 2
'Worksheets("Buffer Audit Report").Cells(6, ColRef).Resize(, 2).Merge
'Worksheets("Buffer Audit Report").Range(Worksheets("Buffer Audit Report").Cells(6, ColRef), Worksheets("Buffer Audit Report").Cells(6, ColRef + 1)).Merge
'Range(Worksheets("Buffer Audit Report").Cells(6, ColRef), Worksheets("Buffer Audit Report").Cells(6, ColRef + 1)).Merge
Next ColRef

[mancubus]

Worksheets("Buffer Audit Report").Range(Worksheets("Buffer Audit Report").Cells(RowRef, ColRef), Worksheets("Buffer Audit Report").Cells(RowRef, ColRef + 1)).Merge

Range(Worksheets("Buffer Audit Report").Cells(RowRef, ColRef), Worksheets("Buffer Audit Report").Cells(RowRef, ColRef + 1)).Merge



yes. Buffer Audit Report must be the active sheet.



RowRef = 3
ColRef = 1
Worksheets("Buffer Audit Report").Activate
Debug.Print Application.Sum(Worksheets("Buffer Audit Report").Range("A3:J3"))
Debug.Print Application.Sum(Worksheets("Buffer Audit Report").Range(Range("A3"), Range("J3")))
Debug.Print Application.Sum(Worksheets("Buffer Audit Report").Range(Cells(3, 1), Cells(3, 10)))
Debug.Print Application.Sum(Worksheets("Buffer Audit Report").Range(Cells(RowRef, ColRef), Cells(RowRef, ColRef + 9)))


or, the better, without selecting the sheet:



RowRef = 3
ColRef = 1
With Worksheets("Buffer Audit Report")
Debug.Print Application.Sum(.Range("A3:J3"))
Debug.Print Application.Sum(.Range(.Range("A3"), .Range("J3")))
Debug.Print Application.Sum(.Range(.Cells(3, 1), .Cells(3, 10)))
Debug.Print Application.Sum(.Range(.Cells(RowRef, ColRef), .Cells(RowRef, ColRef + 9)))
End With

[mancubus]

after activating the sheet, sheet reference in the code is redundant.



RowRef = 3
ColRef = 1
Worksheets("Buffer Audit Report").Activate
Debug.Print Application.Sum(Range("A3:J3"))
Debug.Print Application.Sum(Range(Range("A3"), Range("J3")))
Debug.Print Application.Sum(Range(Cells(3, 1), Cells(3, 10)))
Debug.Print Application.Sum(Range(Cells(RowRef, ColRef), Cells(RowRef, ColRef + 9)))

[p45cal]

after sactivating the sheet sheet reference in the code is redundant.Generally speaking yes, but I can think of 3 instances where that code is likely to falter:
1. The code is in a sheet's code-module which isn't Buffer Audit Report.
2. The sheet you're trying to activate is hidden.
3. Activating that sheet causes an event to trigger which ultimately messes things up, say by activating another sheet.
There's probably more.

I was simply trying to give the OP a solution which was most likely not to fail; remember, we were only given one line of code by the OP. My suggestion changed only that line, and so far I see no reason why it might fail.

[Programs1234]

Apologies for the late reply. I managed to get it to work with the following code:

Worksheets("Buffer Audit Report").Range(Worksheets("Buffer Audit Report").Cells(6, ***), Worksheets("Buffer Audit Report").Cells(6, *** + 1)).Merge

My guess is that the line that I have shown simply did not activate the sheet that was needed to run this code. Thanks!