View Full Version : Question about efficiency
Moragtao
07-05-2017, 10:09 AM
Currently, a lot of the macros I run in Excel require the same thing to do be done to multiple cells. For example, the loop below simply adds an L and a space to the beginning of everything in a particular column.
Do Until ActiveCell.Value = ""
ActiveCell.Value = "L " & ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Loop
What I'm wondering is, is there a more efficient way for me to go about doing this? I know selecting a cell isn't ideal, but this is the best way I currently know to complete the given task.
CROSSPOSTED:
https://www.excelforum.com/excel-programming-vba-macros/1191740-question-about-efficiency.html
mdmackillop
07-05-2017, 11:16 AM
Sub Test()
Dim cel As Range
For Each cel In Range("L:L").SpecialCells(2)
cel.Value = "L " & cel.Value
Next cel
End Sub
Paul_Hossler
07-05-2017, 04:21 PM
This approach can be a little finicky since you have to build the formula string
1. with the double quotes inside a VBA string to represent a 'quote as an embedded character'
2. and there are some 'empty string' double quotes in the formula
So I usually make it easier for me by using the Q = Chr(34) method
I like it since there's no looping required
Option Explicit
Sub test()
Dim sFormula As String, Q As String, QQ As String
Q = Chr(34)
QQ = Chr(34) & Chr(34)
With ActiveSheet.Columns(1).SpecialCells(xlCellTypeConstants)
s = "IF(" & .Address & "<>" & QQ & "," & _
Q & "L " & Q & "&" & .Address & " ," & _
QQ & ")"
.Value = Application.Evaluate(s)
End With
End Sub
The .Evaluate method I learned about here a long time ago, but I forgot the originator
mdmackillop
07-06-2017, 03:11 AM
Hi Paul
You have a problem if there is a blank cell in the data; however over 10k rows, your is 8 times quicker. If I ever find a nut and need a sledgehammer, I know who to ask. :devil2:
Regards
Malcolm
Paul_Hossler
07-06-2017, 08:14 AM
I made an assumption (from #1) that there were no empty cells (you'd think I'd know better)
This is a more robust approach
Option Explicit
Sub test()
Dim sFormula As String, Q As String, QQ As String
Dim rArea As Range
Q = Chr(34)
QQ = Chr(34) & Chr(34)
For Each rArea In ActiveSheet.Columns(1).SpecialCells(xlCellTypeConstants).Areas
With rArea
sFormula = "IF(" & .Address & "<>" & QQ & "," & _
Q & "L " & Q & "&" & .Address & " ," & _
QQ & ")"
.Value = Application.Evaluate(sFormula)
End With
Next
End Sub
Edit: For some reason, even with Option Explicit, using the 's' variable was not flagged as an error, even though sFormula was Dim-ed
I've see that happen a few times, but have not figured out why
mdmackillop
07-06-2017, 08:32 AM
Edit: For some reason, even with Option Explicit, using the 's' variable was not flagged as an error, even though sFormula was Dim-ed
It errors for me. Have you "s" as a Public variable/constant elsewhere?
Paul_Hossler
07-06-2017, 11:53 AM
19687
1. If I select the 's' and right-click for Definition, it throws the error box. But it's still run-able and Alt-d compliable
2. However, after running Rob Bovey's code cleaner, everything works as normal
I've never had a short macro with such a little bit of editing get confused.
It was a head scratcher, but thanks for confirming that it shouldn't work
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.