CODEWITHSUNDEEP
vbaexcel
Scavenger
Scavenger

Reputation: 133

Error when setting borders in macro

I am running a macro that sets the format of a group of cells. 

Public LastRow as Integer
Sub Formatting()
    LastRow = 20
    With ThisWorkbook.Sheets("Sheet1").Range("A15:" & "AA" & LastRow)
        .Borders.Weight = xlThin
    End With
End Sub

When I run the macro the first time it works but on the second time I receive the following error Run-time error '1004': Unable to set the Weight property of the Borders class.

Also I am unable to manually change the borders of the effected cells now. I'm not sure what is happening. I am running excel 2010

Thanks in advance,

Upvotes: 0

Views: 2647

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149297

Few Things

  1. Do not use a Function for this. Use a Sub. You use a Function when you want to return something
  2. Fully Qualify your variables/Objects
  3. I don't see you setting the value of LastRow anywhere.

Try this

Sub Formatting()
    Dim LastRow As Long
    LastRow = 20

    '~~> Change Sheet1 to the relevant sheetname or use Code Name
    With ThisWorkbook.Sheets("Sheet1").Range("A15:" & "AA" & LastRow)
        .Borders.Weight = xlThin
    End With
End Sub

Upvotes: 1

Related Questions