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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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.
 

YoyoZ

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

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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.
 

YoyoZ

New Member
Joined
Aug 30, 2007
Messages
26

ADVERTISEMENT

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!
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
I really appreciate that compliment, and encourage you to keep on asking things. This place is great, huh?
 

YoyoZ

New Member
Joined
Aug 30, 2007
Messages
26

ADVERTISEMENT

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!
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
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.
 

YoyoZ

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

Watch MrExcel Video

Forum statistics

Threads
1,122,689
Messages
5,597,556
Members
414,155
Latest member
Grainne whiteside

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
Top