VBA or Formula? to assign a number based on driving miles

[steve400243]

Hello, I have a Code that i found online and am using to get 'google distance' for driving miles between 2 zip codes. I am trying to get a VBA, or cell formula, to assign a zone number for every 5 miles in distance. On the example sheet Cell B6 has what i am trying to get to auto populate. This will need to go up to 200 miles. Thanks for your time and help.

[SamT]

Add this function to Module 1

Public Function GetZone(Milage As String) As Integer
GetZone = WorksheetFunction.RoundDown(Split(Milage, " ")(2) / 5, 0)
End Function

In Cell I4, use this formula
=If(H4<>"",GetZone(H4),"")

Copy down as needed

Oops! My bad. the B6 Formula is
=If(F5<>"",GetZone(F5),"")

[steve400243]

Thank you for your time Sam, I really appreciate it. Would I add this code in Module 1 at the end? Also how would this zone number then populate in Cell B6?

[steve400243]

Saw your correction, thank you. In cell B6, I am getting a #value error with that code?

[KevO]

Another way to get there - formula in B6 looks up the value found in F5 against table of Zone values (held in sheet named Zone)


=VLOOKUP(ROUNDDOWN(F5,0),Zones!A2:C41,3,TRUE)


(Your worksheet contains a few merged cells which are nice for presentation purposes. But be aware that merged cells can cause a range of problems with VBA! So I try my best to avoid merging cells)

[steve400243]

Thank for the help KevO, I appreciate your time. This will work for what I need!

[SamT]

Saw your correction, thank you. In cell B6, I am getting a #value error with that code?Is there a value in F5?

Try changing the IF(F5 <>"" to IF(F5>0

[steve400243]

Hello Sam, F5 is where the Driving Miles populates when the From and To address, or zip codes, are entered in. I tried adjusting as noted above and get the same error? Any thoughts?

[SamT]

Yeah. I think I need to find a new brain. or at least a set of eyes that see.

The original formula for use in Column I, will in no way work with F5.
Use this Function when using the value of F5

Public Function GetZone2(Milage As String) As Integer
GetZone2 = WorksheetFunction.RoundDown(Milage / 5, 0)
End Function

B6 formula =IF(F5<>"",GetZone2(F5),"")

BTW, both functions will work on milages up to zone 32K

[steve400243]

Thank you for your time Sam, I appreciate the help. I got it working as needed with this code -

[=INDEX(GetZone!A2:C41,MATCH(F5*1,GetZone!B2:B41,1),3)]

[steve400243]

Thank you for your time Sam, I appreciate the help. I got it working as needed with this code -

[=INDEX(GetZone!A2:C41,MATCH(F5*1,GetZone!B2:B41,1),3)]

[steve400243]

Yeah. I think I need to find a new brain. or at least a set of eyes that see.

The original formula for use in Column I, will in no way work with F5.
Use this Function when using the value of F5

Public Function GetZone2(Milage As String) As Integer
GetZone2 = WorksheetFunction.RoundDown(Milage / 5, 0)
End Function

B6 formula =IF(F5<>"",GetZone2(F5),"")

BTW, both functions will work on milages up to zone 32K

In testing - i'm getting 2 in B6, and it should be 3, not sure what i'm doing wrong?

[steve400243]

I think i have it working correct now using your code, and function. by changing the roundDown line to just Round? Sound Correct?

[SamT]

It all depends on where you want the cutoff to be. For Cutoff below 5.0 miles
Use Rounddown(mileage +4.9999, 0)/5)

4.9999 > Mileage from 0.0001 miles to 5.0001 miles = Zone 1

Since Google reports mileage by 0.1, Google mileages from 0.1 to 5.0 are Zone 1. Google Mileage 5.1 = Zone 2

If you want the cutoff to be above 5.0 miles, use
Rounddown(mileage +5, 0)/5)

Google Mileage 0.1 to 4.9 will be Zone 1 and G-Mileage 5 would be Zone 2

[steve400243]

Ah ok that makes sense. I believe I have it now with Rounddown(mileage+5, 0)/5). Thank again for your help, have a nice night.

[SamT]

Oops. Did I say that? my bad.
Rounddown((mileage+5) /5),0)

Details, details, details :crying:


Happy Thanksgiving.