Automating Tasks in Excel via VBA

[jdautel]

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]

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

[SamT]

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]

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]

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.

[SamT]

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.

[SamT]

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.