| |
VBA初挑戦です。。。
以下の数式をについて、あまりにも数式が長いので、
VBAで出来ないものかな？と思い、挑戦してみることにしました。
＜数式＞
===============================================================
A2
=IF(COUNTIF(B2:E2,"ENG")=(COLUMNS(B2:E2)),"ENG",
IF(COUNTIF(B2:E2,"FRA")+COUNTIF(B2:E2,"ENG")=(COLUMNS(B2:E2)),"FRA",
IF(COUNTIF(B2:E2,"ITA")+COUNTIF(B2:E2,"ENG")=(COLUMNS(B2:E2)),"ITA",
IF(COUNTIF(B2:E2,"GER"),"GER",IF(COUNTIF(B2:E2,"ESP"),"ESP",
IF(COUNTA(B2:E2)=(COLUMNS(B2:E2)),"JPN",IF(COUNTA(B2:E2),"ITA","FRA")))))))
===============================================================
＜コード＞
================================================
Sub judgement()
Dim a As Integer '"ENG"の数を数えていく
Dim b As Integer '"FRA"の数を数えていく
Dim c As Integer '"ITA"の数を数えていく
Dim d As Integer '"GER"の数を数えていく
Dim e As Integer '"ESP"の数を数えていく
Dim f As Integer '"JPN"の数を数えていく
Dim i As Integer '"列"の数を数えていく
For i = 2 To 5
Cells(2, i).Select
Select Case Cells(2, i).Value
Case Is = "ENG"
a = a + 1
Case Is = "FRA"
b = b + 1
Case Is = "ITA"
c = c + 1
Case Is = "GER"
d = d + 1
Case Is = "ESP"
e = e + 1
Case Is <> "FRA"
f = f + 1
End Select
Next i
'B2〜E2："ENG"=4のとき、A2="ENG"
If a = 4 Then
Range("A2").Value = "ENG"
'B2〜E2："FRA"+"ENG"=4のとき、A2="FRA"
ElseIf b + a = 4 Then
Range("A2").Value = "FRA"
'B2〜E2："ITA"+"ENG"=4のとき、A2="ITA"
ElseIf c + a = 4 Then
Range("A2").Value = "ITA"
'B2〜E2："GER">=1のとき、A2="GER"
ElseIf d >= 1 Then
Range("A2").Value = "GER"
'B2〜E2:"ESP">=1のとき、A2="ESP"
ElseIf e >= 1 Then
Range("A2").Value = "ESP"
'B2〜E2:"JPN"=4のとき、A2="JPN"
ElseIf f = 4 Then
Range("A2").Value = "JPN"
'B2〜E2:"JPN">=1のとき、A2="ITA"
ElseIf f >= 1 Then
Range("A2").Value = "ITA"
'B2〜E2:上記条件以外のとき、A2="FRA"
Else
Range("A2").Value = "FRA"
End If
End Sub
================================================
ですが、
B2："-"
N2："JPN"
O2："JPN"
E2："JPN"
と入力した結果、A2="JPN"にならずに、A2="ITA"になってしまいます。
どこが間違っているのか、教えていただけますでしょうか。
|
|
はじめて娘
- 05/8/2(火) 17:40 -
