Need help with if's in macro

[YoyoZ]

What i need is if "a" is placed in A1, then "Attack" is placed in B1, and if 'a' is place in A2, then "Attack" is placed in B2, and If 'p' is placed in A3, then "magic" in B3 and so on, Here what i have

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B3:B30")) Is Nothing Then

Exit Sub
     
Else
        Select Case Target.Value
          Case "A"
               Run "RAttack"
          Case "P"
               Run "RMagic"
          Case "D"
               Run "RDefend"
          Case "R"
               Run "RmyRun"
     End Select
End If
End Sub


Sub RAttack()
If IsEmpty(Sheets("Sheet3").Range("C3")) Then
     Sheets("Sheet3").Range("C3").Value = "Attack"
Else
     Sheets("Sheet3").Range("C" & Rows.Count).End(xlUp).Offset(1).Value = "Attack"
End If
End Sub


Sub RMagic()
If IsEmpty(Sheets("Sheet3").Range("C3")) Then
     Sheets("Sheet3").Range("C3").Value = "Magic"
Else
     Sheets("Sheet3").Range("C" & Rows.Count).End(xlUp).Offset(1).Value = "Magic"
End If
End Sub


Sub RDefend()
If IsEmpty(Sheets("Sheet3").Range("C3")) Then
     Sheets("Sheet3").Range("C3").Value = "Defend"
Else
     Sheets("Sheet3").Range("C" & Rows.Count).End(xlUp).Offset(1).Value = "Defend"
End If
End Sub


Sub RmyRun()
If IsEmpty(Sheets("Sheet3").Range("C3")) Then
     Sheets("Sheet3").Range("C3").Value = "Run"
Else
     Sheets("Sheet3").Range("C" & Rows.Count).End(xlUp).Offset(1).Value = "Run"
End If
End Sub

 

[YoyoZ]

man..sorry..i am so unclear.....okok..what i meant was.....
IF A1 has been changed, then F1.Value = G1.Value but spell= fireball and spell2 = firekick. This means that G1 could be fireball or firekick (and to be exact, could be "attack" too). So what i need is like, not just if G1 = Spell then fireball, but if G1= Spell then fireball, if G1 = Spell2 is then fire kick and i need it from A1-A10. Do you know what i mean?

*=Change
A1 * then F1=Fireball because G1=Spell
A2 * then F2=Attack because G2=Attack
A3 * then F3=Attack because G3=Attack
etc...

and the G column has been randomed at the opening of the workbook so the G column is always different. SO SO SOsorry for all the confusion!...i didn't realise it my explainations coudl be taken so many different ways!!..also, i'm stuck on how to make a cell = a formula with macro, example

Sub AAttack()
If IsEmpty(Sheets("Moves").Range("D3")) Then
     Sheets("Moves").Range("D3").Value = 15 + (Str_Aang * 4) + (Agi_Aang / 3) * 99 / 10
Else
     Sheets("Moves").Range("D" & Rows.Count).End(xlUp).Offset(1).Value = 15 + (Str_Aang * 4) + (Agi_Aang / 3) * 99 / 10
End If
End Sub

Thanks heaps!!!

 

[jindon]

man..sorry..i am so unclear.....okok..what i meant was.....
IF A1 has been changed, then F1.Value = G1.Value but spell= fireball and spell2 = firekick. This means that G1 could be fireball or firekick (and to be exact, could be "attack" too). So what i need is like, not just if G1 = Spell then fireball, but if G1= Spell then fireball, if G1 = Spell2 is then fire kick and i need it from A1-A10. Do you know what i mean?

*=Change
A1 * then F1=Fireball because G1=Spell2
A2 * then F2=Attack because G2=Attack
A3 * then F3=Attack because G3=Attack
etc...

Why???

 

[YoyoZ]

sorry....edited it

 

[jindon]

try

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("a1:a10")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target
     Offset(,5).Value = IIf(.Offset(,6).Value = "sepll","FireBall",IIf(.Offset(,6).Value = "spell2","FireKick",.Offset(,6).Value))
End With
Application.EnableEvents = True
End Sub

 

[YoyoZ]

um...na....sub or function not idenified....and could u also tell me how to point in a formula into a macro

 

[jindon]

Missed a "." period

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("a1:a10")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target
     .Offset(,5).Value = IIf(.Offset(,6).Value = "sepll","FireBall",IIf(.Offset(,6).Value = "spell2","FireKick",.Offset(,6).Value))
End With
Application.EnableEvents = True
End Sub

 

[YoyoZ]

sorry don't really get it, where does it say F column or G column?.... can u just highlight it..

 

[jindon]

With Target
     .Offset(,5).Value = IIf(.Offset(,6).Value = "sepll","FireBall",IIf(.Offset(,6).Value = "spell2","FireKick",.Offset(,6).Value))
     ' F.Value = If G.Value = "spell" then "FireBall", ElseIf G.Value ="spell2" then "FireKick" else G.Value
End With
End Sub

Other form

With Target
     If .Offset(,6).Value = "spell" Then
          .Offset(,5).Value = "FireBall"
     ElseIf .Offset(,6).Value = "spell2" Then
          .Offset(,5).Value = "FireKick"
     Else
          .Offset(,5).Value = .Offset(,6).Value
     End If
End With

 

[YoyoZ]

that works but......its not completely what i need. i ddin't realise that i had to have them all in the same sub. what u have is fine for the first enemy but when the second enemy comes along, this spell and spell2 is different...what i will have is private sub workbook_change which will be activated if ("Moves"Sheet)A1 is changed. then wen this activates it runs a macro called "enemy One" if Cell ("Play"Sheet)A1="enemy one" and run a macro called "enemy two" if cell ("Play"Sheet)A1="enemy two". An in this macro, "enemy one" or "enemy two", it will trigger the F1 or F2, or which ever F is the current one which can be choosen by

 If IsEmpty(Sheets("Moves").Range("F3")) Then
     Sheets("Moves").Range("F3").Value = Sheets("Multipliers").Range("E3")
Else
     Sheets("Moves").Range("F" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Multipliers").Range("E" & Rows.Count).End(xlUp)p

to equal the current ("Multipliers"Sheet)E (this was the G column we used last time) with the If "Spell" = Fireball and so on. Man.....thank you so much!.....i know i'm a hussle!....if u want..i can send you a copy of this game....:D...its not totaly done but u can just see what u are contributing too

 

[jindon]

that works but......its not completely what i need. i ddin't realise that i had to have them all in the same sub. what u have is fine for the first enemy but when the second enemy comes along, this spell and spell2 is different...what i will have is private sub workbook_change which will be activated if ("Moves"Sheet)A1 is changed. then wen this activates it runs a macro called "enemy One" if Cell ("Play"Sheet)A1="enemy one" and run a macro called "enemy two" if cell ("Play"Sheet)A1="enemy two". An in this macro, "enemy one" or "enemy two", it will trigger the F1 or F2, or which ever F is the current one which can be choosen by

 If IsEmpty(Sheets("Moves").Range("F3")) Then
     Sheets("Moves").Range("F3").Value = Sheets("Multipliers").Range("E3")
Else
     Sheets("Moves").Range("F" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Multipliers").Range("E" & Rows.Count).End(xlUp)p

to equal the current ("Multipliers"Sheet)E (this was the G column we used last time) with the If "Spell" = Fireball and so on. Man.....thank you so much!.....i know i'm a hussle!....if u want..i can send you a copy of this game....:D...its not totaly done but u can just see what u are contributing too

ToyoZ

Explain what you are trying to do with
1) Clear logic
2) in the undstandable form to anybody

So that you may get some help from someone else.