what scenario do i use

[happyman123uk]

two questions in one here.

when i input data into c4 has it has one of 4 answers in it depending on that answer I want to add something to another cell

example if c4 says "9 won" then i want to add 3 to d13
if c4 says "18 won" then i want it to add 5 to d13

but if c4 says "lost 9" i want it to add 1 to a certain cell
and c4 says "lost 18" i want it to add 1 to a certain cell

the second question is if a certain cell says a name i want it to add 1 to two cell but there are 40 names within the list so its has 40 different cells it could add 1 to.
example

if d4 says "Gary" then add 1 to f4 and f5
if d4 says "peter" then add 1 to h4 and h5
and so on for 40 different names

cheers

 

[Osvaldo Palmeiro]

Hi.
For your first question you could use the code bellow, and for the second question, you could write a similar one.

Put the code in the sheet module, like this:
1. copy the code bellow
2. activate the interest sheet
3. right click on it's tab and choose 'View Code'
4. paste the code into the blank window that will open
5. done! Alt+Q to get back to the sheet and test it

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address <> "$C$4" Then Exit Sub
  Select Case [C4].Value
   Case "9 won"
    [D13] = [D13].Value + 3
   Case "18 won"
    [D13] = [D13].Value + 5
   Case "lost 9"
    'certain cell1=certain cell1.value+1 replace "certain cell" to suit
   Case "lost 18"
    'certain cell2=certain cell2.value+1 replace "certain cell" to suit
  End Select
End Sub

 

[happyman123uk]

Forgot to say the target address is on another sheet but in same workbook ie target address is sheet 2 (Called names1)
tagert cell is on sheet 1(called Table)

 

[Osvaldo Palmeiro]

You said 'target address' and also 'target cell', so I'm afraid if I correctly understood what you mean, so let's try the way bellow.
Install the code into sheet 'names1' module, then it will act on sheet 'Table'.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address <> "$C$4" Then Exit Sub
 Dim wsTable As Worksheet
 Set wsTable = ThisWorkbook.Sheets("Table")
  Select Case [C4].Value
   Case "9 won"
    wsTable.[D13] = wsTable.[D13].Value + 3
   Case "18 won"
    wsTable.[D13] = wsTable.[D13].Value + 5
   Case "lost 9"
    'wsTable.certain cell1=wsTable.certain cell1.value+1 replace "certain cell" to suit
   Case "lost 18"
    'wsTable.certain cell2=wsTable.certain cell2.value+1 replace "certain cell" to suit
  End Select
End Sub

 

[happyman123uk]

Target address is that where the data is inputted or where i want the answer to be ?

Sorry i am very new to this

line 2 of your answer

 

[Osvaldo Palmeiro]

In the code above 'Target.Address' is that where the data will be inputted. In your case, is the cell 'C4' of the sheet called "names1", in which module the code must be pasted.

 

[happyman123uk]

Sorry still dont get it i have uploaded my sheet too this address if you would care to download it and maybe you will see what i am trying to do better

http://myexcelproblem.moonfruit.com/#/home/4562330140


I want to be able to understand what the code is doing and how it's doing it bit by bit

I will send you my email address in a private message maybe then you could post the sheet back to me with the code in so i can see how and what it does

cheers