Runtime error 1004

[ccordner]

Good Morning

The following code gives me a runtime error 1004. If I take out the reference to the sheet, it works, but on the active sheet. I don't really want to activate the sheet first, because the macro alternates between two worksheets quite frequently. Is there a way of doing this without selecting the sheet first?

With Sheets("Leave Summary").Range(Cells(LeaveHeader, 1), Cells(LeaveHeader, 4)).Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
End With

 

[VoG]

You need to fully qualify the range

With Sheets("Leave Summary").Range(Sheets("Leave Summary").Cells(LeaveHeader, 1), Sheets("Leave Summary").Cells(LeaveHeader, 4)).Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
End With

 

[Richard Schollar]

Hi

You need to fully qualify the Cells() references too:

With Sheets("Leave Summary")
  With .Range(.Cells(LeaveHeader, 1), .Cells(LeaveHeader, 4)).Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
  End With
End With

Note the periods before the Cells()

 

[ccordner]

Just tried that, but it's saying "Invalid or unqualified reference" and highlighting the first ".cells".

 

[Richard Schollar]

Show us what you've tried please

 

[ccordner]

Below is the first part of the sub. The bit in red is highlighted when the error code comes up.

Sub SAPHeader()
LeaveHeader = SummaryRow + 1
If SummaryRow = 4 Then LeaveHeader = 3
With Sheets("Leave Summary")
    .Cells(LeaveHeader, 1) = LocNumber & ", " & Location
    .Cells(LeaveHeader + 2, 1) = "Pay No."
    .Cells(LeaveHeader + 2, 2) = "Name"
    .Cells(LeaveHeader + 2, 3) = "SAP Code"
    .Cells(LeaveHeader + 2, 4) = "Detail"
End With
With Sheets("Leave Summary").Range([COLOR=red].Cells[/COLOR](LeaveHeader, 1), .Cells(LeaveHeader, 4))
        .MergeCells = True
End With
With Sheets("Leave Summary").Range(.Cells(LeaveHeader, 1), .Cells(LeaveHeader, 4))
    .Size = 12
    .Color = -4165632
    .Italic = True
End With
   
With Sheets("Leave Summary").Range(.Cells(LeaveHeader, 1), .Cells(LeaveHeader, 4))
    .Bold = True
End With
   
With Sheets("Leave Summary").Range(.Cells(LeaveHeader, 1), .Cells(LeaveHeader, 4)).Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
End With

 

[VoG]

Try like this

With Sheets("Leave Summary")
    .Range(.Cells(LeaveHeader, 1), .Cells(LeaveHeader, 4)).MergeCells = True
End With