Higher brain power needed!

B

bellmal

New member
Joined
Apr 14, 2018
Messages
2
Reaction score
0
Points
0
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.
 
.
Paste this macro into the Sheet Level module. Right click on the sheet affected, select VIEW CODE, paste this macro :

Code: 
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
 
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!
 
Last edited by a moderator:
.
I am guessing that the 3rd "Case" is to have further items
Click to expand...
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.
 
You must log in or register to reply here.
Back
Top