Pushing data to an empty cell.

3

3beejay3

New member
Joined
Oct 25, 2015
Messages
20
Reaction score
0
Points
0
I want to create a formula, or command to push data, or the results of a formula to an empty cell (with conditions)

Some say it can't be done, but some research has shown it can be done using VBA. see links- http://p2p.wrox.com/excel-vba/37251-excel-putting-result-formula-another-cell.html
http://boards.straightdope.com/sdmb/showthread.php?t=516941

I have zero VBA or coding experience.

View attachment New Expense Report working copy.xlsx

In the above sheet I want to push data in the following way:

When an employee enters mileage in column 'E' I want it to first take the number entered and multiply it by .5. Then take that result and push it to column 'F' and then also multiply that result by .13 and push that result to column 'G'

I need it to do this so that other data can be entered into cells in columns F and G when no data is entered into cells in 'E'.

How would I do this?
 
Use the Worksheet_Change event of the sheet.
See if this works.
Right click the sheet tab, select view code, paste this into the right hand pane that opens.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("E9:E24")) Is Nothing Then
    Application.EnableEvents = False
    Target.Offset(0, 1).Value = Target.Value * 0.5
    Target.Offset(0, 2).Value = Target.Value * 0.5 * 0.13
    Application.EnableEvents = True
End If

If Not Intersect(Target, Range("F9:F24")) Is Nothing Then
    If Target.Offset(0, -1).Value <> "" Then
        With Application
            .EnableEvents = False
            .Undo
            .EnableEvents = True
        End With
        Exit Sub
    Else
        Target.Offset(0, 1).Value = Target.Value * 0.13
    End If
End If

End Sub
 
NoS said:
Use the Worksheet_Change event of the sheet.
See if this works.
Right click the sheet tab, select view code, paste this into the right hand pane that opens.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("E9:E24")) Is Nothing Then
    Application.EnableEvents = False
    Target.Offset(0, 1).Value = Target.Value * 0.5
    Target.Offset(0, 2).Value = Target.Value * 0.5 * 0.13
    Application.EnableEvents = True
End If

If Not Intersect(Target, Range("F9:F24")) Is Nothing Then
    If Target.Offset(0, -1).Value <> "" Then
        With Application
            .EnableEvents = False
            .Undo
            .EnableEvents = True
        End With
        Exit Sub
    Else
        Target.Offset(0, 1).Value = Target.Value * 0.13
    End If
End If

End Sub
Click to expand...

As noted above this works perfectly. I just realized I made a mistake in my request; Instead of multiplying by .13, The second part of the calculation should be divided by 1.13 then the result of that subtracted from the first part and the remainder posted to the cell in column 'G'

ie for a 200 km trip, the calculation would roughly look like this;
200 * .50 = 100
then 100 /1.13 = 88.495575 (~88.50)
then 100 - 88.50 = 11.50 (which gets posted to cell in column 'G')

How would I write that to correct the VBA code?

Thanks in advance
 
the first one
Code: 
Target.Offset(0, 2).Value = (Target.Value * 0.5) - ((Target.Value * 0.5) / 1.13)
the second one
Code: 
Target.Offset(0, 1).Value = Target.Value - (Target.Value / 1.13)
 
NoS said:
the first one
Code: 
Target.Offset(0, 2).Value = (Target.Value * 0.5) - ((Target.Value * 0.5) / 1.13)
the second one
Code: 
Target.Offset(0, 1).Value = Target.Value - (Target.Value / 1.13)
Click to expand...

Something has went wrong....nothing displays in columns 'F' and 'G' when I enter data into cells in column E......

updated sheet with code;
 

Attachments

  • New Expense Report.xlsx
    14.1 KB · Views: 16
Last edited:
No need to quote posts when replying.

I don't think you saved the file as a macro enabled workbook (.xlsm)

A default installation of Excel 2007 and maybe others had macros "disabled without notification"

If that's your case go into Options > Trust Center > Trust Center Settings > Macro Settings
to change setting.
 

Attachments

  • Copy of New Expense Report.xlsm
    20.7 KB · Views: 15
Ok, I'll try that. It was kicking back a dialog box about the macro issue, which I had no idea what to do with...

Thanks
 
I keep getting this result when I test with 200km

200$23.01$11.50 $34.51

It appears to be doubling the HST and installing that into the cell where the 88.50 should go...

Similar results for other values......
 
click on the file I attached to my last post, select open with Microsoft Excel.
It might give some message about file format but just click yes.

Doesn't that do what you asked?
 
3beejay3 said:
Some say it can't be done, but some research has shown it can be done using VBA.
Click to expand...
I apologize if I have not understood your question.
English is not my mother tongue
VBA, which is set up @NoS really brilliant job

I ask you,
Why do you need for VBA?
The problem can be solved with formulas.
The formula in column F
Code: 
=E9*0.5
The formula in column G
Code: 
=(E9*0.5)-((E9*0.5)/1.13)
Again, I apologize if I have not well understood your question.
 

Attachments

  • Copy of New Expense Report.xls
    44.5 KB · Views: 2
navic said:
I apologize if I have not understood your question.
English is not my mother tongue
VBA, which is set up @NoS really brilliant job

I ask you,
Why do you need for VBA?
The problem can be solved with formulas.
The formula in column F
Code: 
=E9*0.5
The formula in column G
Code: 
=(E9*0.5)-((E9*0.5)/1.13)
Again, I apologize if I have not well understood your question.
Click to expand...

The data needs to be pushed to empty cells. I'm not aware that you can do that with the stock formulas, which only work by pulling data......
 
NoS - I somehow overlooked the file you posted. Yes - it works :) Thanks
 
navic said:
I apologize if I have not understood your question.
Click to expand...

I do not think that anyone can fully understand a problem like this without seeing some example data, which for some genuine reason im sure 3beejay3 hasn't done, so don't beat yourself up about that.
That leaves a kind of trial and error, which Nos has negotiated exceptionally well. I didn't understand some aspects of this one either, hence I left it alone :smile:
 
I just became aware of a small issue with the VBA code mileage calculations in Columns E, F and G.
Right now it does a nice job of breaking out the HST etc. I didn't realize that the calculation for 'Expenses without HST' is putting the whole total including HST in column F, not the amount after HST has been subtracted. The mileage data in Column F cells ends up being too high.
Not sure how I missed that before........

Thoughts?

Thanks in advance :hail:
 
3beejay3 said:
I just became aware of a small issue with the VBA code mileage calculations in Columns E, F and G.
Right now it does a nice job of breaking out the HST etc. I didn't realize that the calculation for 'Expenses without HST' is putting the whole total including HST in column F, not the amount after HST has been subtracted. The mileage data in Column F cells ends up being too high.
Not sure how I missed that before........

Thoughts?

Thanks in advance :hail:
Click to expand...

I think I got it fixed. Modded the first calculation in the code to this- Target.Offset(0, 1).Value = (Target.Value * 0.5) / 1.13
It seems to work now.

Thanks.
 
I'll take the blame for that.
As the files you posted were .xlsx files I saved the original one as an .xlsm and did the vba in it all the time.
Copied the vba into the last .xlsx you supplied, saved as .xlsm and posted it back to the forum, not noticing the header change.

Sorry 'bout that.

PS: when you're posting replies to the forum, please don't quote everything. Thanks.
 
Thanks NoS - no worries. You did me a huge favor by writing the code in the first place, as that's way beyond my abilities.
 
NoS - I have one further query; I'm wondering if there's a way that the VBA code could only populate cells in Column G(HST) if there is a mileage entry in column E?

Currently, the code does exactly as supposed to for mileage. However, when a non-mileage entry is placed in column F, it auto-calculates HST as if it were mileage(ie HST uncluded, which ends up being too small of a HST number). For non-mileage items, the HST cell has to be cleared and the proper HST amount entered. It's not a big deal, but for our employees, it would probably be best if the non-mileage HST cells stayed clear and they manually entered that data directly off their receipts. (ie. to prevent mistakes)
 
Have I got this figured right?

You are saying not every line uses the mileage column.
If the employee enters mileage in the mileage column things are OK as is.
If the employee's entry is in column F then..... don't do anything (provided there isn't an entry in the mileage column).

If that's figured right, just put an apostrophe at the beginning of the line preceeding the last two End If.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("E9:E24")) Is Nothing Then
    Application.EnableEvents = False
    Target.Offset(0, 1).Value = (Target.Value * 0.5) / 1.13
    Target.Offset(0, 2).Value = (Target.Value * 0.5) - ((Target.Value * 0.5) / 1.13)
    Application.EnableEvents = True
End If

If Not Intersect(Target, Range("F9:F24")) Is Nothing Then
    If Target.Offset(0, -1).Value <> "" Then
        With Application
            .EnableEvents = False
            .Undo
            .EnableEvents = True
        End With
        Exit Sub
    Else
        'Target.Offset(0, 1).Value = Target.Value - (Target.Value / 1.13)
    End If
End If

End Sub
 
You must log in or register to reply here.
Back
Top