JG4life
03-31-2006, 08:43 AM
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
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