VBA to replace header name

[jmazorra]

Hello everyone:

I know this is an easy one, but is late in the day and I can't think anymore, plus I am a rookie at VBA. I am trying to replace the header in column I1 with the title Exemption. But I am erroing out. The code is part of a larger code, so I only posted the part that is giving the problem. The part in red is where the problem is.

Private Sub CommandButton1_Click()
'Prepare exports
'Delete cells and find and replace Y
'delete columns that are not referenced

Dim k As Long, I As Long, ws As Worksheet

With Sheets("byposition")

.Range("E:E").Copy .Range("A1")
.Columns("I:I").Replace What:="$", Replacement:="N", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Columns("I:I").Replace What:="", Replacement:="Y", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

.Range("I1").Select
ActiveCell.FormulaR1C1 = "Exemption"

End With

 

[jbeaucaire]

There is no need to "select" thing in VBA, the recorder misleads you because it is recording your actions. Send your command directly to the range object. Also, it's not a formula, so you can take that out, too:

    .Range("I1") = "Exemption"
End With

The reason it is erroring out is because we've already cleaned up this code earlier and taken out earlier "activation" of this sheet. So you really can't "select" a cell on sheet that isn't even active. But that's OK, we don't need. We don't activate or select in efficient code. Get in the habit now of removing all those selects and merge those commands together, like shown.

 

[jmazorra]

Thanks for the advise. I am still feeling my way through VBA, sort of trying to self teach.

 

[jmazorra]

There is no need to "select" thing in VBA, the recorder misleads you because it is recording your actions. Send your command directly to the range object. Also, it's not a formula, so you can take that out, too:

    .Range("I1") = "Exemption"
End With

The reason it is erroring out is because we've already cleaned up this code earlier and taken out earlier "activation" of this sheet. So you really can't "select" a cell on sheet that isn't even active. But that's OK, we don't need. We don't activate or select in efficient code. Get in the habit now of removing all those selects and merge those commands together, like shown.

I tried the line code, but it did not work. The cell did not change name

 

[jmazorra]

There is no need to "select" thing in VBA, the recorder misleads you because it is recording your actions. Send your command directly to the range object. Also, it's not a formula, so you can take that out, too:

    .Range("I1") = "Exemption"
End With

The reason it is erroring out is because we've already cleaned up this code earlier and taken out earlier "activation" of this sheet. So you really can't "select" a cell on sheet that isn't even active. But that's OK, we don't need. We don't activate or select in efficient code. Get in the habit now of removing all those selects and merge those commands together, like shown.

Sorry, it did work. I just had a senior moment. I had forgotten to add the Exemption to the list of names to not delete.

 

[jbeaucaire]

Glad it worked.

TIP: Resist "Quoting" other people's posts into your own, makes for a messy thread. Just use the Quick Reply button or box below. Thanks.