26 October 2007 by Ross McLean
You know the score, you’ve done a pivot table and there are a load of empty cells. You want to fill them up:
[NB: you have to copy the PT as values or the code will fail!]
I use this almost every day:
[vba]
Sub fill_in()
Dim cell As Object
Application.ScreenUpdating = False
For Each cell In Selection
If cell.Value = “” Then
cell.Value = cell.Offset(-1, 0).Value
Else: End If
Next cell
Application.ScreenUpdating = True
End Sub
[/vba]
I think this may be a repost – I did look back but i could not find anything that i had done before, but i remember that Zack Barresse suggesting a none vba way that involvde a special paste of some sort.
Well there you are…
Tags: Examples, VBA
Categories: Functions •
5 Comments »
18 April 2007 by Ross McLean
I want to qualify this post. It’s 11.20 and I’ve seen away a few.
Having said that this is a idea that was suggested to me by a member of www.eusprig.org
Lets do a little role play easy Simon ;-).
My name is Dave. I went to Manchester Uni. for 3 years, I’ve work as a VB dev for 5 years since I graduated. At the mo I work of a ISP that produces a manufacturing scheduling package. I follow all the best practices and am up to date with all the latest knowledge. The software I write is exceptional; my product can give a 20% performance increase to most customers.
Then there’s Paul,
The model needs to be in by 10. It’s this years spend forcaste; I only started to work on this last night at about 8. I need to get all the data from last year and apply this years growth, and the new Asia figures and the effects of the new IT systems. I don’t have the IT figures, but I should get them, if not I’ll wack in some figures.
So there you go.
Now let me tell you that this.
Dave’s customers have a annual turn over of 5 million pounds.
Paul’s net spend is 30 million.
Here’s my question, where would you want the skilled computer programmer?
My point?
Excel more than any other application, is the one where programming skills are required writing SS formulas IS computer programming.
Your opinions are welcomed
Tags: Best Practice, VBA
Categories: Functions •
5 Comments »
22 February 2006 by Ross McLean
Here is some simple code to combine a selction of formulas into one cell. I have not tested it very much so it’s not guaranteed. It will also add a few extra (), than might be needed.
[vba]Sub CombineFormulas()
Dim objCell As Object
Dim sTemp As String
Dim sOperator As String
Dim rOutPut As range
On Error GoTo errorhandel
sOperator = InputBox(“Operator to use?”, “CombineFormulas”)
For Each cell In Selection
If Len(cell.Cells.Formula) > 1 Then
”get the formula, adds a “(” , remove the “=”, add the closing “)”, and puts a “+” on the end
sTemp = sTemp & “(” & Right(cell.Cells.Formula, (Len(cell.Cells.Formula) – 1)) & “)” & sOperator
Else
End If
Next
”Puts an “=” at the start, then gets rid of the last “+”,
sTemp = “=” & Left(sTemp, Len(sTemp) – 1)
Set rOutPut = Application.InputBox(“Put In Cell”, Type:=8)
ActiveSheet.range(rOutPut.Address).Formula = sTemp
Exit Sub
errorhandel:
MsgBox “Error: ” & vbNewLine _
& Err.Description & vbNewLine _
& Err.Number, _
vbOKOnly, “CombineFormulas”
End Sub[/vba]
Tags: Examples, VBA
Categories: Code, Functions •
No Comments »
