Is there someone can help me on how to get array value ? I need to subtract 2 cell and put the result in the cell also.
Same with multiplication procedure. I did try searching but because I am new in using array I have a hard time to settle the issue in my code urgently.
Kindly see attached file. sheet1 is the preliminary format while sheet2 will be the correct result once code was executed. Below is my code also. Thank you for your help.
Private Sub CommandButton1_Click()
Dim vDB, vR()
Dim i As Long, n As Long, k As Long, j As Integer
Dim r As Integer
Dim s As String
Dim Formula As Integer
vDB = Range("A2", "K" & Range("A" & Rows.Count).End(xlUp).Row)
n = UBound(vDB, 1)
For i = 1 To n
If vDB(i, 6) = "LB" Then
r = 2
Else
r = 1
End If
k = k + r
ReDim Preserve vR(1 To 11, 1 To k)
s = vDB(i, 7)
For j = 1 To 11
If r = 1 Then
vR(j, k) = vDB(i, j)
Else
vR(j, k - 1) = vDB(i, j)
vR(j, k) = vDB(i, j)
End If
Next j
If r = 2 Then
vR(6, k - 1) = "comp"
vR(6, k) = "comp"
vR(8, k) = Split(s, "/")(1) '15
vR(9, k) = Split(s, "X")(1) '150
MsgBox vR(9, k)
vR(11, k) = Empty
End If
Next i
Range("f1") = "Type"
Range("a2").Resize(k, 11) = WorksheetFunction.Transpose(vR)
End Sub
Bookmarks