• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

With Block using ActiveCell.Offset

R

rcreek09

New Member
ActiveCell.Offset(0, 9).FormulaR1C1 = ActiveCell.Offset(0, -2).Value

ActiveCell.Offset(0, 9).Font.Name = "Calibri"

ActiveCell.Offset(0, 9).Font.Size = 11

ActiveCell.Offset(0, 9).Font.Bold = True

ActiveCell.Offset(0, 9).HorizontalAlignment = xlCenter


I have several sections of code similar to that above. I refer to the cell each and every time. I'm sure it would be more efficient using a With Block(as I was reminded from today's post on speeding up spreadsheets - thanks!).


I have tried some variations, but cannot get the context exactly right because either it runs but does nothing, or I get errors.


I tried the obvious:

With ActiveCell.Offset(0, 9).FormulaR1C1 = ActiveCell.Offset(0, -2).Value

.Font.Name = "Calibri"

.Font.Size = 11

.Font.Bold = True

.HorizontalAlignment = xlCenter

End With


I won't bore you with all the others.


What am I doing wrong? No, not urgent since it does work just fine as is, but I have the "optimization bug" and would like to make it as efficient as I can.


Thanks so much,

Deb
 
Hi Deb,


Try as below:

Code: 
With ActiveCell

.Offset(0, 9).FormulaR1C1 =  .Offset(0, -2).Value

.Offset(0, 9).Font.Name = "Calibri"

.Offset(0, 9).Font.Size = 11

.Offset(0, 9).Font.Bold = True

.Offset(0, 9).HorizontalAlignment = xlCenter

End With


Pls note that "with " have made as Activecell and then start using .properties later until "end with".


Regards,

Prasad DN
 
No reason to keep typing the Offset. =)

[pre]
Code: 
With ActiveCell.Offset(0, 9)
.FormulaR1C1 = .Offset(0, -2).Value
.Font.Name = "Calibri"
.Font.Size = 11
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
[/pre]
As prasaddn said, the With statement needs to include the portion that keeps getting repeated. A way to remember, "You don't like to keep typing the same thing, VB doesn't like to keep reading the same thing."
 
Hi, Luke M!

Maybe we have to change ".Offset(0, -2).Value" by "ActiveCell.Offset(0, -2).Value"? Because of the With...

I think that changing it to ".Offset(0, -11).Value" would work too.

Regards!
 
@Luke M


Hi!

I wouldn't have noticed it if my alike-VB reading engine hadn't disliked so much repeated things, even more all together, and focused to see what was about.

Regards!

PS: Did I tell you that your nick has something...? Oh, yeah, I told you... :)
 
Ok, I was good until SirJB7. I'm not sure I follow that.


So, what I need is to change the .offset(0,-2).value TO .offset(0,-11) as below?


With ActiveCell.Offset(0, 9)

.FormulaR1C1 = .Offset(0, -11).Value

.Font.Name = "Calibri"

.Font.Size = 11

.Font.Bold = True

.HorizontalAlignment = xlCenter

End With


Thanks very much.
 
@rcreek09


Correct. Since the With statement makes the focus on the cell 9 below the current cell, the Offset in question needs to be -11 so that it refers to 2 above the current cell. Again, apologies for me error.
 
Hi, rcreek09!


Sorry for the inconvenience, nothing farther from my intention than contributing to your confusion. But it wasn't working and I preferred the cleaner version instead of the repetitive one. And last version didn't do the job, so I apologize for being the one who brought the bad news...


Regards!
 
You must log in or register to reply here.
Back
Top