|
配列というのは、あくまで順番に並んでいないとダメなのです。だから
>.Range("A2:E2, G2").Value = Ary
というように、F2 を飛ばして G2 に入れることはできません。
面倒でも以下のように、各ComboBoxの値を変数に入れ「配列として使えるところだけ」
配列を作って使うようにするしかありません。
Private Sub CommandButton2_Click()
Dim MxR As Long
Dim Ary As Variant, CkR As Variant
Dim CkSt As String, Tx1 As String
Dim Tx2 As String, Tx3 As String
Dim Tx4 As String, Tx5 As String
Dim Tx6 As String, Tx13 As String
Tx1 = ComboBox1.Text: Tx2 = ComboBox2.Text
Tx3 = ComboBox3.Text: Tx4 = ComboBox4.Text
Tx5 = ComboBox5.Text: Tx6 = ComboBox6.Text
Tx13 = ComboBox13.Text
Ary = Array(Tx1, Tx2, Tx3, Tx4, Tx5)
With Worksheets("data")
MxR = .Range("A65536").End(xlUp).Row
If MxR = 1 Then
.Range("A2:E2").Value = Ary
.Range("G2").Value = Tx13
.Range("F2").Value = Val(Tx6)
Else
If IsEmpty(.Cells(MxR, 27).Value) Then
.Range(.Cells(2, 27), .Cells(MxR, 27)).Formula = _
"=CONCATENATE($A2,$B2,$C2,$D2,$E2,$G2)"
End If
CkSt = Join(Ary, "") & Tx13
CkR = Application.Match(CkSt, .Columns(27), 0)
If IsError(CkR) Then
.Range(.Cells(MxR + 1, 1), .Cells(MxR + 1, 5)) _
.Value = Ary
.Cells(MxR + 1, 6).Value = Val(Tx6)
.Cells(MxR + 1, 7).Value = Tx13
Else
.Cells(CkR, 6).Value = _
.Cells(CkR, 6).Value + Val(Tx6)
End If
End If
End With
Worksheets("menu").Activate
MsgBox "入力完了", 64
End Sub
|
|