Deke
Reputation: 495
Excel Finding the Row Number based on Date
I'm trying to figure out how to get the Row number based on a date.
I've built a calendar in Excel on one sheet while using another sheet as a table where A1:NB1 are dates.
Example:
I am using the following formula to give me the column number associated with the day I have cells selected =IFERROR(MATCH(M3,INDIRECT("'"&ScYear&"'!A1:NB1",TRUE),0),"")
Example:
I'd like a way to identify the row number selected on the calendar with the first available row in the tables date column.
For example if I select the first cell on today's date in the calendar it reports row 1 since that's the first blank cell. If I were to select the second cell down under today's date it would select the second row all they way to the 9th row.
Any help would be greatly appreciated!
Upvotes: 0
Views: 696
Answers (1)
Deke
Reputation: 495
In VBA I used this long and in-elegant solution to get the row I wanted based on target.value.
By "Manually" having it identify the the selected target.address I can then have it identify the selected row and write that value to Range("M43") I then use code like the following Sheets("" & DbSht & "").Cells(DbRow + 1, DbCol).Value = Target.Value identify the column and row needed to be updated. (DbRow = the value in M43 and DbCol = the value in M42 which is the column number and row number).
There may be an easier way with index or Match through a module but this solution works for now. Hopefully this makes more sense than my original question.
Just a sample of the code not the whole thing, it's too long:
If Target.Row = 6 And Target.Column = 4 Then
Range("M43").Value = 1
Range("B7").Value = Target.Address
ElseIf Target.Row = 6 And Target.Column = 5 Then
Range("M43").Value = 1
Range("B7").Value = Target.Address
ElseIf Target.Row = 6 And Target.Column = 6 Then
Range("M43").Value = 1
Range("B7").Value = Target.Address
ElseIf Target.Row = 6 And Target.Column = 7 Then
Range("M43").Value = 1
Range("B7").Value = Target.Address
ElseIf Target.Row = 6 And Target.Column = 8 Then
Range("M43").Value = 1
Range("B7").Value = Target.Address
ElseIf Target.Row = 6 And Target.Column = 9 Then
Range("M43").Value = 1
Range("B7").Value = Target.Address
ElseIf Target.Row = 6 And Target.Column = 10 Then
Range("M43").Value = 1
Range("B7").Value = Target.Address
ElseIf Target.Row = 7 And Target.Column = 4 Then
Range("M43").Value = 2
Range("B7").Value = Target.Offset(-1, 0).Address
ElseIf Target.Row = 7 And Target.Column = 5 Then
Range("M43").Value = 2
Range("B7").Value = Target.Offset(-1, 0).Address
ElseIf Target.Row = 7 And Target.Column = 6 Then
Range("M43").Value = 2
Range("B7").Value = Target.Offset(-1, 0).Address
ElseIf Target.Row = 7 And Target.Column = 7 Then
Range("M43").Value = 2
Range("B7").Value = Target.Offset(-1, 0).Address
ElseIf Target.Row = 7 And Target.Column = 8 Then
Range("M43").Value = 2
Range("B7").Value = Target.Offset(-1, 0).Address
ElseIf Target.Row = 7 And Target.Column = 9 Then
Range("M43").Value = 2
Range("B7").Value = Target.Offset(-1, 0).Address
ElseIf Target.Row = 7 And Target.Column = 10 Then
Range("M43").Value = 2
Range("B7").Value = Target.Offset(-1, 0).Address
ElseIf Target.Row = 8 And Target.Column = 4 Then
Range("M43").Value = 3
Range("B7").Value = Target.Offset(-2, 0).Address
ElseIf Target.Row = 8 And Target.Column = 5 Then
Range("M43").Value = 3
Range("B7").Value = Target.Offset(-2, 0).Address
ElseIf Target.Row = 8 And Target.Column = 6 Then
Range("M43").Value = 3
Range("B7").Value = Target.Offset(-2, 0).Address
ElseIf Target.Row = 8 And Target.Column = 7 Then
Range("M43").Value = 3
Range("B7").Value = Target.Offset(-2, 0).Address
ElseIf Target.Row = 8 And Target.Column = 8 Then
Range("M43").Value = 3
Range("B7").Value = Target.Offset(-2, 0).Address
ElseIf Target.Row = 8 And Target.Column = 9 Then
Range("M43").Value = 3
Range("B7").Value = Target.Offset(-2, 0).Address
ElseIf Target.Row = 8 And Target.Column = 10 Then
Range("M43").Value = 3
Range("B7").Value = Target.Offset(-2, 0).Address
ElseIf Target.Row = 9 And Target.Column = 4 Then
Range("M43").Value = 4
Range("B7").Value = Target.Offset(-3, 0).Address
ElseIf Target.Row = 9 And Target.Column = 5 Then
Range("M43").Value = 4
Range("B7").Value = Target.Offset(-3, 0).Address
ElseIf Target.Row = 9 And Target.Column = 6 Then
Range("M43").Value = 4
Range("B7").Value = Target.Offset(-3, 0).Address
ElseIf Target.Row = 9 And Target.Column = 7 Then
Range("M43").Value = 4
Range("B7").Value = Target.Offset(-3, 0).Address
ElseIf Target.Row = 9 And Target.Column = 8 Then
Range("M43").Value = 4
Range("B7").Value = Target.Offset(-3, 0).Address
ElseIf Target.Row = 9 And Target.Column = 9 Then
Range("M43").Value = 4
Range("B7").Value = Target.Offset(-3, 0).Address
ElseIf Target.Row = 9 And Target.Column = 10 Then
Range("M43").Value = 4
Range("B7").Value = Target.Offset(-3, 0).Address
ElseIf Target.Row = 10 And Target.Column = 4 Then
Range("M43").Value = 5
Range("B7").Value = Target.Offset(-4, 0).Address
ElseIf Target.Row = 10 And Target.Column = 5 Then
Range("M43").Value = 5
Range("B7").Value = Target.Offset(-4, 0).Address
ElseIf Target.Row = 10 And Target.Column = 6 Then
Range("M43").Value = 5
Range("B7").Value = Target.Offset(-4, 0).Address
ElseIf Target.Row = 10 And Target.Column = 7 Then
Range("M43").Value = 5
Range("B7").Value = Target.Offset(-4, 0).Address
ElseIf Target.Row = 10 And Target.Column = 8 Then
Range("M43").Value = 5
Range("B7").Value = Target.Offset(-4, 0).Address
ElseIf Target.Row = 10 And Target.Column = 9 Then
Range("M43").Value = 5
Range("B7").Value = Target.Offset(-4, 0).Address
ElseIf Target.Row = 10 And Target.Column = 10 Then
Range("M43").Value = 5
Range("B7").Value = Target.Offset(-4, 0).Address
ElseIf Target.Row = 11 And Target.Column = 4 Then
Range("M43").Value = 6
Range("B7").Value = Target.Offset(-5, 0).Address
ElseIf Target.Row = 11 And Target.Column = 5 Then
Range("M43").Value = 6
Range("B7").Value = Target.Offset(-5, 0).Address
ElseIf Target.Row = 11 And Target.Column = 6 Then
Range("M43").Value = 6
Range("B7").Value = Target.Offset(-5, 0).Address
ElseIf Target.Row = 11 And Target.Column = 7 Then
Range("M43").Value = 6
Range("B7").Value = Target.Offset(-5, 0).Address
ElseIf Target.Row = 11 And Target.Column = 8 Then
Range("M43").Value = 6
Range("B7").Value = Target.Offset(-5, 0).Address
ElseIf Target.Row = 11 And Target.Column = 9 Then
Range("M43").Value = 6
Range("B7").Value = Target.Offset(-5, 0).Address
ElseIf Target.Row = 11 And Target.Column = 10 Then
Range("M43").Value = 6
Range("B7").Value = Target.Offset(-5, 0).Address
ElseIf Target.Row = 12 And Target.Column = 4 Then
Range("M43").Value = 7
Range("B7").Value = Target.Offset(-6, 0).Address
ElseIf Target.Row = 12 And Target.Column = 5 Then
Range("M43").Value = 7
Range("B7").Value = Target.Offset(-6, 0).Address
ElseIf Target.Row = 12 And Target.Column = 6 Then
Range("M43").Value = 7
Range("B7").Value = Target.Offset(-6, 0).Address
ElseIf Target.Row = 12 And Target.Column = 7 Then
Range("M43").Value = 7
Range("B7").Value = Target.Offset(-6, 0).Address
ElseIf Target.Row = 12 And Target.Column = 8 Then
Range("M43").Value = 7
Range("B7").Value = Target.Offset(-6, 0).Address
ElseIf Target.Row = 12 And Target.Column = 9 Then
Range("M43").Value = 7
Range("B7").Value = Target.Offset(-6, 0).Address
ElseIf Target.Row = 12 And Target.Column = 10 Then
Range("M43").Value = 7
Range("B7").Value = Target.Offset(-6, 0).Address
ElseIf Target.Row = 13 And Target.Column = 4 Then
Range("M43").Value = 8
Range("B7").Value = Target.Offset(-7, 0).Address
ElseIf Target.Row = 13 And Target.Column = 5 Then
Range("M43").Value = 8
Range("B7").Value = Target.Offset(-7, 0).Address
ElseIf Target.Row = 13 And Target.Column = 6 Then
Range("M43").Value = 8
Range("B7").Value = Target.Offset(-7, 0).Address
ElseIf Target.Row = 13 And Target.Column = 7 Then
Range("M43").Value = 8
Range("B7").Value = Target.Offset(-7, 0).Address
ElseIf Target.Row = 13 And Target.Column = 8 Then
Range("M43").Value = 8
Range("B7").Value = Target.Offset(-7, 0).Address
ElseIf Target.Row = 13 And Target.Column = 9 Then
Range("M43").Value = 8
Range("B7").Value = Target.Offset(-7, 0).Address
ElseIf Target.Row = 13 And Target.Column = 10 Then
Range("M43").Value = 8
Range("B7").Value = Target.Offset(-7, 0).Address
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ElseIf Target.Row = 15 And Target.Column = 4 Then
Range("M43").Value = 1
Range("B7").Value = Target.Address
ElseIf Target.Row = 15 And Target.Column = 5 Then
Range("M43").Value = 1
Range("B7").Value = Target.Address
ElseIf Target.Row = 15 And Target.Column = 6 Then
Range("M43").Value = 1
Range("B7").Value = Target.Address
ElseIf Target.Row = 15 And Target.Column = 7 Then
Range("M43").Value = 1
Range("B7").Value = Target.Address
ElseIf Target.Row = 15 And Target.Column = 8 Then
Range("M43").Value = 1
Range("B7").Value = Target.Address
ElseIf Target.Row = 15 And Target.Column = 9 Then
Range("M43").Value = 1
Range("B7").Value = Target.Address
ElseIf Target.Row = 15 And Target.Column = 10 Then
Range("M43").Value = 1
Range("B7").Value = Target.Address
ElseIf Target.Row = 16 And Target.Column = 4 Then
Range("M43").Value = 2
Range("B7").Value = Target.Offset(-1, 0).Address
ElseIf Target.Row = 16 And Target.Column = 5 Then
Range("M43").Value = 2
Range("B7").Value = Target.Offset(-1, 0).Address
ElseIf Target.Row = 16 And Target.Column = 6 Then
Range("M43").Value = 2
Range("B7").Value = Target.Offset(-1, 0).Address
ElseIf Target.Row = 16 And Target.Column = 7 Then
Range("M43").Value = 2
Range("B7").Value = Target.Offset(-1, 0).Address
ElseIf Target.Row = 16 And Target.Column = 8 Then
Range("M43").Value = 2
Range("B7").Value = Target.Offset(-1, 0).Address
ElseIf Target.Row = 16 And Target.Column = 9 Then
Range("M43").Value = 2
Range("B7").Value = Target.Offset(-1, 0).Address
ElseIf Target.Row = 16 And Target.Column = 10 Then
Range("M43").Value = 2
Range("B7").Value = Target.Offset(-1, 0).Address
ElseIf Target.Row = 17 And Target.Column = 4 Then
Range("M43").Value = 3
Range("B7").Value = Target.Offset(-2, 0).Address
ElseIf Target.Row = 17 And Target.Column = 5 Then
Range("M43").Value = 3
Range("B7").Value = Target.Offset(-2, 0).Address
ElseIf Target.Row = 17 And Target.Column = 6 Then
Range("M43").Value = 3
Range("B7").Value = Target.Offset(-2, 0).Address
ElseIf Target.Row = 17 And Target.Column = 7 Then
Range("M43").Value = 3
Range("B7").Value = Target.Offset(-2, 0).Address
ElseIf Target.Row = 17 And Target.Column = 8 Then
Range("M43").Value = 3
Range("B7").Value = Target.Offset(-2, 0).Address
ElseIf Target.Row = 17 And Target.Column = 9 Then
Range("M43").Value = 3
Range("B7").Value = Target.Offset(-2, 0).Address
ElseIf Target.Row = 17 And Target.Column = 10 Then
Range("M43").Value = 3
Range("B7").Value = Target.Offset(-2, 0).Address
ElseIf Target.Row = 18 And Target.Column = 4 Then
Range("M43").Value = 4
Range("B7").Value = Target.Offset(-3, 0).Address
ElseIf Target.Row = 18 And Target.Column = 5 Then
Range("M43").Value = 4
Range("B7").Value = Target.Offset(-3, 0).Address
ElseIf Target.Row = 18 And Target.Column = 6 Then
Range("M43").Value = 4
Range("B7").Value = Target.Offset(-3, 0).Address
ElseIf Target.Row = 18 And Target.Column = 7 Then
Range("M43").Value = 4
Range("B7").Value = Target.Offset(-3, 0).Address
ElseIf Target.Row = 18 And Target.Column = 8 Then
Range("M43").Value = 4
Range("B7").Value = Target.Offset(-3, 0).Address
ElseIf Target.Row = 18 And Target.Column = 9 Then
Range("M43").Value = 4
Range("B7").Value = Target.Offset(-3, 0).Address
ElseIf Target.Row = 18 And Target.Column = 10 Then
Range("M43").Value = 4
Range("B7").Value = Target.Offset(-3, 0).Address
ElseIf Target.Row = 19 And Target.Column = 4 Then
Range("M43").Value = 5
Range("B7").Value = Target.Offset(-4, 0).Address
ElseIf Target.Row = 19 And Target.Column = 5 Then
Range("M43").Value = 5
Range("B7").Value = Target.Offset(-4, 0).Address
ElseIf Target.Row = 19 And Target.Column = 6 Then
Range("M43").Value = 5
Range("B7").Value = Target.Offset(-4, 0).Address
ElseIf Target.Row = 19 And Target.Column = 7 Then
Range("M43").Value = 5
Range("B7").Value = Target.Offset(-4, 0).Address
ElseIf Target.Row = 19 And Target.Column = 8 Then
Range("M43").Value = 5
Range("B7").Value = Target.Offset(-4, 0).Address
ElseIf Target.Row = 19 And Target.Column = 9 Then
Range("M43").Value = 5
Range("B7").Value = Target.Offset(-4, 0).Address
ElseIf Target.Row = 19 And Target.Column = 10 Then
Range("M43").Value = 5
Range("B7").Value = Target.Offset(-4, 0).Address
ElseIf Target.Row = 20 And Target.Column = 4 Then
Range("M43").Value = 6
Range("B7").Value = Target.Offset(-5, 0).Address
ElseIf Target.Row = 20 And Target.Column = 5 Then
Range("M43").Value = 6
Range("B7").Value = Target.Offset(-5, 0).Address
ElseIf Target.Row = 20 And Target.Column = 6 Then
Range("M43").Value = 6
Range("B7").Value = Target.Offset(-5, 0).Address
ElseIf Target.Row = 20 And Target.Column = 7 Then
Range("M43").Value = 6
Range("B7").Value = Target.Offset(-5, 0).Address
ElseIf Target.Row = 20 And Target.Column = 8 Then
Range("M43").Value = 6
Range("B7").Value = Target.Offset(-5, 0).Address
ElseIf Target.Row = 20 And Target.Column = 9 Then
Range("M43").Value = 6
Range("B7").Value = Target.Offset(-5, 0).Address
ElseIf Target.Row = 20 And Target.Column = 10 Then
Range("M43").Value = 6
Range("B7").Value = Target.Offset(-5, 0).Address
ElseIf Target.Row = 21 And Target.Column = 4 Then
Range("M43").Value = 7
Range("B7").Value = Target.Offset(-6, 0).Address
ElseIf Target.Row = 21 And Target.Column = 5 Then
Range("M43").Value = 7
Range("B7").Value = Target.Offset(-6, 0).Address
ElseIf Target.Row = 21 And Target.Column = 6 Then
Range("M43").Value = 7
Range("B7").Value = Target.Offset(-6, 0).Address
ElseIf Target.Row = 21 And Target.Column = 7 Then
Range("M43").Value = 7
Range("B7").Value = Target.Offset(-6, 0).Address
ElseIf Target.Row = 21 And Target.Column = 8 Then
Range("M43").Value = 7
Range("B7").Value = Target.Offset(-6, 0).Address
ElseIf Target.Row = 21 And Target.Column = 9 Then
Range("M43").Value = 7
Range("B7").Value = Target.Offset(-6, 0).Address
ElseIf Target.Row = 21 And Target.Column = 10 Then
Range("M43").Value = 7
Range("B7").Value = Target.Offset(-6, 0).Address
ElseIf Target.Row = 22 And Target.Column = 4 Then
Range("M43").Value = 8
Range("B7").Value = Target.Offset(-7, 0).Address
ElseIf Target.Row = 22 And Target.Column = 5 Then
Range("M43").Value = 8
Range("B7").Value = Target.Offset(-7, 0).Address
ElseIf Target.Row = 22 And Target.Column = 6 Then
Range("M43").Value = 8
Range("B7").Value = Target.Offset(-7, 0).Address
ElseIf Target.Row = 22 And Target.Column = 7 Then
Range("M43").Value = 8
Range("B7").Value = Target.Offset(-7, 0).Address
ElseIf Target.Row = 22 And Target.Column = 8 Then
Range("M43").Value = 8
Range("B7").Value = Target.Offset(-7, 0).Address
ElseIf Target.Row = 22 And Target.Column = 9 Then
Range("M43").Value = 8
Range("B7").Value = Target.Offset(-7, 0).Address
ElseIf Target.Row = 22 And Target.Column = 10 Then
Range("M43").Value = 8
Range("B7").Value = Target.Offset(-7, 0).Address
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ElseIf Target.Row = 24 And Target.Column = 4 Then
Range("M43").Value = 1
Range("B7").Value = Target.Address
ElseIf Target.Row = 24 And Target.Column = 5 Then
Range("M43").Value = 1
Range("B7").Value = Target.Address
ElseIf Target.Row = 24 And Target.Column = 6 Then
Range("M43").Value = 1
Range("B7").Value = Target.Address
ElseIf Target.Row = 24 And Target.Column = 7 Then
Range("M43").Value = 1
Range("B7").Value = Target.Address
ElseIf Target.Row = 24 And Target.Column = 8 Then
Range("M43").Value = 1
Range("B7").Value = Target.Address
ElseIf Target.Row = 24 And Target.Column = 9 Then
Range("M43").Value = 1
Range("B7").Value = Target.Address
ElseIf Target.Row = 24 And Target.Column = 10 Then
Range("M43").Value = 1
Range("B7").Value = Target.Address
ElseIf Target.Row = 25 And Target.Column = 4 Then
Range("M43").Value = 2
Range("B7").Value = Target.Offset(-1, 0).Address
ElseIf Target.Row = 25 And Target.Column = 5 Then
Range("M43").Value = 2
Range("B7").Value = Target.Offset(-1, 0).Address
ElseIf Target.Row = 25 And Target.Column = 6 Then
Range("M43").Value = 2
Range("B7").Value = Target.Offset(-1, 0).Address
ElseIf Target.Row = 25 And Target.Column = 7 Then
Range("M43").Value = 2
Range("B7").Value = Target.Offset(-1, 0).Address
ElseIf Target.Row = 25 And Target.Column = 8 Then
Range("M43").Value = 2
Range("B7").Value = Target.Offset(-1, 0).Address
ElseIf Target.Row = 25 And Target.Column = 9 Then
Range("M43").Value = 2
Range("B7").Value = Target.Offset(-1, 0).Address
ElseIf Target.Row = 25 And Target.Column = 10 Then
Range("M43").Value = 2
Range("B7").Value = Target.Offset(-1, 0).Address
ElseIf Target.Row = 26 And Target.Column = 4 Then
Range("M43").Value = 3
Range("B7").Value = Target.Offset(-2, 0).Address
ElseIf Target.Row = 26 And Target.Column = 5 Then
Range("M43").Value = 3
Range("B7").Value = Target.Offset(-2, 0).Address
ElseIf Target.Row = 26 And Target.Column = 6 Then
Range("M43").Value = 3
Range("B7").Value = Target.Offset(-2, 0).Address
ElseIf Target.Row = 26 And Target.Column = 7 Then
Range("M43").Value = 3
Range("B7").Value = Target.Offset(-2, 0).Address
ElseIf Target.Row = 26 And Target.Column = 8 Then
Range("M43").Value = 3
Range("B7").Value = Target.Offset(-2, 0).Address
ElseIf Target.Row = 26 And Target.Column = 9 Then
Range("M43").Value = 3
Range("B7").Value = Target.Offset(-2, 0).Address
ElseIf Target.Row = 26 And Target.Column = 10 Then
Range("M43").Value = 3
Range("B7").Value = Target.Offset(-2, 0).Address
ElseIf Target.Row = 27 And Target.Column = 4 Then
Range("M43").Value = 4
Range("B7").Value = Target.Offset(-3, 0).Address
ElseIf Target.Row = 27 And Target.Column = 5 Then
Range("M43").Value = 4
Range("B7").Value = Target.Offset(-3, 0).Address
ElseIf Target.Row = 27 And Target.Column = 6 Then
Range("M43").Value = 4
Range("B7").Value = Target.Offset(-3, 0).Address
ElseIf Target.Row = 27 And Target.Column = 7 Then
Range("M43").Value = 4
Range("B7").Value = Target.Offset(-3, 0).Address
ElseIf Target.Row = 27 And Target.Column = 8 Then
Range("M43").Value = 4
Range("B7").Value = Target.Offset(-3, 0).Address
ElseIf Target.Row = 27 And Target.Column = 9 Then
Range("M43").Value = 4
Range("B7").Value = Target.Offset(-3, 0).Address
ElseIf Target.Row = 27 And Target.Column = 10 Then
Range("M43").Value = 4
Range("B7").Value = Target.Offset(-3, 0).Address
ElseIf Target.Row = 28 And Target.Column = 4 Then
Range("M43").Value = 5
Range("B7").Value = Target.Offset(-4, 0).Address
ElseIf Target.Row = 28 And Target.Column = 5 Then
Range("M43").Value = 5
Range("B7").Value = Target.Offset(-4, 0).Address
ElseIf Target.Row = 28 And Target.Column = 6 Then
Range("M43").Value = 5
Range("B7").Value = Target.Offset(-4, 0).Address
ElseIf Target.Row = 28 And Target.Column = 7 Then
Range("M43").Value = 5
Range("B7").Value = Target.Offset(-4, 0).Address
ElseIf Target.Row = 28 And Target.Column = 8 Then
Range("M43").Value = 5
Range("B7").Value = Target.Offset(-4, 0).Address
ElseIf Target.Row = 28 And Target.Column = 9 Then
Range("M43").Value = 5
Range("B7").Value = Target.Offset(-4, 0).Address
ElseIf Target.Row = 28 And Target.Column = 10 Then
Range("M43").Value = 5
Range("B7").Value = Target.Offset(-4, 0).Address
ElseIf Target.Row = 29 And Target.Column = 4 Then
Range("M43").Value = 6
Range("B7").Value = Target.Offset(-5, 0).Address
ElseIf Target.Row = 29 And Target.Column = 5 Then
Range("M43").Value = 6
Range("B7").Value = Target.Offset(-5, 0).Address
ElseIf Target.Row = 29 And Target.Column = 6 Then
Range("M43").Value = 6
Range("B7").Value = Target.Offset(-5, 0).Address
ElseIf Target.Row = 29 And Target.Column = 7 Then
Range("M43").Value = 6
Range("B7").Value = Target.Offset(-5, 0).Address
ElseIf Target.Row = 29 And Target.Column = 8 Then
Range("M43").Value = 6
Range("B7").Value = Target.Offset(-5, 0).Address
ElseIf Target.Row = 29 And Target.Column = 9 Then
Range("M43").Value = 6
Range("B7").Value = Target.Offset(-5, 0).Address
ElseIf Target.Row = 29 And Target.Column = 10 Then
Range("M43").Value = 6
Range("B7").Value = Target.Offset(-5, 0).Address
ElseIf Target.Row = 30 And Target.Column = 4 Then
Range("M43").Value = 7
Range("B7").Value = Target.Offset(-6, 0).Address
ElseIf Target.Row = 30 And Target.Column = 5 Then
Range("M43").Value = 7
Range("B7").Value = Target.Offset(-6, 0).Address
ElseIf Target.Row = 30 And Target.Column = 6 Then
Range("M43").Value = 7
Range("B7").Value = Target.Offset(-6, 0).Address
ElseIf Target.Row = 30 And Target.Column = 7 Then
Range("M43").Value = 7
Range("B7").Value = Target.Offset(-6, 0).Address
ElseIf Target.Row = 30 And Target.Column = 8 Then
Range("M43").Value = 7
Range("B7").Value = Target.Offset(-6, 0).Address
ElseIf Target.Row = 30 And Target.Column = 9 Then
Range("M43").Value = 7
Range("B7").Value = Target.Offset(-6, 0).Address
ElseIf Target.Row = 30 And Target.Column = 10 Then
Range("M43").Value = 7
Range("B7").Value = Target.Offset(-6, 0).Address
ElseIf Target.Row = 31 And Target.Column = 4 Then
Range("M43").Value = 8
Range("B7").Value = Target.Offset(-7, 0).Address
ElseIf Target.Row = 31 And Target.Column = 5 Then
Range("M43").Value = 8
Range("B7").Value = Target.Offset(-7, 0).Address
ElseIf Target.Row = 31 And Target.Column = 6 Then
Range("M43").Value = 8
Range("B7").Value = Target.Offset(-7, 0).Address
ElseIf Target.Row = 31 And Target.Column = 7 Then
Range("M43").Value = 8
Range("B7").Value = Target.Offset(-7, 0).Address
ElseIf Target.Row = 31 And Target.Column = 8 Then
Range("M43").Value = 8
Range("B7").Value = Target.Offset(-7, 0).Address
ElseIf Target.Row = 31 And Target.Column = 9 Then
Range("M43").Value = 8
Range("B7").Value = Target.Offset(-7, 0).Address
ElseIf Target.Row = 31 And Target.Column = 10 Then
Range("M43").Value = 8
Range("B7").Value = Target.Offset(-7, 0).Address
End If
Upvotes: 0
Related Questions
- Counting number of cells populated with data based on referenced dates in the future
- Returning a row number based on two (or more) criteria
- Excel Formula to highlight range based on date range
- Concatenate cells within Excel row based off variable start and end columns
- Searching trough column (cells from 2 to 1001) and comparing date in every cell to extract data from the same row
- Get content of a cell given the row and column numbers
- Year to Date Excel formula
- Excel Formula to lookup the nearest greater number based on date
- Copy a Column Format based on header row Excel