Having a good dive is mostly just a mindset and paying for it just takes a little creativity and a willingness to work.
For example, I started diving in the middle of South Dakota, yet we had a large number of local divers with a very active diving community. Strange when you consider that SD is not exactly an area most people associate with diving, let alone good diving. The key is to take maximum advantage of what is available in your area. For example we had a large (200 mile long) reservoir with depths to 240 ft, excellent spearfishing opportunities (walleye, salmon, northern pike, perch, small mouth bass and buffalo carp), interesting underwater structures, as well as the tailrace and river below the damn, and drift diving below a hydro electric plant also has some very interesting diving possibilities.
One of the revenue sources for me as a starving college student/diver was the fishing lures lost in the tailrace. It was common to do a drift dive in tailrace with it's 3-7 kt current (depending on how much water they were putting through) and recover anywhere from a half dozen to twenty or more lost fishing lures that would snag on rocks, cables left over from construction of the dam, on previously lost fishing line. It took a quick eye and some effort to see them, and cut them free while holding in the current. We would clean them, re-hook them (with hooks bought in bulk) and then sell them either through the local shop or directly to the many fishing guides in the area and it would more than pay for your diving expenses. In fact, it more than paid for my dive gear and training for the several years I was there.
The river itself was a highway through the region for fur trappers since before the country was even born, and saw about 10,000 years of use by Native Americans before that, so the artifact possibilities are almost literally endless. In addition skulls from an extinct species of buffalo were also a fairly regular find as they washed out of the bottom.
In the western part of the state we also had some alpine lakes and reservoirs where the water was cold and deep but the visibility consistently very good, again with the potential for flooded structures as well as flooded mine shafts that had been made in the granite slopes along side the now flooded creek. I slipped my boat in a marina (free slip in exchange for underwater inspection and maintenance of the marina) on the largest alpine lake in the area and enjoyed just about every weekend there in the spring, summer and fall, arriving on Friday afternoon and staying on and diving off my boat all weekend long on a very picturesque forested mountain lake with excellent diving. 130 dives a year was the average and between the no cost slip in the marina, and my own compressor, my cost per dive was dirt cheap even after the boat expenses were considered, and at the time I was also charging $200 per hour and $100 per hour for subsequent hours for commercial dive work, which turned my diving into a decent source of part time income in addition to the reg repair work I did for the local dive shop.
I liked the deep, cold water diving found there, but freshwater diving also offers a great deal of plant and fish life in the shallower areas above about 40 ft, so it's rare to have a "boring" dive even when the dive is shallow.
Now, you have to put a couple things in perspective as 10 ft visibility was considered good, 20-30 ft was "great" with anything more than that being stellar visibility. Ice diving is the frequent exception as the stable, undisturbed water under the ice can offer exceptional visibility, an ice diving brings with it a new set of challenges and rewards.
As mentioned above, over the years my diving also extended to search and recovery, vehicle and body recovery, and a variety of inland commercial diving activities such as inspection and repair of pipes, head gates, intake structures, dams, marinas, bridges, etc. Most of it was low viz or no viz diving, but all of it was interesting and can't recall many "boring" dives at all - and it paid fairly well for a part time job.
The end result is that I could feed that income back into technical dive training and technical wreck diving trips to the Great Lakes. A friend and technical dive partner would fly his Twin Comanche to the Great Lakes for both training and wreck diving trips, reducing the travel time involved.
Eventually I moved to the east coast for a change of pace, better employment and the opportunity for easier offshore wreck diving. However I very shortly afterward gravitated to cave diving, making on average four seven day long trips per year to north FL or Mexico for cave diving, resulting in around 50 cave dives per year. Most of that was paid for with reg repair for two shops in the Baltimore/Washington DC area until moving to NC a few years ago, where the combination of telecommuting to a high paying job from a location in NC with a low cost of living pretty much eliminates the need to worry about funding my diving.
Like the OP, I'm not a fan of paying $125 plus a fuel surcharge for offshore diving that requires me to get up at 4am to drive to the coast, or alternatively rent a hotel the night before for a minimum of $100, only to find that the boat isn't leaving the dock due to the wind, on more than half the trips I book. It's entirely possible for two of us to spend $400 in a weekend and get no diving, or worse get two 40 minute dives on a crappy low viz, cold water, inshore wreck. That makes cave diving 9 hours away a relative bargain when we can each get twelve to fourteen 90 to 120 minute cave dives in a week for around $1000-$1250 total.
Still, I augment my cave diving with the occasional offshore wreck charter (maybe 2 per year) and every few years a live aboard trip, along with regular dives in the local quarry to keep my skills sharp. Or, like today, assisting a partially paralyzed recon Marine to develop his buoyancy and trim skills to the point he is ready for Cavern and Intro to Cave classes in June. That is enjoyable as well, and in some respect it represents t me what diving is all about - a life long learning process and the pursuit of perfection in your diving.