Hi
@roxorvoxor. Thanks for posting on MrExcel.
I show you two options, one automatic and one with a button.
With macros, help cells are not necessary.
The macro can read the data directly from the cells: name "BS4", month "CE4" and activity "CJ4".
This assumes that in cell CE4 (month) you are literally capturing the letters "Jan" for January, "Feb" for February, "Mar" for March, etc.
Option 1 Automatic.
Works automatically when you change the value of any of the 3 cells mentioned above.
Put the following code in the sheet events.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("BS4:CX5")) Is Nothing Then
Dim sName As String, sMont As String, sActi As String
Dim n As Long, col As Long
Dim dt As Date
Dim f As Range
sName = Range("BS4").Value
sMont = Range("CE4").Text
sActi = Range("CJ4").Value
If sName <> "" And sMont <> "" And sActi <> "" Then
dt = "01/" & sMont & "/" & Year(Date)
n = Month(dt) - 1
Set f = Range("11:11").Find(sActi, , xlValues, xlWhole, , , False)
If Not f Is Nothing Then
col = f.Column + n
Set f = Range("A:A").Find(sName, , xlValues, xlWhole, , , False)
If Not f Is Nothing Then
With Cells(f.Row, col)
.Select
.Value = "x"
End With
End If
End If
End If
End If
End Sub
Note Sheet Event:
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.
Option 2 With A Button.
Works when you press a button.
Put the following code in a module.
VBA Code:
Sub Enter_value_at_intersection()
Dim sName As String, sMont As String, sActi As String
Dim n As Long, col As Long
Dim dt As Date
Dim f As Range
sName = Range("BS4").Value
sMont = Range("CE4").Text
sActi = Range("CJ4").Value
If sName <> "" And sMont <> "" And sActi <> "" Then
dt = "01/" & sMont & "/" & Year(Date)
n = Month(dt) - 1
Set f = Range("11:11").Find(sActi, , xlValues, xlWhole, , , False)
If Not f Is Nothing Then
col = f.Column + n
Set f = Range("A:A").Find(sName, , xlValues, xlWhole, , , False)
If Not f Is Nothing Then
With Cells(f.Row, col)
.Select
.Value = "x"
End With
End If
End If
End If
End Sub
Note Insert A Module:
Press Alt-F11 to open the VBA editor. From the menu select Insert > Module. On the panel that opens, paste the code previous.
Create a shape in your sheet and assign the macro "Enter_value_at_intersection".
--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------
