Dynamically selecting a row in excel using vba and MATCH function

Problem Statement : In a Resource loading tracker sheet(Planning Bid Managers), I wanted each user to be able to add a new row only above a particular row belonging to that user(so that my totals formula remained intact). However, with the addition of rows, the position of this particular reference row would change. 

What I'm going to do : 
1) first of all, I create form buttons that would be used to insert new rows. (Developer->Insert-> Form Controls-> Button)
Note : If you don't see the Developer tab, you need to go to File-> Excel Options-> Customize Ribbon, and check the "Developer" box. 
2) Then, I assign appropriate macros to perform the insert correctly. 

Execution - How I started : Initially, after inserting the buttons, i began with simply recording macros (View->Macros -> Record Macros), and inserted a row below the desired location. 

So the following macro got created : 

Sub insertrow()

'

' insertrow Macro

' Inserts a new row at selected location

'

    Rows("8:8").Select

    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

End Sub

However, the problem with this is, that it will always insert rows above row 8! This would need to change as new rows get inserted!! So this numbering needs to be dynamic. 

The Solution: 
1) First, I created a small table to keep a track of the person's name using the excel MATCH function in the same worksheet. The MATCH function is used like this - 
=MATCH("Angshuman Majumder",B:B,0)+2
This formula essentially returns the row number where Angshuman is found, and adds 2 to it. (Since I want to add a row 2 rows below the row containing the name of Angshuman). 

Now that I have the number row number where i want the row available, I now modify the macro like this: 

2) '  Instead of  "Rows("8:8").Select" , Get the value of where Angshuman is located thru a variable, then simply select that row!

Sub Angshuman()
'
' Angshuman Macro
    var1 = Worksheets("Planning").Range("Z1").Value
     Rows(var1 & ":" & var1).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow

End Sub

Similarly, for the other users, I add macros in turn: 
Sub Mandeep()
'
' Mandeep Macro
'
    var1 = Worksheets("Planning").Range("Z2").Value
    Rows(var1 & ":" & var1).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
End Sub
Sub Suman()
'
' Suman Macro
'
    var1 = Worksheets("Sheet1").Range("Z3").Value
    Rows(var1 & ":" & var1).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
End Sub
Sub Joel()
'
' Joel Macro
'

'
'    Rows("31:31").Select
    var1 = Worksheets("Planning").Range("Z4").Value
     Rows(var1 & ":" & var1).Select

    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow

End Sub

And that's how it's done! :-) 

Allowing users to edit ranges in excel

You can allow users to only edit certain cells, or ranges of cell in an excel, keeping the rest protected (See Protecting a worksheet in excel if you want to protect the whole sheet).

1. Tools -> Protection -> Allow Users to Edit Ranges














2. Click on New















3. Give appropriate title for the range, and define the range

4. You can add several ranges using steps 2 & 3
5. Click OK

6. You'd come to the New-modify-delete form again, where you have the option of protecting the sheet. Click the button marked "Protect Sheet".
(remember, we have to protect the sheet so that only the defined ranges are editable)

7. You would be asked to provide a password, and confirm the same. Once you enter the passwords, you are all set !!

Protecting a worksheet

If, for whatever reasons, you do not wish people to mess with the data that you are sending out in your sheet, you can simply protect the sheet.

PS: In case you want them to be able to edit parts of your sheet, you can use "Allow Users to Edit Ranges".

For protecting a worksheet, select the appropriate worksheet.
Go to the tools menu.
Tools -> Protection -> Protect Worksheet


Tools -> Protection -> Protect Worksheet











- At this stage, excel would ask you for a password. Provide a password of your choice.
- Excel then asks you to re-enter the same password.
- Click OK, and you are all set.

A Quick Gannt in excel

See the sample file here.


1. Open up a new excel file
2. Under column A, we would have the Task Names
3. Under column B, we would have the Start dates
4. Under column C, we would have the End dates
5. Column D onwards we have the dates for which we are building the Gantt.
6. Select cell D2 and copy this formula into the cell:
=IF(AND(D$1>=$B2,D$1<=$C2),1,0)
7. This formula essentially says, "If the date at the top of this column falls between the start and end dates, then have the value 1 in this cell; else have 0 in this cell.
8. copy the contents of the cell having the formula (Cell D2 in the example)
9. Select all the cells under the date columns (Cell D2 to AH7 in the example)
10. Paste the formula into all the selected cells
11. In the toolbar, select Format -> Conditional formatting
12. Select "Cell Value is" , "equal to" , and type the number "1" without quotes in the condition.
13. Click on Format
14. Select Patterns tab
15. Select a color of your choice (not white!!)
16. Add one more condition.
17. Perform steps 14-15 and select white this time.
18. Click OK
You're all set !! Fill in the dates and see the Gantt chart appear !