I totally agree; I am very new to all of this, as well as being old(!) and it explains things very clearly indeed.
Congratulatiuons to Jonmo1
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Most common question/answer
- Thread starter Jonmo1
- Start date
I totally agree; I am very new to all of this, as well as being old(!) and it explains things very clearly indeed.
Congratulatiuons to Jonmo1
First of all, massive kudos and bouquets to Jonmo for even daring to think of a project like this, let alone get so far with it in so little time.
I am not familiar with how docs are laid out on this board, but to me the most convenient layout would be a list of topics and/or keywords hyperlinked to their relevant areas in the doc, and the facility to return to the index.
IMHO this is a Hall of Fame candidate.
I am not familiar with how docs are laid out on this board, but to me the most convenient layout would be a list of topics and/or keywords hyperlinked to their relevant areas in the doc, and the facility to return to the index.
IMHO this is a Hall of Fame candidate.
Heeey, dont worry, 84 isn't that old
Easy on, fella, you are not that far away from me!!!
Jonmo1
MrExcel MVP
- Joined
- Oct 12, 2006
- Messages
- 44,061
Thanks for the feedback all.
Must admit this was a pretty large undertaking. It surprised me how long the article got. And I'm still reading it over and over again and finding more typos.
I'll be submitting another revised version later. Just correcting typos, no changes to content.
I'm pretty sure NateO is going to handle making it an official post. I would also like to see it as an Indexed type of article. With the main list being links to each individual section, then back to main list.
Must admit this was a pretty large undertaking. It surprised me how long the article got. And I'm still reading it over and over again and finding more typos.
I'll be submitting another revised version later. Just correcting typos, no changes to content.
I'm pretty sure NateO is going to handle making it an official post. I would also like to see it as an Indexed type of article. With the main list being links to each individual section, then back to main list.
Jonmo1
MrExcel MVP
- Joined
- Oct 12, 2006
- Messages
- 44,061
OK, here is what I believe will be my final revision.
I have no statistical data to support which questions or problems are asked the most. But after a couple years of reading this forum, it seems fairly obvious what they are.
I've split them up into 2 categories, Formula and VBA.
I think there should be more for the VBA category, but I couldn't think of any more.
Formula:
1. How to eliminate #N/A errors from a Vlookup formula?
2. Vlookup doesn't find the match but I know it's there?
3. How to make Vlookup work from right to left?
4. How to perform a multiple criteria count or sum?
5. How to set rates based on value ranges (1-10 = 10%, 11-20 = 15%, 21-30 = 20%)?
6. How do I nest more than 7 Ifs?
7. How to make a range adjust its size according to how much data is in the range?
8. How to lookup value in a matrix with row and column variables?
VBA:
1. How to find the last used row or column #?
2. How to speed up my code?
3. How to apply macro to multiple sheets?
4. I protected my sheets but now my macros don't work?
5. How can I name my sheet according to the value in a cell?
How to eliminate #N/A errors from a Vlookup formula.
Bear in mind the same logic in this section can be applied to Hlookup as well as Vlookup, just transposed.
Use this example formula for this section.
=VLOOKUP(A1,B:C, 2, FALSE)
There are 3 reasons for a Vlookup to return #N/A.
1. The value in A1 was not found in column B and false was used in the 4th argument of the formula.
2. The data is not sorted ascending by column B, and true was used in the 4th argument of the formula.
3. The Vlookup formula found a match for A1 in column B. But the resulting cell contains a formula that results in #N/A.
There are also 2 reasons for wanting to eliminate #N/A errors.
1. You expect the errors to occur and just don’t want to see them.
2. You know the match is there, but vlookup is not finding it.
Reason 1. - If you are expecting #N/A errors, but just don't want to see them.
Basically, you know sometimes there won't be a match, and you just don't want to see the #N/A errors. This is wise because other formulas that refer to the vlookup formula will error if the vlookup formula is #N/A.
There are 2 basic methods to eliminate the expected #N/A errors.
Again, this is using the example formula above.
Method 1. Test the Vlookup formula for the error, and return blank (or other value) if it results in an error.
=IF(ISNA(VLOOKUP(A1,B:C,2,FALSE)),"",VLOOKUP(A1,B:C,2,FALSE))
Method 2. Check if the value in A1 exists in column B, if it does, do the Vlookup, if not, return blank (or other value).
=IF(ISNUMBER(MATCH(A1,B:B,0)),VLOOKUP(A1,B:C,2,FALSE),"")
Method 2 is widely considered the more efficient method, while method 1 seems a little easier to understand and implement. Method 1 actually must do the vlookup twice. Where method 2 only has to look in column B for a match, it does not have to return a value and test for #N/A.
Reason 2. - If you think the vlookup should be returning a result, but it’s giving #N/A.
First understand the 4th argument of Vlookup, either true or false (also 1 or 0).
True means Vlookup looks for the closest match, the largest value that is less than or equal to the lookup value. And the data must be sorted ascending by the leftmost column of the array (column B in this example).
False means Vlookup looks for an exact match. And the data does not need to be sorted.
If you omit the 4th argument (leave it blank), true is assumed.
If you use true for the 4th argument, and you receive #N/A errors or unexpected results, the problem is most likely that the data is not sorted ascending.
If you use false as the 4th argument and you are receiving #N/A errors, then that means that an exact match was not found. Make sure the data matches exactly. Spelling counts, but capitalization does not matter.
This is still using the same example formula from above.
Look at the values in both A1 and in column B.
Check the spelling and look for extra spaces or other non visible characters.
Probably the most common problem is "Numbers Stored As Text". Sometimes Excel doesn't see numbers as numbers.
This is especially true when importing or copy/pasting numbers from an external source. Excel will sometimes treat these numbers as text. The best way to tell if that is the problem is to use the Isnumber function.
Again, given the example Vlookup formula above, you would want to test both A1 and column B. So put these formulas anywhere.
=ISNUMBER(A1)
=ISNUMBER(B1)
The Isnumber formula for column B should be filled down to the end of the data.
Those formulas will return true if they are numbers, false if they are "Numbers Stored As Text".
You want them all to be the same, either all true or all false, not a mixture.
It would be preferred if they are all numbers.
There are a few ways to resolve this problem.
1. Paste special – values – add.
Copy any blank cell.
Highlight the range that contains the "Numbers Stored as Text" - Isnumber returned False.
Click edit – paste special – select values – select add – click ok.
2. Data – text to columns.
Highlight the range that contains the "Numbers Stored as Text"
Click data – text to columns, select deliminated – click finish.
3. If column B is either all numbers or all text, not a mix of both, you can adjust your formula to account for "Numbers Stored as Text"
If A1 is a number and column B is "Numbers Stored as Text".
You can write:
=VLOOKUP(A1&"",B:C,2,FALSE)
If A1 is "Number Stored as Text" and column B is numbers
You can write:
=VLOOKUP(A1+0,B:C,2,FALSE)
Vlookup doesn't find the match but I know it's there.
See reason 2 from previous section "How to eliminate #N/A errors from a Vlookup formula".
How to make Vlookup work from right to left
We all know that vlookup reads from left to right. It looks up a value in column A and returns the corresponding value from column B. But very often you need to do the opposite. You need to lookup the value in column B and return the corresponding value from column A.
The short answer is that Vlookup cannot do it, period.
However, you can use a combination of the Index and Match functions to accomplish this. These are one of the most commonly used combinations of functions in Excel.
You first need to understand what each function does individually before you can understand how they work together.
The Index function returns the value from the intersection of the specified row and column within an array.
Here is the basic structure.
=INDEX(array, row #, col #)
Example 1:
=INDEX(A1:D10,5,3)
Example 2:
=INDEX(G10:I30,15,2)
Example 1 would return the value in C5 (the intersection of the 5th row and 3rd column within A1:D10).
Example 2 would return the value in H24 (the intersection of 15th row and 2nd column within G10:I30).
It is important to notice in example 2 that it does not return the 15th row and 2nd column of the sheet (B15). It returns the cell at the intersection of the 15th row and 2nd column relative to the top left cell (G10) of the array (G10:I30). That is cell H24. H is the 2nd column from G, 24 is the 15th row from 10.
The row # and column # arguments in Index are both optional, but at least 1 of the 2 must be used.
You can also use 1 dimensional arrays (either 1 column or 1 row). In this case you would only use the row # or column # argument. If using a 1 column array like A:A, you would use the row #. If using a 1 row array like A1:Z1, you would use the column #.
=INDEX(A:A,23)
This returns the value in A23.
=INDEX(A5:G5,,3) ß Notice the row # argument is omitted.
This would return the value in C5.
The Match function looks up a value in a 1 dimensional array and returns the position # in which it was found.
Example 1:
=MATCH("George",A1:A10,0)
If George was in say A5, this formula would return 5. A5 is the 5th position within A1:A10.
Example 2:
=MATCH("George",E3:M3,0)
If George was in say H3, this formula would return 4. H3 is the 4th position within E3:M3.
Important to notice that match does not necessarily return the row # or column # that the value was found in. It returns the position # relative to the 1st cell in the array.
The 3rd argument in Match is similar to the True/False argument in Vlookup. It determines if the Match formula will find an exact match, or closest match.
0 = Match searches for exact match. Returns #N/A if not found. Data does not need to be sorted.
1 = Match searches for closest match. The largest # that is less than or equal to lookup value. Data must be sorted ascending.
-1 = Match searches for closest match. The smallest # that is greater than or equal to lookup value. Data must be sorted descending.
If omitted, 1 is default.
Now to use Index and Match in combination to simulate Vlookup and read from right to left.
Here is a basic Vlookup formula.
=VLOOKUP(A1,B:C,2,FALSE)
To perform the exact same function with Index and Match.
=INDEX(C:C,MATCH(A1,B:B,0))
Column C is the array for the Index.
Match will find A1 in column B and return the position # in which it was found.
Index will then return that position # from column C.
This is now very powerful because you can lookup in any column, and return from any column.
So take the example Vlookup formula from above. If you wanted to find A1 in column C and return the value from column B, simply swap the two ranges from the example Index Match formula.
=INDEX(B:B,MATCH(A1,C:C,0))
This subject has caused much debate recently on the forum. About weather or not we actually need Vlookup to begin with, since Index Match can do the same thing.
It is widely considered that the Index Match combination is more robust and efficient. However, it is also recognized that Vlookup is a little easier to write, understand and explain/teach.
This topic is discussed in great detail here.
http://www.mrexcel.com/forum/showthread.php?t=322319
How to perform a multiple criteria count or sum.
Very often I see posts that say I have this Sumif formula that works great.
=SUMIF(A:A,"SomeWord",C:C)
It sums column C where column A = "SomeWord".
But I want to add another criteria, to sum column C where column A = "SomeWord" and column B = "AnotherWord".
Sumproduct is a very powerful function that you can use to accomplish this.
Here is a basic structure using the example of column A = "SomeWord" and column B = "AnotherWord".
=SUMPRODUCT(--(A1:A100="SomeWord"),--(B1:B100="AnotherWord"),C1:C100)
That will sum column C where column A = "SomeWord" and column B = "AnotherWord".
It is a very simple formula and works extremely well.
Each section defined by --(range=criteria) is a logical question with a true or false answer.
The -- converts the true/false answer to 1/0. True = 1, false = 0.
The 3rd section C1:C100 is not a question, it only contains a numerical value.
It reads the formula 1 row at a time, and answers each true/false question.
So take row 1 - is A1 = "Someword" and is B1 = "AnotherWord"?
Each question has a true/false answer, converted to 1 or 0.
It then multiplies the 3 sections together, the 2 answers and the numerical value in column C.
The result of that multiplication is the value for row 1.
Then moves on to the next row.
Is A2 = "SomeWord" and is "B2 = "AnotherWord"?
It then multiplies the 3 sections together, the 2 answers and the numerical value in column C.
The result of that multiplication is the value for row 2.
Each row has only 4 possible combinations of answers.
Both true = 1*1*(value of column C).
Both false = 0*0*(value of column C).
True and false = 1*0*(value of column C).
False and true = 0*1*(value of column C).
Each row has only 2 possible values, 1*(value of column C) or 0*(value of column C).
Only if both criteria are true, will the result be 1*(value of column C).
If either question (or both) is false, the resulting value will be 0*(value of column C).
After all questions in all rows are answered and multiplied and values assigned to the row, it then sums the values of each row.
So given a small example
A1 = SomeWord
B1 = AnotherWord
C1 = 25
A2 = SomeWord
B2 = BlahBlah
C2 = 40
This results in (1*1*25) + (1*0*40).
That breaks down to (25) + (0).
Result is 25, only 1 row (row 1) met the criteria of col A = "SomeWord" and col B = "AnotherWord".
IMPORTANT RULES
The ranges cannot be entire column references like A:A, you must use a finite range like A1:A100.
The ranges must be the same size.
You can use cell references for the variables like:
=SUMPRODUCT(--(A1:A100=D1),--(B1:B100=E1),C1:C100)
D1 = SomeWord.
E1 = AnotherWord.
You can make it a conditional count by simply removing the sumrange.
=SUMPRODUCT(--(A1:A100=D1),--(B1:B100=E1))
That will count rows where column A = "SomeWord" and column B = "AnotherWord".
You can also add other functions inside each section of the formula for criteria.
For example, you want to sum column C where column A begins with "X".
=SUMPRODUCT(--(LEFT(A1:A100,1)="X"),C1:C100)
So you can create just about any function or combination of functions in each criteria section of the formula, as long as those functions result in a true or false answer.
How to set rates based on value ranges (1-10 = 10%, 11-20 = 15%, 21-30 = 20%)
A common example of this is for sales commissions.
If my sales for the month were:
0 - 1000 I get 5%
1001 to 1500 I get 7%
1501 to 2000 I get 9%
2001 or more I get 10%
So say A1 is your total sales for the month.
=A1*LOOKUP(A1,{0, 1001, 1501, 2001},{.05, .07, .09, .1})
Make sure the values in {0, 1001, 1501, 2001} are sorted ascending left to right.
It looks for the largest number in {0,1001,1501,2001} that is less than or equal to A1.
It then returns the corresponding value from {.05,.07,.09,.1}.
Then multiplies A1 by that #.
Another way to do it is to use a lookup table on a sheet.
That way you don't have to hard code the values in the formula, and it’s easier to adjust/maintain.
You would put the low end dollar amounts in column B and the corresponding % in column C.
Sort the table ascending by column B.
=A1*LOOKUP(A1,B:C)
It basically looks for the largest number in column B that is less than or equal to A1.
It then returns the corresponding value from column C.
Then multiplies A1 by that #.
That's basically it, pretty straight forward.
How do I nest more than 7 IFs
There's really no straight answer to that. But it is almost always possible to find a different approach to your goal without nesting ifs. But if you must, here is a way to do it.
Split the formula in two.
In one cell (say A1), nest your first 7 ifs, and make it return "" if none of the results are true. This is a very important step. To be sure that the first nested if formula results in "" if none of the if functions are true.
In a second cell (say B1), test A1 for blank. If A1 is not blank, return A1. If A1 is blank continue the nested ifs.
So for example:
In A1:
=IF(D1=1,"A",IF(D1=2,"B",IF(D1=3,"C",IF(D1=4,"D",""))))
In B1:
=IF(A1<>"",A1,IF(D1=5,"E",IF(D1=6,"F",IF(D1=7,"G",IF(D1=8,"H","")))))
But I must strongly recommend searching for alternate methods.
Using the example 8 nested if setup above.
It is much easier to do it like this, similar to the last section of assigning a rate to a commission.
=LOOKUP(D1,{1,2,3,4,5,6,7,8,9},{"A","B","C","D","E","F","G","H",""})
It finds D1 in {1,2,3,4,5,6,7,8,9}, then returns the corresponding value from {"A","B","C","D","E","F","G","H",""}.
The first set of values needs to be sorted alphanumerically from left to right.
It can also be done with lookup table as in previous section.
Column B is the criteria, column C is the results.
Data must be sorted ascending by column B.
=LOOKUP(D1,B:C)
Or to be more exact, use Vlookup:
=VLOOKUP(D1,B:C,2,FALSE)
Using the Vlookup, the data would not need to be sorted.
How to make a range adjust its size according to how much data is in the range.
You have a bunch of formulas referring to 100s of rows of data.
But that data is updated frequently, and the length (or width) of the data is not always the same. Sometimes it’s 500 rows, sometimes it's 750.
You could write your formulas to use the whole column A:A, or a very large part of the column to cover the largest the range would ever be.
However, that is a good way to decrease the performance of your sheet. The larger the range you refer to, the slower the formula will be.
A solution is to use a dynamic named range. One that automatically adjusts its length and/or width based on the size of the data on the sheet.
We'll use an example of data that changes size in length, not width. But the same logic can be applied the other way around. It can also be used for both (varying length and width).
You first need to decide on a specific column to use that will determine how far down the data goes.
And you'll want to decide which row # the data starts on, that is usually pretty consistent.
So for this example, we'll say the data starts in row 1, and col A will determine how far down the data goes.
This formula will return a range object starting in A1 going down to the last cell in column A that is not empty. Provided there are no blanks within the range of column A.
=A1:INDEX(A:A,COUNTA(A:A))
So you could write this basic formula using that.
=SUM(A1:INDEX(A:A,COUNTA(A:A)))
You can then create a named range for that range.
Click insert – name – define.
Type a name like MyRange.
In the "Refers to" box, put:
=$A$1:INDEX($A:$A,COUNTA($A:$A))
It’s important to use absolute references in the named range, things can get weird if you don't.
Now you can use the formula:
=SUM(MyRange)
If there are blanks in the range, it gets a little more complicated. But I've found this to work well for me.
=$A$1:INDEX($A:$A,MATCH(2,1/($A$1:$A$65535<>"")))
The $A$1:$A$65535 part cannot be an entire column ref like A:A, as it is an array formula. I'm not sure why this works in a named range without CTRL + SHIFT + ENTER, but it does.
If you wanted to use that in a cell formula, instead of named range, you would have to enter it with CTRL + SHIFT + ENTER to make it an array formula.
Like this:
{=SUM($A$1:INDEX($A:$A,MATCH(2,1/($A$1:$A$65535<>""))))}
Now these can be transposed to row ranges instead of column ranges.
=$A$1:INDEX($1:$1,COUNTA($1:$1))
And
=$A$1:INDEX($1:$1,MATCH(2,1/($A$1:$IU$1<>"")))
Now, to do a 2 dimensional dynamic range (multiple columns and multiple rows), you have to use the Offset function.
The basic structure is:
=OFFSET($A$1,0,0,# of rows,# of columns))
We'll again use column A to determine # of rows, and row 1 to determine number of columns.
This will give a range from A1 to the last column and last row.
=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))
That also again, depends on having no blanks in column A or row 1.
If there are blanks, you can adapt the match part from above.
=OFFSET($A$1,0,0,MATCH(2,1/($A$1:$A$65535<>"")),MATCH(2,1/($A$1:$IU$1<>"")))
When using these formulas as named ranges, be careful where you put your formula =SUM(MyRange). If you put that formula in a cell that is within one of the ranges referred to by the named range formula (column A or row 1), you will get a circular reference error.
How to lookup value in a matrix with row and column variables
See the section titled "How to make Vlookup work from right to left" to understand the use of Index and Match in this section.
Say you have a matrix of city to city travel distances.
You see these on maps all the time.
A2:A10 = a list of cities.
B1:K1 = another list of cities.
In B2:K10 you have a table of corresponding distances between the city in column A, and the city in row 1.
Now you want to lookup the distance by entering 2 cities in other cells.
You basically want the value from the cell at the intersection of the row and column of the 2 cities.
This is pretty common and fairly simple with a combination of Index and Match.
So given the example table of A1:K10.
A2:A10 is one list of cities.
B1:K10 is another list of cities.
You enter 2 cities in other cells to find the correlating distance between the two.
M1 = Houston.
M2 = New York.
So you want to find the distance from Houston to New York.
Now we’ll say that in the table, Houston is found in A7 and New York is found in G1.
That means that G7 is the value you want to return, because it is at the intersection of the column (G) and row (7).
Your formula would be
=INDEX(A1:K10,MATCH(M1,A1:A10,0),MATCH(M2,A1:K1,0))
Disclaimer:
All macros in this document are mere suggestions. Some have very real potential to cause loss of data. PLEASE make sure you save a backup copy of your file before using them.
How to find the last used row or column #
This is a very common obstacle, and easy to overcome.
Pick a column to determine the last used row #. Say A for my example.
This simulates going to cell A65536, and pressing CTRL + UP.
You now have a variable (LR) that is equal to the last used row # of column A.
You can then use that variable in range references like this.
If you don't know which column will determine the last used row, in other words, sometimes column B has the most data, sometimes column F has the most data.
And the same can be done for the last used column #.
Or if you don’t know which row has the most data.
How to speed up my code
The most common method to speed up code is to turn off Calculation, Events and ScreenUpdating.
Calculation:
Formulas will recalculate every time your macro changes something on a sheet.
If your macro inserts or deletes rows or changes certain values, formulas then recalculate. This greatly slows down your code.
You can turn calculation off, perform some macro code, and then turn calculation back on.
Events:
Worksheet and workbook events can be triggered when your macro makes changes to the sheets.
Any event code you have in the sheet or workbook modules will then run.
You can turn workbook events off, perform some macro code, and then turn events back on.
ScreenUpdating:
When your macro makes changes to a cell or selects a different sheet, that action is updated on the screen in the user interface. That makes it so that the user can see changes the macro makes while it is happening. That really slows things down.
You can turn ScreenUpdating off, perform some macro code, and then turn ScreenUpdating back on.
You can do all those things at once like this:
And reverse it at the end.
Use these methods at your own discretion. Depending on the situation, it may not be appropriate to turn calculation or events off. If your macro depends on the values of formulas, and your macro changes cells that those formulas refer to, then those formulas won't update and your macro will have the wrong value.
You could get around that by turning off calculation, and then just using the calculate command at key points during your macro to make sure you have correct values. It is better to calculate just once or a few times, than it is to calculate every time something changes.
You can do that several ways.
To make the whole book recalculate:
To make just a specific sheet recalculate:
To make just a specific range on a specific sheet recalculate:
Now, the most common reason for slow code is using select and activate.
Most of the time code that uses select and activate comes from using the macro recorder.
The macro recorder is fine, it's a great tool for learning the correct syntax for a certain task. But it does create a lot of unnecessary and inefficient code, particularly select and activate.
So go ahead and use the macro recorder, but once you get the resulting code from the recorder, you can then tweak it to remove the select and activate code.
99% of the time it is not necessary to select or activate an object (range, sheet, textbox, etc) to manipulate it.
Take this small snippet of code that uses Select.
That can all be done in 2 lines without using select.
There are 2 very basic guidelines to converting code that uses select to code that does not use select.
1. You will need to specify sheet names on all range or cell references you use in the code. Simply add Sheets("SheetName") before any and all range or cells reference. Just look at whatever sheet was last selected prior to the line you're modifying, that is the sheet you add to the range reference. This tells the code which sheet you want to work with. For example, if you had this line Sheets("Sheet1").Select in your code, you would look for every range or cells reference beyond that line and add Sheets("Sheet1") prior to it. Say you found Range("A1:A10") in the code after sheet1 had been selected. Change that to Sheets("Sheet1").Range("A1:A10").
2. Anywhere you see the word selection, you can replace that with whatever was previously selected. Paying attention to the last sheet that was selected, and adding that sheet before the range or cells reference.
It can be a very tedious (but not difficult) process to convert code that uses select to code that does not use select.
But it is very well worth it.
How to apply macro to multiple sheets
OK, you’ve got a macro that works great on a specific sheet, but you want it to run on several sheets. Either on all sheets, only a few sheets you specify, or all sheets except a few that you specify.
The first thing you have to do is make sure your code doesn't select sheets or ranges. If it does, follow the instructions of the previous section (How to speed up my code), and eliminate select.
Now you can use this simple loop:
And anywhere in your code that references a sheet name, like Sheets("Sheet1"), replace that with ws.
So for example, you have this small code that works on a specific sheet:
That sets the interior fill color of A1:E10 to red on Sheet1.
To make that code work on all sheets, use a loop like this:
If there are multiple lines referring to the same sheet, you can use a With structure.
That will apply a red fill color for range A1:E10 and yellow fill color for range G1:K10 on all sheets in the book.
To make it apply to only a few sheets that you specify, use an array to hold the sheet names you want it to apply to, and loop through all sheets using that array as criteria to determine which ones to apply the macro code to.
You can also use a Select Case structure:
To make it do all sheets except a specific few you specify, it's exactly the same, just remove the word Not.
Or to use Select Case structure, put the action to perform in the Case Else section.
I protected my sheets but now my macros don't work
This is very easy. You have to know the password of course, duh!
You simply add a line of code at the beginning of your macro to unprotect the sheet, then another line at the end to reprotect it.
You can also use this:
That command basically says that the sheet is protected from users changing things by hand, but allows changes to be made by VBA code.
So you would write something like:
The drawback to this is that setting will not be saved when you save/close/reopen the book.
So when you reopen the book, it is no longer set to UserInterFaceOnly = True. You have to run the command again upon opening the book.
You can do that with a workbook open event.
To do that, press ALT + F11 to open the VBA window.
Find the module named ThisWorkbook.
In that module, put this code.
That of course assumes you want to protect every sheet and that every sheet has the same password. It can be fairly easily modified to accommodate for that though.
How can I name my sheet according to the value in a cell
This is fairly simple. You just have to be aware of certain rules for sheet names.
1. There can’t be a duplicate sheet name.
2. Certain characters are not allowed in a sheet name, like /.
3. The length of the sheet name is limited to 31 characters.
Once you know you have a valid useable sheet name in a cell, say A1 you can use a code like:
If you want this to happen automatically as you type the value in the cell, you will have to use an event code. The following code would be pasted in the sheet module for the sheet you want it applied to. To make sure it goes in the right place, right click on the tab of the sheet you want to apply this to and select view code. Paste the following code right there.
This also has some error handling code in case an invalid name is used in cell A1. It will pop up a message stating an error occurred. That is optional, you can remove the if structure to remove that.
The most common problem renaming a sheet to a cells value is when you’re trying to rename the sheet to a date. Dates contain /, sheet names cannot contain /. So you have to use some method to change the / to another allowed character, most commonly either a space or a hyphen. Like this.
One last common request in this area is you have a list of values in say A1:A10 on Sheet1. You now want to create a set of new sheets using that list for the names of each new sheet.
Again, make sure your list follows the rules for sheet names. Here is a simple code that will do that for you.
Most Common Problems and Solutions.
I have no statistical data to support which questions or problems are asked the most. But after a couple years of reading this forum, it seems fairly obvious what they are.
I've split them up into 2 categories, Formula and VBA.
I think there should be more for the VBA category, but I couldn't think of any more.
Formula:
1. How to eliminate #N/A errors from a Vlookup formula?
2. Vlookup doesn't find the match but I know it's there?
3. How to make Vlookup work from right to left?
4. How to perform a multiple criteria count or sum?
5. How to set rates based on value ranges (1-10 = 10%, 11-20 = 15%, 21-30 = 20%)?
6. How do I nest more than 7 Ifs?
7. How to make a range adjust its size according to how much data is in the range?
8. How to lookup value in a matrix with row and column variables?
VBA:
1. How to find the last used row or column #?
2. How to speed up my code?
3. How to apply macro to multiple sheets?
4. I protected my sheets but now my macros don't work?
5. How can I name my sheet according to the value in a cell?
Formula ISSUES:
How to eliminate #N/A errors from a Vlookup formula.
Bear in mind the same logic in this section can be applied to Hlookup as well as Vlookup, just transposed.
Use this example formula for this section.
=VLOOKUP(A1,B:C, 2, FALSE)
There are 3 reasons for a Vlookup to return #N/A.
1. The value in A1 was not found in column B and false was used in the 4th argument of the formula.
2. The data is not sorted ascending by column B, and true was used in the 4th argument of the formula.
3. The Vlookup formula found a match for A1 in column B. But the resulting cell contains a formula that results in #N/A.
There are also 2 reasons for wanting to eliminate #N/A errors.
1. You expect the errors to occur and just don’t want to see them.
2. You know the match is there, but vlookup is not finding it.
Reason 1. - If you are expecting #N/A errors, but just don't want to see them.
Basically, you know sometimes there won't be a match, and you just don't want to see the #N/A errors. This is wise because other formulas that refer to the vlookup formula will error if the vlookup formula is #N/A.
There are 2 basic methods to eliminate the expected #N/A errors.
Again, this is using the example formula above.
Method 1. Test the Vlookup formula for the error, and return blank (or other value) if it results in an error.
=IF(ISNA(VLOOKUP(A1,B:C,2,FALSE)),"",VLOOKUP(A1,B:C,2,FALSE))
Method 2. Check if the value in A1 exists in column B, if it does, do the Vlookup, if not, return blank (or other value).
=IF(ISNUMBER(MATCH(A1,B:B,0)),VLOOKUP(A1,B:C,2,FALSE),"")
Method 2 is widely considered the more efficient method, while method 1 seems a little easier to understand and implement. Method 1 actually must do the vlookup twice. Where method 2 only has to look in column B for a match, it does not have to return a value and test for #N/A.
Reason 2. - If you think the vlookup should be returning a result, but it’s giving #N/A.
First understand the 4th argument of Vlookup, either true or false (also 1 or 0).
True means Vlookup looks for the closest match, the largest value that is less than or equal to the lookup value. And the data must be sorted ascending by the leftmost column of the array (column B in this example).
False means Vlookup looks for an exact match. And the data does not need to be sorted.
If you omit the 4th argument (leave it blank), true is assumed.
If you use true for the 4th argument, and you receive #N/A errors or unexpected results, the problem is most likely that the data is not sorted ascending.
If you use false as the 4th argument and you are receiving #N/A errors, then that means that an exact match was not found. Make sure the data matches exactly. Spelling counts, but capitalization does not matter.
This is still using the same example formula from above.
Look at the values in both A1 and in column B.
Check the spelling and look for extra spaces or other non visible characters.
Probably the most common problem is "Numbers Stored As Text". Sometimes Excel doesn't see numbers as numbers.
This is especially true when importing or copy/pasting numbers from an external source. Excel will sometimes treat these numbers as text. The best way to tell if that is the problem is to use the Isnumber function.
Again, given the example Vlookup formula above, you would want to test both A1 and column B. So put these formulas anywhere.
=ISNUMBER(A1)
=ISNUMBER(B1)
The Isnumber formula for column B should be filled down to the end of the data.
Those formulas will return true if they are numbers, false if they are "Numbers Stored As Text".
You want them all to be the same, either all true or all false, not a mixture.
It would be preferred if they are all numbers.
There are a few ways to resolve this problem.
1. Paste special – values – add.
Copy any blank cell.
Highlight the range that contains the "Numbers Stored as Text" - Isnumber returned False.
Click edit – paste special – select values – select add – click ok.
2. Data – text to columns.
Highlight the range that contains the "Numbers Stored as Text"
Click data – text to columns, select deliminated – click finish.
3. If column B is either all numbers or all text, not a mix of both, you can adjust your formula to account for "Numbers Stored as Text"
If A1 is a number and column B is "Numbers Stored as Text".
You can write:
=VLOOKUP(A1&"",B:C,2,FALSE)
If A1 is "Number Stored as Text" and column B is numbers
You can write:
=VLOOKUP(A1+0,B:C,2,FALSE)
Vlookup doesn't find the match but I know it's there.
See reason 2 from previous section "How to eliminate #N/A errors from a Vlookup formula".
How to make Vlookup work from right to left
We all know that vlookup reads from left to right. It looks up a value in column A and returns the corresponding value from column B. But very often you need to do the opposite. You need to lookup the value in column B and return the corresponding value from column A.
The short answer is that Vlookup cannot do it, period.
However, you can use a combination of the Index and Match functions to accomplish this. These are one of the most commonly used combinations of functions in Excel.
You first need to understand what each function does individually before you can understand how they work together.
The Index function returns the value from the intersection of the specified row and column within an array.
Here is the basic structure.
=INDEX(array, row #, col #)
Example 1:
=INDEX(A1:D10,5,3)
Example 2:
=INDEX(G10:I30,15,2)
Example 1 would return the value in C5 (the intersection of the 5th row and 3rd column within A1:D10).
Example 2 would return the value in H24 (the intersection of 15th row and 2nd column within G10:I30).
It is important to notice in example 2 that it does not return the 15th row and 2nd column of the sheet (B15). It returns the cell at the intersection of the 15th row and 2nd column relative to the top left cell (G10) of the array (G10:I30). That is cell H24. H is the 2nd column from G, 24 is the 15th row from 10.
The row # and column # arguments in Index are both optional, but at least 1 of the 2 must be used.
You can also use 1 dimensional arrays (either 1 column or 1 row). In this case you would only use the row # or column # argument. If using a 1 column array like A:A, you would use the row #. If using a 1 row array like A1:Z1, you would use the column #.
=INDEX(A:A,23)
This returns the value in A23.
=INDEX(A5:G5,,3) ß Notice the row # argument is omitted.
This would return the value in C5.
The Match function looks up a value in a 1 dimensional array and returns the position # in which it was found.
Example 1:
=MATCH("George",A1:A10,0)
If George was in say A5, this formula would return 5. A5 is the 5th position within A1:A10.
Example 2:
=MATCH("George",E3:M3,0)
If George was in say H3, this formula would return 4. H3 is the 4th position within E3:M3.
Important to notice that match does not necessarily return the row # or column # that the value was found in. It returns the position # relative to the 1st cell in the array.
The 3rd argument in Match is similar to the True/False argument in Vlookup. It determines if the Match formula will find an exact match, or closest match.
0 = Match searches for exact match. Returns #N/A if not found. Data does not need to be sorted.
1 = Match searches for closest match. The largest # that is less than or equal to lookup value. Data must be sorted ascending.
-1 = Match searches for closest match. The smallest # that is greater than or equal to lookup value. Data must be sorted descending.
If omitted, 1 is default.
Now to use Index and Match in combination to simulate Vlookup and read from right to left.
Here is a basic Vlookup formula.
=VLOOKUP(A1,B:C,2,FALSE)
To perform the exact same function with Index and Match.
=INDEX(C:C,MATCH(A1,B:B,0))
Column C is the array for the Index.
Match will find A1 in column B and return the position # in which it was found.
Index will then return that position # from column C.
This is now very powerful because you can lookup in any column, and return from any column.
So take the example Vlookup formula from above. If you wanted to find A1 in column C and return the value from column B, simply swap the two ranges from the example Index Match formula.
=INDEX(B:B,MATCH(A1,C:C,0))
This subject has caused much debate recently on the forum. About weather or not we actually need Vlookup to begin with, since Index Match can do the same thing.
It is widely considered that the Index Match combination is more robust and efficient. However, it is also recognized that Vlookup is a little easier to write, understand and explain/teach.
This topic is discussed in great detail here.
http://www.mrexcel.com/forum/showthread.php?t=322319
How to perform a multiple criteria count or sum.
Very often I see posts that say I have this Sumif formula that works great.
=SUMIF(A:A,"SomeWord",C:C)
It sums column C where column A = "SomeWord".
But I want to add another criteria, to sum column C where column A = "SomeWord" and column B = "AnotherWord".
Sumproduct is a very powerful function that you can use to accomplish this.
Here is a basic structure using the example of column A = "SomeWord" and column B = "AnotherWord".
=SUMPRODUCT(--(A1:A100="SomeWord"),--(B1:B100="AnotherWord"),C1:C100)
That will sum column C where column A = "SomeWord" and column B = "AnotherWord".
It is a very simple formula and works extremely well.
Each section defined by --(range=criteria) is a logical question with a true or false answer.
The -- converts the true/false answer to 1/0. True = 1, false = 0.
The 3rd section C1:C100 is not a question, it only contains a numerical value.
It reads the formula 1 row at a time, and answers each true/false question.
So take row 1 - is A1 = "Someword" and is B1 = "AnotherWord"?
Each question has a true/false answer, converted to 1 or 0.
It then multiplies the 3 sections together, the 2 answers and the numerical value in column C.
The result of that multiplication is the value for row 1.
Then moves on to the next row.
Is A2 = "SomeWord" and is "B2 = "AnotherWord"?
It then multiplies the 3 sections together, the 2 answers and the numerical value in column C.
The result of that multiplication is the value for row 2.
Each row has only 4 possible combinations of answers.
Both true = 1*1*(value of column C).
Both false = 0*0*(value of column C).
True and false = 1*0*(value of column C).
False and true = 0*1*(value of column C).
Each row has only 2 possible values, 1*(value of column C) or 0*(value of column C).
Only if both criteria are true, will the result be 1*(value of column C).
If either question (or both) is false, the resulting value will be 0*(value of column C).
After all questions in all rows are answered and multiplied and values assigned to the row, it then sums the values of each row.
So given a small example
A1 = SomeWord
B1 = AnotherWord
C1 = 25
A2 = SomeWord
B2 = BlahBlah
C2 = 40
This results in (1*1*25) + (1*0*40).
That breaks down to (25) + (0).
Result is 25, only 1 row (row 1) met the criteria of col A = "SomeWord" and col B = "AnotherWord".
IMPORTANT RULES
The ranges cannot be entire column references like A:A, you must use a finite range like A1:A100.
The ranges must be the same size.
You can use cell references for the variables like:
=SUMPRODUCT(--(A1:A100=D1),--(B1:B100=E1),C1:C100)
D1 = SomeWord.
E1 = AnotherWord.
You can make it a conditional count by simply removing the sumrange.
=SUMPRODUCT(--(A1:A100=D1),--(B1:B100=E1))
That will count rows where column A = "SomeWord" and column B = "AnotherWord".
You can also add other functions inside each section of the formula for criteria.
For example, you want to sum column C where column A begins with "X".
=SUMPRODUCT(--(LEFT(A1:A100,1)="X"),C1:C100)
So you can create just about any function or combination of functions in each criteria section of the formula, as long as those functions result in a true or false answer.
How to set rates based on value ranges (1-10 = 10%, 11-20 = 15%, 21-30 = 20%)
A common example of this is for sales commissions.
If my sales for the month were:
0 - 1000 I get 5%
1001 to 1500 I get 7%
1501 to 2000 I get 9%
2001 or more I get 10%
So say A1 is your total sales for the month.
=A1*LOOKUP(A1,{0, 1001, 1501, 2001},{.05, .07, .09, .1})
Make sure the values in {0, 1001, 1501, 2001} are sorted ascending left to right.
It looks for the largest number in {0,1001,1501,2001} that is less than or equal to A1.
It then returns the corresponding value from {.05,.07,.09,.1}.
Then multiplies A1 by that #.
Another way to do it is to use a lookup table on a sheet.
That way you don't have to hard code the values in the formula, and it’s easier to adjust/maintain.
You would put the low end dollar amounts in column B and the corresponding % in column C.
Sort the table ascending by column B.
=A1*LOOKUP(A1,B:C)
It basically looks for the largest number in column B that is less than or equal to A1.
It then returns the corresponding value from column C.
Then multiplies A1 by that #.
That's basically it, pretty straight forward.
How do I nest more than 7 IFs
There's really no straight answer to that. But it is almost always possible to find a different approach to your goal without nesting ifs. But if you must, here is a way to do it.
Split the formula in two.
In one cell (say A1), nest your first 7 ifs, and make it return "" if none of the results are true. This is a very important step. To be sure that the first nested if formula results in "" if none of the if functions are true.
In a second cell (say B1), test A1 for blank. If A1 is not blank, return A1. If A1 is blank continue the nested ifs.
So for example:
In A1:
=IF(D1=1,"A",IF(D1=2,"B",IF(D1=3,"C",IF(D1=4,"D",""))))
In B1:
=IF(A1<>"",A1,IF(D1=5,"E",IF(D1=6,"F",IF(D1=7,"G",IF(D1=8,"H","")))))
But I must strongly recommend searching for alternate methods.
Using the example 8 nested if setup above.
It is much easier to do it like this, similar to the last section of assigning a rate to a commission.
=LOOKUP(D1,{1,2,3,4,5,6,7,8,9},{"A","B","C","D","E","F","G","H",""})
It finds D1 in {1,2,3,4,5,6,7,8,9}, then returns the corresponding value from {"A","B","C","D","E","F","G","H",""}.
The first set of values needs to be sorted alphanumerically from left to right.
It can also be done with lookup table as in previous section.
Column B is the criteria, column C is the results.
Data must be sorted ascending by column B.
=LOOKUP(D1,B:C)
Or to be more exact, use Vlookup:
=VLOOKUP(D1,B:C,2,FALSE)
Using the Vlookup, the data would not need to be sorted.
How to make a range adjust its size according to how much data is in the range.
You have a bunch of formulas referring to 100s of rows of data.
But that data is updated frequently, and the length (or width) of the data is not always the same. Sometimes it’s 500 rows, sometimes it's 750.
You could write your formulas to use the whole column A:A, or a very large part of the column to cover the largest the range would ever be.
However, that is a good way to decrease the performance of your sheet. The larger the range you refer to, the slower the formula will be.
A solution is to use a dynamic named range. One that automatically adjusts its length and/or width based on the size of the data on the sheet.
We'll use an example of data that changes size in length, not width. But the same logic can be applied the other way around. It can also be used for both (varying length and width).
You first need to decide on a specific column to use that will determine how far down the data goes.
And you'll want to decide which row # the data starts on, that is usually pretty consistent.
So for this example, we'll say the data starts in row 1, and col A will determine how far down the data goes.
This formula will return a range object starting in A1 going down to the last cell in column A that is not empty. Provided there are no blanks within the range of column A.
=A1:INDEX(A:A,COUNTA(A:A))
So you could write this basic formula using that.
=SUM(A1:INDEX(A:A,COUNTA(A:A)))
You can then create a named range for that range.
Click insert – name – define.
Type a name like MyRange.
In the "Refers to" box, put:
=$A$1:INDEX($A:$A,COUNTA($A:$A))
It’s important to use absolute references in the named range, things can get weird if you don't.
Now you can use the formula:
=SUM(MyRange)
If there are blanks in the range, it gets a little more complicated. But I've found this to work well for me.
=$A$1:INDEX($A:$A,MATCH(2,1/($A$1:$A$65535<>"")))
The $A$1:$A$65535 part cannot be an entire column ref like A:A, as it is an array formula. I'm not sure why this works in a named range without CTRL + SHIFT + ENTER, but it does.
If you wanted to use that in a cell formula, instead of named range, you would have to enter it with CTRL + SHIFT + ENTER to make it an array formula.
Like this:
{=SUM($A$1:INDEX($A:$A,MATCH(2,1/($A$1:$A$65535<>""))))}
Now these can be transposed to row ranges instead of column ranges.
=$A$1:INDEX($1:$1,COUNTA($1:$1))
And
=$A$1:INDEX($1:$1,MATCH(2,1/($A$1:$IU$1<>"")))
Now, to do a 2 dimensional dynamic range (multiple columns and multiple rows), you have to use the Offset function.
The basic structure is:
=OFFSET($A$1,0,0,# of rows,# of columns))
We'll again use column A to determine # of rows, and row 1 to determine number of columns.
This will give a range from A1 to the last column and last row.
=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))
That also again, depends on having no blanks in column A or row 1.
If there are blanks, you can adapt the match part from above.
=OFFSET($A$1,0,0,MATCH(2,1/($A$1:$A$65535<>"")),MATCH(2,1/($A$1:$IU$1<>"")))
When using these formulas as named ranges, be careful where you put your formula =SUM(MyRange). If you put that formula in a cell that is within one of the ranges referred to by the named range formula (column A or row 1), you will get a circular reference error.
How to lookup value in a matrix with row and column variables
See the section titled "How to make Vlookup work from right to left" to understand the use of Index and Match in this section.
Say you have a matrix of city to city travel distances.
You see these on maps all the time.
A2:A10 = a list of cities.
B1:K1 = another list of cities.
In B2:K10 you have a table of corresponding distances between the city in column A, and the city in row 1.
Now you want to lookup the distance by entering 2 cities in other cells.
You basically want the value from the cell at the intersection of the row and column of the 2 cities.
This is pretty common and fairly simple with a combination of Index and Match.
So given the example table of A1:K10.
A2:A10 is one list of cities.
B1:K10 is another list of cities.
You enter 2 cities in other cells to find the correlating distance between the two.
M1 = Houston.
M2 = New York.
So you want to find the distance from Houston to New York.
Now we’ll say that in the table, Houston is found in A7 and New York is found in G1.
That means that G7 is the value you want to return, because it is at the intersection of the column (G) and row (7).
Your formula would be
=INDEX(A1:K10,MATCH(M1,A1:A10,0),MATCH(M2,A1:K1,0))
VBA ISSUES:
Disclaimer:
All macros in this document are mere suggestions. Some have very real potential to cause loss of data. PLEASE make sure you save a backup copy of your file before using them.
How to find the last used row or column #
This is a very common obstacle, and easy to overcome.
Pick a column to determine the last used row #. Say A for my example.
Rich (BB code):
LR = Cells(Rows.Count, "A").End(xlUp).RowYou now have a variable (LR) that is equal to the last used row # of column A.
You can then use that variable in range references like this.
Rich (BB code):
Range("A1:A" & LR)
Rich (BB code):
LR = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count – 1
Rich (BB code):
LC = Cells(1, Columns.Count).End(xlToLeft).Column
Rich (BB code):
LC = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count - 1How to speed up my code
The most common method to speed up code is to turn off Calculation, Events and ScreenUpdating.
Calculation:
Formulas will recalculate every time your macro changes something on a sheet.
If your macro inserts or deletes rows or changes certain values, formulas then recalculate. This greatly slows down your code.
You can turn calculation off, perform some macro code, and then turn calculation back on.
Rich (BB code):
Application.Calculation = xlCalculationManual
‘Put your code here
Application.Calculation = xlCalculationAutomaticWorksheet and workbook events can be triggered when your macro makes changes to the sheets.
Any event code you have in the sheet or workbook modules will then run.
You can turn workbook events off, perform some macro code, and then turn events back on.
Rich (BB code):
Application.EnableEvents = False
‘Put your code here
Application.EnableEvents = TrueWhen your macro makes changes to a cell or selects a different sheet, that action is updated on the screen in the user interface. That makes it so that the user can see changes the macro makes while it is happening. That really slows things down.
You can turn ScreenUpdating off, perform some macro code, and then turn ScreenUpdating back on.
Rich (BB code):
Application.ScreenUpdating = False
‘Put your code here
Application.ScreenUpdating = True
Rich (BB code):
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
Rich (BB code):
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End WithYou could get around that by turning off calculation, and then just using the calculate command at key points during your macro to make sure you have correct values. It is better to calculate just once or a few times, than it is to calculate every time something changes.
You can do that several ways.
To make the whole book recalculate:
Rich (BB code):
Application.Calculate
Rich (BB code):
Sheets("Sheet1").Calculate
Rich (BB code):
Sheets("Sheet1").Range("A1:A10").CalculateNow, the most common reason for slow code is using select and activate.
Most of the time code that uses select and activate comes from using the macro recorder.
The macro recorder is fine, it's a great tool for learning the correct syntax for a certain task. But it does create a lot of unnecessary and inefficient code, particularly select and activate.
So go ahead and use the macro recorder, but once you get the resulting code from the recorder, you can then tweak it to remove the select and activate code.
99% of the time it is not necessary to select or activate an object (range, sheet, textbox, etc) to manipulate it.
Take this small snippet of code that uses Select.
Rich (BB code):
Sheets("Sheet2").Select
Range("A1:A10").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1:A10").Select
Selection.PasteSpecial xlPasteValues
Rich (BB code):
Sheets("Sheet2").Range("A1:A10").Copy
Sheets("Sheet1").Range("A1:A10").PasteSpecial xlPasteValues1. You will need to specify sheet names on all range or cell references you use in the code. Simply add Sheets("SheetName") before any and all range or cells reference. Just look at whatever sheet was last selected prior to the line you're modifying, that is the sheet you add to the range reference. This tells the code which sheet you want to work with. For example, if you had this line Sheets("Sheet1").Select in your code, you would look for every range or cells reference beyond that line and add Sheets("Sheet1") prior to it. Say you found Range("A1:A10") in the code after sheet1 had been selected. Change that to Sheets("Sheet1").Range("A1:A10").
2. Anywhere you see the word selection, you can replace that with whatever was previously selected. Paying attention to the last sheet that was selected, and adding that sheet before the range or cells reference.
It can be a very tedious (but not difficult) process to convert code that uses select to code that does not use select.
But it is very well worth it.
How to apply macro to multiple sheets
OK, you’ve got a macro that works great on a specific sheet, but you want it to run on several sheets. Either on all sheets, only a few sheets you specify, or all sheets except a few that you specify.
The first thing you have to do is make sure your code doesn't select sheets or ranges. If it does, follow the instructions of the previous section (How to speed up my code), and eliminate select.
Now you can use this simple loop:
Rich (BB code):
For Each ws in Sheets
'The rest of your code goes here
Next wsSo for example, you have this small code that works on a specific sheet:
Rich (BB code):
Sheets("Sheet1").Range("A1:E10").Interior.ColorIndex = 3To make that code work on all sheets, use a loop like this:
Rich (BB code):
For Each ws In Sheets
ws.Range("A1:E10").Interior.ColorIndex = 3
Next ws
Rich (BB code):
For Each ws In Sheets
With ws
.Range("A1:E10").Interior.ColorIndex = 3
.Range("G1:K10").Interior.ColorIndex = 6
End With
Next wsTo make it apply to only a few sheets that you specify, use an array to hold the sheet names you want it to apply to, and loop through all sheets using that array as criteria to determine which ones to apply the macro code to.
Rich (BB code):
MySheets = Array("ThisOne", "ThisOneToo") 'Sheets you want the macro to run on go here.
For Each ws In Sheets
X = Application.Match(ws.Name, MySheets, 0)
If Not IsError(X) Then
With ws
.Range("A1:E10").Interior.ColorIndex = 3
.Range("G1:K10").Interior.ColorIndex = 6
End With
End If
Next ws
Rich (BB code):
For Each ws In Sheets
Select Case ws.Name
Case "ThisOne", "ThisOneToo" 'Sheets you want the macro to run on go here.
With ws
.Range("A1:E10").Interior.ColorIndex = 3
.Range("G1:K10").Interior.ColorIndex = 6
End With
Case Else
'Do Nothing
End Select
Next ws
Rich (BB code):
MySheets = Array("NotThisOne", "NorThisOne") 'Sheets you do not want the macro to run on go here.
For Each ws In Sheets
X = Application.Match(ws.Name, MySheets, 0)
If IsError(X) Then
With ws
.Range("A1:E10").Interior.ColorIndex = 3
.Range("G1:K10").Interior.ColorIndex = 6
End With
End If
Next ws
Rich (BB code):
For Each ws In Sheets
Select Case ws.Name
Case "NotThisOne", "NorThisOne" 'Sheets you do not want the macro to run on go here.
'Do Nothing
Case Else
With ws
.Range("A1:E10").Interior.ColorIndex = 3
.Range("G1:K10").Interior.ColorIndex = 6
End With
End Select
Next wsI protected my sheets but now my macros don't work
This is very easy. You have to know the password of course, duh!
You simply add a line of code at the beginning of your macro to unprotect the sheet, then another line at the end to reprotect it.
Rich (BB code):
Sheets("Sheet1").Unprotect "PasswordGoesHere"
‘The rest of your code here
Sheets("Sheet1").Protect "PasswordGoesHere"You can also use this:
Rich (BB code):
UserInterFaceOnly = TrueSo you would write something like:
Rich (BB code):
Sheets("Sheet1").Protect Password:="PasswordHere", UserInterFaceOnly:=TrueSo when you reopen the book, it is no longer set to UserInterFaceOnly = True. You have to run the command again upon opening the book.
You can do that with a workbook open event.
To do that, press ALT + F11 to open the VBA window.
Find the module named ThisWorkbook.
In that module, put this code.
Rich (BB code):
Private Sub Workbook_Open()
For Each ws In Sheets
ws.Protect Password:="PasswordHere", UserInterFaceOnly:=True
Next ws
End SubThat of course assumes you want to protect every sheet and that every sheet has the same password. It can be fairly easily modified to accommodate for that though.
How can I name my sheet according to the value in a cell
This is fairly simple. You just have to be aware of certain rules for sheet names.
1. There can’t be a duplicate sheet name.
2. Certain characters are not allowed in a sheet name, like /.
3. The length of the sheet name is limited to 31 characters.
Once you know you have a valid useable sheet name in a cell, say A1 you can use a code like:
Rich (BB code):
ActiveSheet.Name = ActiveSheet.Range("A1").Value
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0,0) <> "A1" Then Exit Sub
On Error Resume Next
Me.Name = Me.Range("A1").Value
On Error GoTo 0
If Me.Name <> Me.Range("A1").Value Then
MsgBox "An error occurred trying to rename the sheet to " & Me.Range("A1").Value _
& Chr(10) & "Either it already exists, has too many characters or has invalid characters"
End If
End SubThis also has some error handling code in case an invalid name is used in cell A1. It will pop up a message stating an error occurred. That is optional, you can remove the if structure to remove that.
The most common problem renaming a sheet to a cells value is when you’re trying to rename the sheet to a date. Dates contain /, sheet names cannot contain /. So you have to use some method to change the / to another allowed character, most commonly either a space or a hyphen. Like this.
Rich (BB code):
Me.Name = Format(Me.Range("A1").Value, "mm-dd-yyyy")Again, make sure your list follows the rules for sheet names. Here is a simple code that will do that for you.
Rich (BB code):
Application.DisplayAlerts = False
With Sheets("Sheet1")
LR = .Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LR
Sheets.Add After:=Sheets(Sheets.Count)
On Error Resume Next
ActiveSheet.Name = .Range("A" & i).Value
On Error GoTo 0
If ActiveSheet.Name <> .Range("A" & i).Value Then
ActiveSheet.Delete
MsgBox "An error occurred creating the sheet " & .Range("A" & i).Value _
& Chr(10) & "The sheet was not created"
End If
Next i
End With
Application.DisplayAlerts = True
Colin Legg
MrExcel MVP
- Joined
- Feb 28, 2008
- Messages
- 3,507
- Office Version
- 365
- Platform
- Windows
That's some really good work, Jonmo - it must have taken you absolutely ages. It's a fine effort so congrats. 
I've just got a couple of nitpicks for you on a couple of the posts. They're all meant to be entirely constructive, and I tried to balance in the level of the target audience when I was reading the material. I haven't had the time to go through everything with a fine toothcomb so apologies if I missed something.
How to perform a multiple criteria count or sum.
I know that you're trying to keep it simple so that you can explain how SUMPRODUCT works, but since Excel 2007 has introduced the new "IFS" functions, I'm wondering if a different example for using SUMPRODUCT would be better, or at least the current example could be qualified as Excel 2003 or earlier? As well as SUMIFS and COUNTIFS, a pivot table or database functions are options - the title of the post is "How to perform a multiple criteria count or sum" and not "How to perform a multiple criteria count or sum with Sumproduct", after all? I also believe that Mr Excel has an array formula primer - would it be worth including a link?
For the important rules - again, in Excel 2007, entire columns are allowed as arguments in SUMPRODUCT. [I know you are aware of this].
A final thought, and I know that this is going to spark a lot of contention, the relative readability / efficiency / understandability advantages/disadvatages of using:
How to apply macro to multiple sheets
This nitpick applies to other similar scenarios in the examples. I'm not keen on the use of the sheets collection here:
First suggestion would be to include the ws declaration in the example (encouraging people to declare their variables). Next suggestion is to loop through the worksheets collection. Otherwise, when using the sheets collection, if it hits a chart sheet then the range property will kick out an error - this might confuse people.
If the Sheets collection is the requirement for the code as per the title of the post, then I think that, at the very least, defensive coding should be included to allow for chart sheets, and a brief explanation as to why the ws variable is declared as an object....
Well, that's it from me. Kudos again on making such a tremendous effort.
HTH,
Colin
I've just got a couple of nitpicks for you on a couple of the posts. They're all meant to be entirely constructive, and I tried to balance in the level of the target audience when I was reading the material. I haven't had the time to go through everything with a fine toothcomb so apologies if I missed something.
How to perform a multiple criteria count or sum.
I know that you're trying to keep it simple so that you can explain how SUMPRODUCT works, but since Excel 2007 has introduced the new "IFS" functions, I'm wondering if a different example for using SUMPRODUCT would be better, or at least the current example could be qualified as Excel 2003 or earlier? As well as SUMIFS and COUNTIFS, a pivot table or database functions are options - the title of the post is "How to perform a multiple criteria count or sum" and not "How to perform a multiple criteria count or sum with Sumproduct", after all? I also believe that Mr Excel has an array formula primer - would it be worth including a link?
For the important rules - again, in Excel 2007, entire columns are allowed as arguments in SUMPRODUCT. [I know you are aware of this].
A final thought, and I know that this is going to spark a lot of contention, the relative readability / efficiency / understandability advantages/disadvatages of using:
as opposed to this:
might be worth some sort of debate. I guess it ultimately depends on the target audience and what people at MrExcel consider to be best practice. I always used to use the former, but then Nate convinced me otherwise.... So which construction does Mr Excel advocate, the one that's more efficient or the one that's easier to understand, and which one is the most appropriate here? Not for me to say.... but it is a valid question!
How to apply macro to multiple sheets
This nitpick applies to other similar scenarios in the examples. I'm not keen on the use of the sheets collection here:
Code:
For Each ws In Sheets
With ws
.Range("A1:E10").Interior.ColorIndex = 3
.Range("G1:K10").Interior.ColorIndex = 6
End With
Next ws
Code:
Dim ws as Worksheet
For Each ws In Worksheets
With ws
.Range("A1:E10").Interior.ColorIndex = 3
.Range("G1:K10").Interior.ColorIndex = 6
End With
Next ws
Code:
Sub foo()
Dim ws As Object
For Each ws In Sheets
If TypeOf ws Is Worksheet Then
'or alternatively:
'If TypeName(ws) = "Worksheet" Then
.Range("A1:E10").Interior.ColorIndex = 3
.Range("G1:K10").Interior.ColorIndex = 6
End If
Next ws
End SubWell, that's it from me. Kudos again on making such a tremendous effort.
HTH,
Colin
Colin Legg
MrExcel MVP
- Joined
- Feb 28, 2008
- Messages
- 3,507
- Office Version
- 365
- Platform
- Windows
Typo on the last code sample in that post....missed the With/End With block:
Code:
Dim ws As Object
For Each ws In Sheets
If TypeOf ws Is Worksheet Then
'or alternatively:
'If TypeName(ws) = "Worksheet" Then
With ws
.Range("A1:E10").Interior.ColorIndex = 3
.Range("G1:K10").Interior.ColorIndex = 6
End With
End If
Next ws
