Solved: Help with looping code

[JG4life]

I don't even know if "looping code" is what you call it but I am at a loss as to how to do this without copying and pasting my code 25 times. I have a little section of code that when you click in a cell it checks to see if certain conditions exist and then creates a formula. Below is the code.

 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo Finished
If Target.Address = "$I$17" Then
    If Range("$H$17") = "Personal Vehicle" And Range("$I$17") = "" Then
 
        Dim days As Integer
        days = InputBox(Prompt:="Enter the number days you used your " _ &
                personal Vehicle for Company Use for this expense reporting period", _
                Title:="Days used")
        ActiveCell.Formula = "=((M4-M3-M5) *.4) +(10*" & days & ")"
    End If
    Else
End If
Finished:
End Sub

What that does is if the value of H17 is Personal vehicle and I17 is empty find out how many days they used their company vehicle and then create a formula to calculate how much thay are to get paid for mileage for using their personal vehicle. This code works perfectly but only for the 17th row. I want it to do it for every row between 8 and 32 without having to copy and paste that code 24 more times.

There are other sections of code in the form I am workin on that I have already created the code 25 times (fortunately only 2 lines of code each time LOL) and I figured it is high time I figure out how to do this properly.

Any help would be greatly appreciated.

Sean

[Bob Phillips]

[vba]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo Finished
With Target
If .Row >= 8 And .Row <= 32 And .Column = 9 Then
If Range("H" & .Row) = "Personal Vehicle" And .Value = "" Then
Dim days As Integer
days = InputBox(Prompt:="Enter the number days you used your " _ &
"personal Vehicle for Company Use for this expense reporting period", _
Title:="Days used")
ActiveCell.Formula = "=((M4-M3-M5) *.4) +(10*" & days & ")"
End If
End If
End With
Finished:
End Sub
[/vba]

[JG4life]

Thank you ever so kindly!!!

That just cut out tonnes of code that I had done because I didn't know how to do that.

[mdmackillop]

Hi Sean,
Welcome to VBAX.
Please note that when you're posting code, inserting line breaks keeps it all on the screen, making it easier to follow.
Regards
MD

[JG4life]

yeah I was meaning to do that as soon as I got it working. I actually stored it in a variable after I got the code working. I am a messy coder LOL Thanks for the tip!!

Sean

[mdmackillop]

It's not a problen so much in the VBE code window, but for users with smaller resolutions, it can be a PIA here.
Glad you found your solution though. I'll mark this solved manually as that function is not working properly just now. (Thread Tools is where you'll find it)