Need help with if's in macro

YoyoZ

New Member
Joined
Aug 30, 2007
Messages
26
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

Code:
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
First, use
Select Case ucase(Target.Value)
so if they type in lowercase it won't matter.

Change "Run" to "Call" and remove all double quotes(") from the call statements.

With that it looks like it'll do as you wish.

Note that you seemed to interchangably refer to row 1 and 3 so be careful. If you know about "defining range names" that would be a good idea for the top value in column C.

You do understand that the code builds a "stack" of commands in column C, right? I just didn't know if you copied this code from somewhere and didn't realize it, based on what you described before the code. Also, note that if someone types an A over a P in (e.g.) cell B5, it will just add the Attack command to the stack.
 
Upvote 0
Sweet, thanks heaps, tat worked wonderfully, one more question, how do i make a cell display the newest number e.g
Example 1

(B1) = 2 (B2) = 8 Then (A1) = 8

Example 2

(B1) = 5 (B2) = 10 (B3) = 99 Then (A1) = 99

Thanks heaps!

Also, is it possible to have a if inside a if or a select case inside a select case, e.g

If (Range1) Then
If (Value1) Then
Good
Elseif (Value2) Then
Bad
Elseif (Range2) Then
If (Value1) Then
Yes
Elseif (Value2) Then
No
End Sub

Do you know what i mean? coz i think i can only use one
Private Sub Worksheet_Change(ByVal Target As Range) per page and i need two individual things that happen when different ranges. I have
Code:
Private Sub Worksheet_change(ByVal Target As Range)

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

Exit Sub
      
Else
        Select Case Target.Text
          Case Not A, P, D, R
               Exit Sub
          Case "A"
               Call RAttack
          Case "P"
          If Sheets("Sheet1").Range("B4").Value > 15 Then
               Call RAir_Punch
               Else
          MsgBox "You do not have enough MP"
          End If
          Case "D"
               Call RDefend
          Case "R"
               Call RRun
                     
     End Select
        
End If
End Sub
Already and so i have to have either 2 "if"s or 2 "select case" which i can't seem to be able to do, or else have another
Private Sub Worksheet_change(ByVal Target As Range), Thanks!
 
Upvote 0
As to your Example 1 and 2, would "newest number" be defined as the last nonblank value in the column?

As to the "is it possible" there's nothing wrong with nesting IFs as you describe. The way you lay it out, Select Case would probably not simplify it - but keep in mind that coding economies are available with select Case when you use commas in the cases (maybe helpful if there are Value3, Value4, etc.). On that matter, you can put functions in Case statements too such as
Case Intersect(Range1,Range2)
or
Case Intersect(Range1,Range2) is nothing
but you're still okay using IFs.

Now I'm reading your second code sample which shows you already know about taking advantage of commas - never mind! Here are a few observations there:
- That Case Not... should really be your Case Else. I've never tried putting Case Else before the other cases, so you might move that to the end of the block.
- Yes, you only get one Sub Worksheet_change. But you can have different code for different ranges with an If block for each range.

You could use Select Case in an interesting fashion with
Select case false
Case intersect(1st case) is nothing
...
Case intersect(2nd case) is nothing
...
and nest Select Case within each case. Yet using the construct "Select case false" or "Select case true" is not intrinsically better than IF statements; it's just an alternative style that might be visually easier for some coders to follow.
Already and so i have to have either 2 "if"s or 2 "select case" which i can't seem to be able to do, or else have another
Private Sub Worksheet_change(ByVal Target As Range), Thanks!
No, just nest your processing. It may seem "wrong" to you because you may end up duplicating code between the major segments (i.e., the Range1 segment, Range2 segment) but you can nest several levels deep and handle it all in one place.

One more note: if the code gets much longer, evaluate using "sub subroutines" to keep the vertical height of long IF or CASE blocks visually shorter. That way you can look at the complex set of code tests and follow along much better.
 
Upvote 0
Wow...tat was really really awesome help there, give me points i didn't ask for but were totally useful and in a languge i could totally understand. U were great, if i could rate ya, i'd give ya a 12/10!
 
Upvote 0
Oh, just realised, you didn't answer my question..wel...help me with it anyway....umm...how do i make it show the newest value....like, the last nonblank value, and also, how do i make it If (A1) ="a" then (B1) = "attack" If (A10)="m" then (B10) = "Magic". THanks!
 
Upvote 0
You should have a "lookup table" - perhaps on an unused sheet - with 2 columns
a attack
m magic

and everything else. Highlight all values in both columns and on the menu insert/name/define and type in CmdTranslate and hit enter. (In the future, redo that step if you add to the length of the list.)

Then your code needs at the top of the routine
Dim c as range
and your command is in 2 lines:
Set c = ActiveSheet.Range("A65536").End(xlUp)
c.Offset(0, 1) = Application.WorksheetFunction.VLookup(c.Value, Range("CmdTranslate"), 2, False)
(Actually you could have done all 3 lines in one, but I spared you the 4 line version which is normally used :) )

This will set the translation of the single letter in column B next to the last value in A. Give it a try and let us know.

If you want to set B1 as the translation of A1 even though there are more entries below row 1, then
B1=vlookup(A1,CmdTranslate, 2, False)
B2=vlookup(A2,CmdTranslate, 2, False)
etc.

Holler if you want to know more about what any worksheet function or code does. The above might not make sense to you, but I would try Help in both the spreadsheet and in the code window (2 different help files) and ask here where it's still unclear.
 
Upvote 0
sorry.....lol, thanks but i don't understand tat at all...i'm actually a real newb at this, can u send me the code in (Code) format and mayb explain how it works if u wanna...i have no idea what to do. I just started by myself, like, just trying things out in excel and some ppl have given me codes and i fiddle around with them abit and get some ideas but yeah. sorry
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top