Hello guys/ladies
I have this table:
<tbody>
</tbody>
and this code
what it does:
For each JOB (dunno how to merge tables in here...sorry) it looks for V S or N.
then it will return biggest value (just pretend V>S>N)
but it return only 1 of these values...it means, if you have Item B / JOB A combination, then V = 1 S = 0 N = 0
for Item B / Job B result is V =0 S = 1 N = 0
for item B / Job C result is V = 0 S = 1 N = 0
together for item B result is V = 1, S = 2, N = 0
Etc.
this macro i wrote works perfectly
Currently is in Worksheet_SelectionChange mode and I want to change it into Worksheet_Change, but problem is, there are other macros in this workbook and with Worksheet_Change loop (or calculation) is endless
Do you think there is a way how to update this macro to be more...sexy? maybe then Worksheet_Change will work faster.
....probably less loops, but there is nothing in my mind how to update it...
I have this table:
Job A | Job A | Job A | Job B | Job B | Job B | Job C | Job C | Job C | |||||
# of V | # of S | # of N | |||||||||||
<tbody> </tbody> | 3 | 0 | 0 | V | S | S | V | V | N | - | S | V | |
<tbody> </tbody> | 1 | 2 | 0 | N | - | V | S | S | - | N | S | - | |
<tbody> </tbody> | 1 | 1 | 1 | S | S | S | N | - | - | S | V | N |
<tbody>
</tbody>
and this code
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Dim ws As Worksheet
Set ws = ActiveSheet
Last = ws.Cells(Rows.Count, 1).End(xlUp).row
LC = ws.Cells(1, Columns.Count).End(xlToLeft).column
For rowX = 4 To Last Step 1
V = 0
S = 0
N = 0
XX = 0
For columnX = 6 To LC Step 3
For XX = 0 To 2
If Cells(rowX , columnX + XX).Value = "V" Or Cells(rowX , columnX + XX).Value = "v" Then
V = V + 1
GoTo NextV
Else
End If
Next XX
For XX = 0 To 2
If Cells(rowX , columnX + XX).Value = "S" Or Cells(rowX , columnX + XX).Value = "s" Then
S = S + 1
GoTo NextV
Else
End If
Next XX
For XX = 0 To 2
If Cells(rowX , columnX + XX).Value = "N" Or Cells(rowX , columnX + XX).Value = "n" Then
N = N + 1
GoTo NextV
Else
End If
Next XX
NextV:
Next columnX
Cells(rowX , "C").Value = V
Cells(rowX , "D").Value = S
Cells(rowX , "E").Value = N
Next rowX
Application.ScreenUpdating = True
End Sub
what it does:
For each JOB (dunno how to merge tables in here...sorry) it looks for V S or N.
then it will return biggest value (just pretend V>S>N)
but it return only 1 of these values...it means, if you have Item B / JOB A combination, then V = 1 S = 0 N = 0
for Item B / Job B result is V =0 S = 1 N = 0
for item B / Job C result is V = 0 S = 1 N = 0
together for item B result is V = 1, S = 2, N = 0
Etc.
this macro i wrote works perfectly
Currently is in Worksheet_SelectionChange mode and I want to change it into Worksheet_Change, but problem is, there are other macros in this workbook and with Worksheet_Change loop (or calculation) is endless
Do you think there is a way how to update this macro to be more...sexy? maybe then Worksheet_Change will work faster.
....probably less loops, but there is nothing in my mind how to update it...