creating a formula using Activecell and Offset

[JohnZ1156]

I would like VBA to enter a formula into a cell based on the Activecell and offset.

For example:
When I enter a value in cell B10, I would like a value placed in C10 from =B10-B9.
But if I move to row 11, I would need the value in C11 to be =B11-B10, etc.


So as soon as I enter a value into the Activecell, VBA should enter the formula and calculate it to a number.

 

[Joe4]

Do you only want this to happen when you place a value in column B, and no other column?
Is row 10 the row where you want this behavior to start, or should it start at an earlier row?

 

[Joe4]

If my assumptions are correct, right-click on the sheet tab name at the bottom of the screen, select "View Code", and enter this VBA code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells entered at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   Only run if value entered in column B at row 10 or below
    If (Target.Column = 2) And (Target.Row >= 10) Then
'       Update column C with value
        Application.EnableEvents = False
        Target.Offset(0, 1).Value = Target.Value - Target.Offset(-1, 0).Value
        Application.EnableEvents = True
    End If
    
End Sub

Then, as you manually values in column B at row 10 or below, it will automatically place your desired value in column C.

 

[JohnZ1156]

Let me be more specific:
I have a Automobile mileage sheet with a macro to enter Date, odometer reading, trip, price per gallon, gallons, etc.
After I enter the odometer value, I would like it to calculate the trip value based on the current odometer value minus the last odometer value.
But the actual trip value contains 1 decimal place. So after it does the calculation, which will be in a whole number, then I'd like to manually enter the acctial value with thedecimal place.

Here is my basic macro.
Don't laugh, it was one of my first attempts in writing a macro.

Option Explicit

Sub EnterData()

    Dim Fill As Date
    Dim Odometer As Double
    Dim Trip As Double
    Dim Price As Double
    Dim Gallons As Double
    Dim CMPG As Double
    Dim Octane As Double
    Dim Light As String
        
    Worksheets("Mileage").Select
    Range("A1").Select
    Range("A11").End(xlDown).Select
    
    Range("A1048576").End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
    
'Enter Date of Purchase
    Selection.ClearContents
    Fill = InputBox("Please enter the Date.", "Fill Date", Default, XPos:=2880, YPos:=5760)
    ActiveCell.Value = ActiveCell.Value & Fill
    ActiveCell.Offset(0, 1).Select

'Enter Odometer reading at time of purchase
    Selection.ClearContents
    Odometer = InputBox("Please enter the Odometer.", "Odometer Reading", Default, XPos:=2880, YPos:=5760)
    ActiveCell.Value = ActiveCell.Value & Odometer
    ActiveCell.Offset(0, 1).Select
    
[B]' Here's where I'd like it to calculate the trip value, but still allow me to enter the actual trip value before moving on[/B]

'Enter Trip
    Selection.ClearContents
    Trip = InputBox("Please enter the Trip Distance.", "Trip Distance", Default, XPos:=2880, YPos:=5760)
    ActiveCell.Value = ActiveCell.Value & Trip
    ActiveCell.Offset(0, 1).Select
    
'Enter Price per Gallon
    Selection.ClearContents
    Price = InputBox("Please enter the Price per Gallon.", "Price per Gallon", Default, XPos:=2880, YPos:=5760)
    ActiveCell.Value = ActiveCell.Value & Price
    ActiveCell.Offset(0, 1).Select

'Enter Gallons Purchased
    Selection.ClearContents
    Gallons = InputBox("Please enter the Gallons.", "Gallons Purchased", Default, XPos:=2880, YPos:=5760)
    ActiveCell.Value = ActiveCell.Value & Gallons
    ActiveCell.Offset(0, 3).Select
    
'Enter CMPG (Car MPG)
    Selection.ClearContents
    CMPG = InputBox("Please enter the Cars MPG.", "Cars MPG", Default, XPos:=2880, YPos:=5760)
    ActiveCell.Value = ActiveCell.Value & CMPG
    ActiveCell.Offset(0, 2).Select
    
 'Enter Octane
    Selection.ClearContents
    Octane = InputBox("Please enter the Octane.", "Octane", Default, XPos:=2880, YPos:=5760)
    ActiveCell.Value = ActiveCell.Value & Octane
    ActiveCell.Offset(0, 1).Select
   
 'Enter Light
    Selection.ClearContents
    Light = InputBox("Please enter <Yes> for Light.", "Light", Default, XPos:=2880, YPos:=5760)
'    ActiveCell.Value = ActiveCell.Value & Light
    ActiveCell.Offset(0, 1).Select
  
End Sub

 

[Joe4]

You should really be forthright with your complete problem and code that you may be using in your original post. You don't want to ask an oversimplified question, only to add the complexity in in a follow-up post, so that people know what they are getting into and understand the real problem before they reply.

That being said, did you at least try my code? I think it will do what you want.
If it does not, please show some sample data and your expected output.

 

[JohnZ1156]

Hi Joe,

Thank you for your help. Yes, your code worked fine, on its own, but then I tried to incorporate it into my existing macro and I got a debug error.
I think it's because I'm using Input boxes to enter the data. I'll try to figure it out on my own.

Thanks again. John

 

[Joe4]

Hi Joe,

Thank you for your help. Yes, your code worked fine, on its own, but then I tried to incorporate it into my existing macro and I got a debug error.
I think it's because I'm using Input boxes to enter the data. I'll try to figure it out on my own.

Thanks again. John

OK, if you cannot figure it out, hit the "Debug" error to see which line of code is causing the issues, and post that section of the code here.

Note: You should also avoid reserved words (words already using by Excel/VBA for functions, methods, properties, objects, etc), and that may cause errors and/or unexpected results.
"Fill" is one such reserved word.