View Full Version : Higher brain power needed!

2018-04-16, 08:22 PM
After hours searching the internet trying to find an answer without any joy I have resorted to the forums with the hope of engaging someone with higher brain power than I have!

I am hoping to find a way of entering data into Excel that I would have thought quite simple but the method appears elusive.

I need to enter expenses into a spread sheet and so I would like to automate the process to a certain degree.

If I type into column B 'Salary' I would like that to automatically populate column C and H with 157 on the same row. But then if I was to type 'Mileage' into another row in column B then this would automatically populate columns C and F with 63 on the same row. I realise that I need a reference sheet with the different items 'Salary' and 'Mileage' and the amounts associated with them but would this be a VLOOKUP or a different function to auto enter the amounts into the various columns? Although I only mention 2 items there are 5 or 6.

Any help would be greatly appreciated.

2018-04-16, 09:23 PM
Yes, VLOOKUP would be a suitable function for this purpose.


2018-04-16, 10:25 PM
Paste this macro into the Sheet Level module. Right click on the sheet affected, select VIEW CODE, paste this macro :

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastRow As Long
Dim i As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
Select Case Range("B" & i)
Case "Salary"
Range("C" & i).Value = "157"
Range("H" & i).Value = "157"
Case "Mileage"
Range("C" & i).Value = "63"
Range("F" & i).Value = "63"
'Case ""
'Range("?" & i) = "?"
End Select
Next i
End Sub

2018-04-17, 03:11 PM
Thanks Logit,

This looks promising but I need to have my hand held a little more to get this up and running, my Excel experience is rather limited.

I am guessing that the 3rd "Case" is to have further items and there values added. If they are left 'as is' would this stop the macro from running as I'm getting a compile error 'invalid inside procedure'?

Should this be added to the reference sheet or the sheet I need to add the expenses to?

Sorry if I am coming across a bit dumb and that you don't mind doing a bit more (maybe a lot more) explaining but I will be learning!

2018-04-21, 04:30 PM

I am guessing that the 3rd "Case" is to have further items YES

Those lines are commented out with the symbol ' . Excel ignores anything after the ' . You can delete those two lines from the macro and that would
do the same thing as the ' symbol.

The macro should be added to the sheet where SALARY and MILEAGE are entered. Right click that sheet's tab, select VIEW CODE and then paste the
macro into the large white "window" on the right.