Macro

[Solved/Closed]
Report
-
 Neo -
Hello,


https://authentification.site/files/22774798/Copy_of_sample.xls

please see the attached to see what i want done.

2 replies

please have a look at the attached file. thanks
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
For limiting the count of V
you can use data validation

select you data range and enter this formula
=COUNTIF($M18:$GK18, "V")<=$J18


I did not see any graph in your sample workbook
there you go, i copied the code for the letter M, but it does not work.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim vPos As Variant
Dim iCol As Integer
Dim CellValue As Variant
Dim iOffsetL2 As Integer
Dim iOffsetL1 As Integer
Dim iOffsetR1 As Integer
Dim iOffset2 As Integer

Dim CellL2 As Variant
Dim CellL1 As Variant
Dim Cell0 As Variant
Dim CellR1 As Variant
Dim CellR2 As Variant

If ((Target.Columns.Count = 1) And (Target.Rows.Count = 1)) Then
If Target = "" Then Exit Sub
End If

vPos = ""

' Exit Sub

On Error GoTo End_Sub

Application.EnableEvents = False

For Each cell In Target
If cell.Column >= 14 And UCase(cell) = "B" Then
MsgBox "It is B"
If cell.Column >= 14 And UCase(cell) = "M" Then
MsgBox "It is M"
End If

Cell0 = UCase(cell.Value)
If Cell0 <> "S" Then GoTo Next_Cell

vPos = ""
iOffsetL2 = 0
iOffsetL1 = 0
iOffsetR1 = 0
iOffsetR2 = 0

iCol = cell.Column

If (IsDate(Cells(17, iCol))) Then

CellL2 = "Garbage Value"
CellL1 = "Garbage Value"
CellR1 = "Garbage Value"
CellR2 = "Garbage Value"

Select Case (Weekday(Cells(17, iCol), vbMonday))

Case Is = 1
iOffsetL2 = -2
iOffsetL1 = -2
iOffsetR1 = 0
iOffsetR2 = 0

Case Is = 2
iOffsetL2 = -2
iOffsetL1 = 0
iOffsetR1 = 0
iOffsetR2 = 0

Case Is = 4
iOffsetL2 = 0
iOffsetL1 = 0
iOffsetR1 = 0
iOffsetR2 = 2

Case Is = 5
iOffsetL2 = 0
iOffsetL1 = 0
iOffsetR1 = 2
iOffsetR2 = 2
End Select
End If

On Error Resume Next
CellL2 = cell.Offset(0, (-2 + iOffsetL2)).Value
CellL1 = cell.Offset(0, (-1 + iOffsetL1)).Value
CellR1 = cell.Offset(0, (1 + iOffsetR1)).Value
CellR2 = cell.Offset(0, (2 + iOffsetR2)).Value
On Error GoTo End_Sub

CellL2 = UCase(CellL2)
CellL1 = UCase(CellL1)
CellR1 = UCase(CellR1)
CellR2 = UCase(CellR2)

If (iCol + iOffsetL2 > 2) Then

' ? ? X
If ((CellL2 = Cell0) And (CellL1 = Cell0)) Then
vPos = -1
GoTo End_Sub
End If

End If


If ((iCol + iOffsetL1 > 0) And ((iCol - iOffsetR1) < Columns.Count)) Then

' ? X ?
If ((CellL1 = Cell0) And (Cell0 = CellR1)) Then
vPos = 0
GoTo End_Sub
End If

End If


If (iCol < Columns.Count - 1) Then

' X ? ?
If ((Cell0 = CellR1) And (Cell0 = CellR2)) Then
vPos = 1
GoTo End_Sub
End If

End If

Next_Cell:

Next

End_Sub:

Application.EnableEvents = True
If (vPos <> "") Then

MsgBox "three in a row"
End If
End Sub
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You have
If cell.Column >= 14 And UCase(cell) = "B" Then
MsgBox "It is B"
If cell.Column >= 14 And UCase(cell) = "M" Then
MsgBox "It is M"
End If


Now if statement is like

IF (conditon) THEN

     ... executes these lines of code if condition is true

END IF




you may also have
IF (conditon) THEN

     ... executes these lines of code if condition is true

ELSE


     ... executes these lines of code if condition is false

END IF



you can also have
you may also have
IF (conditon 1) THEN

     ... executes these lines of code if condition 1 is true

ELSEIF (conditon 2) THEN

     ... executes these lines of code if condition 2 is true and condition one is not true


ELSE

     ... executes these lines of code if condition 1 and condition 2 both are  false

END IF



Now to you issue. you can have either
If cell.Column >= 14 And UCase(cell) = "B" Then
MsgBox "It is B"
END IF

If cell.Column >= 14 And UCase(cell) = "M" Then
MsgBox "It is M"
End If


or you can have

If cell.Column >= 14 And UCase(cell) = "B" Then
MsgBox "It is B"

ELSEIF cell.Column >= 14 And UCase(cell) = "M" Then
MsgBox "It is M"

End If
perfect. Thanks alot Rizvisa. Could I have recorded this macro ? Where abouts are you guys located ?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
No you would not have been able to record this thing.. These are specific programming instructions with a logic. What recorder does is copy what you are doing and translate your action into code. As you can imangine, even though,in you mind you may have used IF statement to make your next move, but computer does not know that. It just know you did some thing and it will record what you did but have idea why you did it.

Well I am in US and have no idea where other guys are at and from. It is a forum. Most, if not all, guys here are not paid for any thing and not even connected to the site in any shape or form. They are just like you and me. They just do it coz they want to do it.
I'm guessing you are a programmer. I think its really cool that you guys offer this service for free. I'm sure I'll be needing your help soon.