PDA

View Full Version : Application Error



Programs1234
10-02-2017, 12:15 PM
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
10-02-2017, 12:53 PM
is it really 3 *'s or did you obfuscate a variable?

should be Cells(row reference, column reference)

p45cal
10-02-2017, 12:57 PM
both instances of Cells need qualifying:
Cells(6, ***)
to:
Worksheets("Buffer Audit Report").Cells(6, ***)

You might get away with un-qualifying .Range

p45cal
10-02-2017, 01:01 PM
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
10-02-2017, 01:31 PM
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
10-02-2017, 01:37 PM
I wanted to run a loop method that would merge 30 pairs of cells together.

p45cal
10-02-2017, 01:58 PM
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
10-03-2017, 04:52 AM
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
10-03-2017, 02:19 PM
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
10-03-2017, 03:57 PM
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
10-04-2017, 07:28 AM
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!