View Full Version : Automating Tasks in Excel via VBA
jdautel
08-01-2016, 03:25 PM
Hey guys, I started VBA last week and have no prior coding experience. I'm just trying to automate some tasks in the office using it and this is one thing I've been working on a bit. Getting an error message that I don't understand why. Any ideas? My code is below
Option Explicit
Sub maintenanceratio(rng1 As Range, rng2 As Range)
Dim NI As Range, MC As Range, myrange1 As Range, Cell As Object, NI2 As Double, MC2 As Double
'Sets variables as the type I need them to be
For Each Cell In rng1
If Cell.Value = "4110 · Maintenance Contracts" Then
Set MC = Range(Cell.Address).Offset(0, 12)
End If
Next
'for each cell in rng1 it searches for the value "4110 · Maintenance Contracts".
'Once it finds that it sets the variable MC equal to the cell 12 spaces to the right of "4110 · Maintenance Contracts"
For Each Cell In rng2
If Cell.Value = "Total Income" Then
Set NI = Range(Cell.Address).Offset(0, 15)
End If
Next
'it then repeats the same step for rng2 and NI
NI.Select
'this is where the problem is, I'm trying to select the cell NI but it is giving me error 91. I don't understand seeing as it is defined above as a range
NI2 = ActiveCell.Value
MC.Select
MC2 = ActiveCell.Value
ActiveCell.Offset(0, 1) = MC2 / NI2
End Sub
Sub callmaintenanceratio()
Call maintenanceratio(Range("G1:G20"), Range("D1:D30"))
End Sub
Paul_Hossler
08-01-2016, 05:30 PM
Guessing ....
If this never gets executed
Set NI = Range(Cell.Address).Offset(0, 15)
because there is never a Cell.Value = "Total Income", then NI would be Nothing
As a test, replace
NI.Select
with
If NI Is Nothing then
Msgbox "Nothing"
Else
NI.Select
End If
If that is the case, then you'll need to handle the Not Found case
BTW, approx. 50% of a macro is error handling, the other 50% is user interface, and the remaining 50% is actual computation
I don't remember what an error 91 is all about, but try this
Option Explicit
Sub maintenanceratio(rng1 As Range, rng2 As Range)
Dim NI As Range, MC As Range, myrange1 As Range, NI2 As Double, MC2 As Double
Dim Cel As Range 'don't use eywords or words that may become keywords
'Uses only the last cell in the range that matches
For Each Cel In rng1
If Cel.Value = "4110 · Maintenance Contracts" Then
Set MC = Cel.Offset(0, 12)
'Exit for 'Uncomment the "Exit For" to use the first match
End If
Next
For Each Cel In rng2
If Cel.Value = "Total Income" Then
Set NI = Cel.Offset(0, 15)
'Exit For
End If
Next
'Do some error checking
If MC Is Nothing Or MC = 0 Then
MsgBox "Did not find any 4110 · Maintenance Contracts"
Exit Sub
ElseIf NI Is Nothing Or NI = 0 Then
MsgBox "Did not find any Total Income"
Exit Sub
End If
MC.Offset(0, 1) = MC / NI
End Sub
Sub callmaintenanceratio()
'What Sheet?
maintenanceratio Range("G1:G20"), Range("D1:D30")
End Sub
Aussiebear
08-02-2016, 12:48 AM
BTW, approx. 50% of a macro is error handling, the other 50% is user interface, and the remaining 50% is actual computation
ROFl....:devil2: Maths, is your strong point Paul?
jdautel
08-02-2016, 07:01 AM
Got it working. I think, this is embarrassing, that I was testing it on the wrong spreadsheet. Once I complete this code for every line item in the budget it's going to save SO much time for us in the office. Thanks for y'alls help.
One other thing, right now I've been working through a course on VBA on the side just to get more formally acquainted with coding. Do you guys have any suggestions for good resources or courses to take to learn?
I can make things happen but no where near comfortable enough to do it in the most efficient and elegant ways.
Paul,
You forgot the 75% that is designing the Worksheet layout.
Ted,
That math sounds about right, it always takes at least twice as long as assumed.
Do you guys have any suggestions for good resources or courses to take to learn? I can make things happen but no where near comfortable enough to do it in the most efficient and elegant ways.
VBA Express Forum - Excel Help (http://www.vbaexpress.com/forum/forumdisplay.php?f=17)
Elegance is in the eye of the beholder, and we get into some discussion about that here. Pay attention to the lists of last posters and you will quickly learn to recognize familiar names.
Elegance is also about code style, compare the styles of different members, regardless of what the code is doing, or even if it is error free. Unfortunately, the forum software imposes a particular white space style on all posted code, so download member example workbooks to see the subtler details of their unique code style.
Some code styles sacrifice some efficiency in favor of Self Documenting Code.
For pure efficiency and depth of understanding of VBA for Excel, read and parse every bit of code by member snb.
If you have access to a pre-2007 version of Office, extract the help files and study the Object Models. IMO, understanding the entire Application Object Model is critical. If you search for "Object model" under my Member Name, you should find an attachment showing a complete Excel model on one sheet. If it hasn't been removed from storage yet.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.