S
Sal
Currently this macro takes the contents in column K in any combination in any
order, possibly with other words in between, before, or behind them, but as
long as all the words in the line of the code are present and only when all
the words in the line of the code are present a number that is associated to
that line will be entered into the same row in Column I. I am trying to
change the numbers for each line that are going into Column I. I put the
associated number I am aiming to get into Column I next to the line of the
macro that is associated to it. Thank you for your help.
Sub CellFilling()
Dim nRow As Long, iRow As Long
Dim x As Integer, y As Integer, z As Integer, a As Integer
Dim arrVals(3, 4) As Variant, i As Long
arrVals(1, 0) = "Give": arrVals(1, 1) = "Wait": arrVals(1, 2) = "Agree"
(Column I = 05)
arrVals(1, 0) = "Give": arrVals(1, 1) = "Wait": arrVals(1, 2) = "From"
(Column I = 06)
arrVals(0, 0) = "Give": arrVals(0, 1) = "Wait" (Column I = 04)
arrVals(1, 0) = "Give": arrVals(1, 1) = "Wait": arrVals(1, 2) = "Release"
(Column I = 09)
arrVals(0, 0) = "Give": arrVals(0, 1) = "Agree" (Column I = 5)
arrVals(1, 0) = "Give": arrVals(1, 1) = "No": arrVals(1, 2) = "Agree"
(Column I = 10)
arrVals(1, 0) = "Give": arrVals(1, 1) = "From" (Column I = 06)
arrVals(2, 0) = "Give": arrVals(2, 1) = "Gave" (Column I = 08)
arrVals(1, 0) = "Give": arrVals(1, 1) = "Take": arrVals(1, 2) = "No" (Column
I = 10)
arrVals(1, 0) = "Give": arrVals(1, 1) = "Take": arrVals(1, 2) = "From"
(Column I = 06)
arrVals(1, 0) = "Give": arrVals(1, 1) = "Take": arrVals(1, 2) = "No":
arrVals(1, 2) = "Stop" (Column I = 10a)
arrVals(2, 2) = "Done": arrVals(2, 3) = "Call" (Column I = 5a)
arrVals(2, 2) = "Give": arrVals(2, 3) = "Call" (Column I = 5a)
arrVals(2, 2) = "Allot" (Column I = 11)
arrVals(2, 2) = "Allot": arrVals(2, 3) = "From" (Column I = 12)
arrVals(2, 2) = "Allot": arrVals(2, 3) = "Agree" (Column I = 12a)
arrVals(0, 0) = "Trade" (Column I = 13)
arrVals(2, 2) = "Trade": arrVals(2, 3) = "Agree" (Column I = 13a)
arrVals(2, 2) = "Discard" (Column I = 14)
arrVals(2, 2) = "Final" (Column I = 15)
arrVals(1, 0) = "Final": arrVals(1, 1) = "Agree" (Column I = 15a)
nRow = ActiveSheet.Cells(Rows.Count, "K").End(xlUp).Row
For iRow = 1 To nRow
With Cells(iRow, "K")
For a = 0 To 2
For z = 0 To 4
If arrVals(a, z) = "" Then Exit For
x = InStr(1, .Value, arrVals(a, z))
If x > 0 Then y = y + 1
Next z
If y = z Then Cells(iRow, "I") = "0" & z
y = 0
x = 0
Next a
End With
Next iRow
End Sub
order, possibly with other words in between, before, or behind them, but as
long as all the words in the line of the code are present and only when all
the words in the line of the code are present a number that is associated to
that line will be entered into the same row in Column I. I am trying to
change the numbers for each line that are going into Column I. I put the
associated number I am aiming to get into Column I next to the line of the
macro that is associated to it. Thank you for your help.
Sub CellFilling()
Dim nRow As Long, iRow As Long
Dim x As Integer, y As Integer, z As Integer, a As Integer
Dim arrVals(3, 4) As Variant, i As Long
arrVals(1, 0) = "Give": arrVals(1, 1) = "Wait": arrVals(1, 2) = "Agree"
(Column I = 05)
arrVals(1, 0) = "Give": arrVals(1, 1) = "Wait": arrVals(1, 2) = "From"
(Column I = 06)
arrVals(0, 0) = "Give": arrVals(0, 1) = "Wait" (Column I = 04)
arrVals(1, 0) = "Give": arrVals(1, 1) = "Wait": arrVals(1, 2) = "Release"
(Column I = 09)
arrVals(0, 0) = "Give": arrVals(0, 1) = "Agree" (Column I = 5)
arrVals(1, 0) = "Give": arrVals(1, 1) = "No": arrVals(1, 2) = "Agree"
(Column I = 10)
arrVals(1, 0) = "Give": arrVals(1, 1) = "From" (Column I = 06)
arrVals(2, 0) = "Give": arrVals(2, 1) = "Gave" (Column I = 08)
arrVals(1, 0) = "Give": arrVals(1, 1) = "Take": arrVals(1, 2) = "No" (Column
I = 10)
arrVals(1, 0) = "Give": arrVals(1, 1) = "Take": arrVals(1, 2) = "From"
(Column I = 06)
arrVals(1, 0) = "Give": arrVals(1, 1) = "Take": arrVals(1, 2) = "No":
arrVals(1, 2) = "Stop" (Column I = 10a)
arrVals(2, 2) = "Done": arrVals(2, 3) = "Call" (Column I = 5a)
arrVals(2, 2) = "Give": arrVals(2, 3) = "Call" (Column I = 5a)
arrVals(2, 2) = "Allot" (Column I = 11)
arrVals(2, 2) = "Allot": arrVals(2, 3) = "From" (Column I = 12)
arrVals(2, 2) = "Allot": arrVals(2, 3) = "Agree" (Column I = 12a)
arrVals(0, 0) = "Trade" (Column I = 13)
arrVals(2, 2) = "Trade": arrVals(2, 3) = "Agree" (Column I = 13a)
arrVals(2, 2) = "Discard" (Column I = 14)
arrVals(2, 2) = "Final" (Column I = 15)
arrVals(1, 0) = "Final": arrVals(1, 1) = "Agree" (Column I = 15a)
nRow = ActiveSheet.Cells(Rows.Count, "K").End(xlUp).Row
For iRow = 1 To nRow
With Cells(iRow, "K")
For a = 0 To 2
For z = 0 To 4
If arrVals(a, z) = "" Then Exit For
x = InStr(1, .Value, arrVals(a, z))
If x > 0 Then y = y + 1
Next z
If y = z Then Cells(iRow, "I") = "0" & z
y = 0
x = 0
Next a
End With
Next iRow
End Sub