[已解决]求vba代码

xin12389

要求：若
           1、A1单元格的值在1-20之间与之对应的A2则为A
           2、A1单元格的值在21-30之间与之对应的A2则为B
           3、A1单元格的值在31-40之间与之对应的A2则为C
         且   B1=A1
           4、B1=10 则A2=D，A1=25则A2=E
           5、B1的值大于等于41则A2=F
           6、A1的值小于1则A2仅为字符“无效值”
例如:A1=25 则A2=AE，只能用case判断
不好意思漏掉一个条件,这样好理解一点

最佳答案

月排行榜 / 总排行榜

suye1010

2012-6-7 17:58

1. Sub test()
   
2. Select Case Cells(2, 1)
   
3.     Case Is < 1
   
4.         Cells(1, 1) = "无效值"
   
5.     Case Else
   
6.         Cells(1, 1) = WorksheetFunction.Lookup(Cells(2, 1), Array(1, 10, 11, 21, 25, 26, 31, 41), Array("A", "D", "A", "B", "E", "B", "C", "F"))
   
7. End Select
   
8. End Sub

复制代码

跳转到最佳答案楼层

ws278106483

我试试。楼主。呵呵

Select

有矛盾。
条件x：A1单元格的值在1-20之间与之对应的A2则为A
条件y：A1=10 则A2=D


select case做好些。

ws278106483

1. 
   
2. Sub pd()
   
3. If Range("a1").Value > 1 And Range("a1").Value <= 20 And Range("a1").Value = 10 Then
   
4.   Range("a2").Value = "D"
   
5.   ElseIf Range("a1").Value > 20 And Range("a1").Value <= 30 And Range("a1").Value = 25 Then
   
6.   Range("a2").Value = "E"
   
7.   ElseIf Range("a1").Value > 30 And Range("a1").Value <= 40 Then
   
8.    Range("a2").Value = "C"
   
9.   ElseIf Range("a1").Value > 1 And Range("a1").Value <= 20 Then
   
10.    Range("a2").Value = "A"
    
11.    ElseIf Range("a1").Value > 20 And Range("a1").Value <= 30 Then
    
12.     Range("a2").Value = "B"
    
13.   ElseIf Range("a1").Value >= 41 Then
    
14.   Range("a2").Value = "F"
    
15.   Else
    
16.   Range("a2").Value = "无效值"
    
17. end if
    
18. End Sub
    

复制代码

楼主试试，应该可以的。

ws278106483

楼主看一下，对不。记得给个回愎哦。呵呵

表里如意

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. If Target.Address = "$A$1" Then
   
3.   Select Case Target
   
4.   Case 10: [A2] = "D"
   
5.   Case 25: [A2] = "E"
   
6.   Case Is < 1: [A2] = "无效值"
   
7.   Case Is <= 20: [A2] = "A"
   
8.   Case Is <= 30: [A2] = "B"
   
9.   Case Is <= 40: [A2] = "C"
   
10.   Case Else: [A2] = "F"
    
11. End Select
    
12. End If
    
13. End Sub

复制代码

suye1010

1. =LOOKUP(A2,{-100000,1,10,11,21,25,26,31,41},{"无效值","A","D","A","B","E","B","C","F"}))

复制代码

直接在VBA中用个公式来处理不好吗？写Select语句好长哦

suye1010

1. Sub test()
   
2. Select Case Cells(2, 1)
   
3.     Case Is < 1
   
4.         Cells(1, 1) = "无效值"
   
5.     Case Else
   
6.         Cells(1, 1) = WorksheetFunction.Lookup(Cells(2, 1), Array(1, 10, 11, 21, 25, 26, 31, 41), Array("A", "D", "A", "B", "E", "B", "C", "F"))
   
7. End Select
   
8. End Sub

复制代码

xin12389

Select 发表于 2012-6-7 17:24
有矛盾。
条件x：A1单元格的值在1-20之间与之对应的A2则为A
条件y：A1=10 则A2=D

不好意思漏掉一个条件

xin12389

ws278106483 发表于 2012-6-7 17:35
楼主看一下，对不。记得给个回愎哦。呵呵

非常谢谢您的回复