VBA to replace header name

jmazorra

Well-known Member
Joined
Mar 19, 2011
Messages
715
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
 
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:
Code:
    .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.
 
Last edited:
Upvote 0
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:
Code:
    .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
 
Upvote 0
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:
Code:
    .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.
 
Upvote 0
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. ;)
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top