Most common question/answer

[Jonmo1]

OK, here's my current list of common problem/solution

I have 8 formula issues and 5 VBA issues.

Any more suggestions?

I've shyed away from talking about Date/Time and Conditional Formatting issues. Those are more like general discussions and may not be appropriate for a top 10 type of article..

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 assign a different rate 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 dynamically adjust 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?

 

[Jonmo1]

OK, after some rewrites and several "Once Overs" for spelling/grammer. Here we go.

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 2 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 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.

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.

That means there was no match. 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. You can adjust your formula to account for "Numbers Stored as Text"; If column B is either all numbers or all text, not a mix of both.

If A1 is a number and column B is "Numbers Stored as Text"

=VLOOKUP(A1&"",B:C,2,FALSE)

If A1 is "Number Stored as Text" and column B is numbers

=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 15th row and 2nd column relative to the top left cell (G10) of the array (H24).

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 (or both) question(s) 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)
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"






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 is 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 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.

LR = Cells(Rows.Count, "A").End(xlUp).Row

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 in range references like this

Range("A1:A" & LR)

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...

LR = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count – 1

And the same can be done for the last used column #

LC = Cells(1, Columns.Count).End(xlToLeft).Column
 
Or if you don’t know which row has the most data
 
LC = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count - 1

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, then turn calculation back on.

Application.Calculation = xlCalculationManual
 
Put your code here
 
Application.Calculation = xlCalculationAutomatic

Events:
Worksheet and workbook events can be triggered when your macro makes changes to the sheets.
Any code you have in the event modules will then run.

You can turn workbook events off, perform some macro code, then turn events back on

Application.EnableEvents = False
 
Put your code here
 
Application.EnableEvents = True

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, then turn ScreenUpdating back on.

Application.ScreenUpdating = False
 
Put your code here
 
Application.ScreenUpdating = True

You can do all those things at once like this

With Application
  .ScreenUpdating = False
  .EnableEvents = False
  .Calculation = xlCalculationManual
End With

And reverse it at the end.

With Application
  .ScreenUpdating = True
  .EnableEvents = True
  .Calculation = xlCalculationAutomatic
End With

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
Application.Calculate
 
To make just a specific sheet recalculate
Sheets("Sheet1").Calculate
 
To make just a specific range on a specific sheet recalculate
Sheets("Sheet1").Range("A1:A10")Calculate

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

Sheets("Sheet2").Select
Range("A1:A10").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1:A10").Select
Selection.PasteSpecial xlPasteValues

That can all be done in 2 lines without using select

Sheets("Sheet2").Range("A1:A10").Copy
Sheets("Sheet1").Range("A1:A10").PasteSpecial xlPasteValues

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. You would 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

For Each ws in Sheets
  'The rest of your code goes here
Next ws

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

Sheets("Sheet1").Range("A1:E10").Interior.ColorIndex = 3

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

For Each ws In Sheets
  ws.Range("A1:E10").Interior.ColorIndex = 3
Next ws

That will apply a red fill color for range A1:E10 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.

MySheets = Array("ThisOne", "ThisOneToo") 'All 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
      ws.Range("A1:E10").Interior.ColorIndex = 3
  End If
Next ws

To make it do all sheets except a specific few you specify, it's exactly the same, just remove the word Not.

MySheets = Array("NotThisOne", "NorThisOne") 'All sheets you DO NOT want the macro to run on here.
For Each ws In Sheets
  X = Application.Match(ws.Name, MySheets, 0)
  If IsError(X) Then
      ws.Range("A1:E10").Interior.ColorIndex = 3
  End If
Next ws

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.

Sheets("Sheet1").Unprotect "PasswordGoesHere"
  The rest of your code here
Sheets("Sheet1").Protect "PasswordGoesHere"

You can also use this

UserInterFaceOnly = True

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

Sheets("Sheet1").Protect Password:="PasswordHere", UserInterFaceOnly:=True

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

Private Sub Workbook_Open()
For Each ws In Sheets
  ws.Protect Password:="PasswordHere", UserInterFaceOnly:=True
Next ws
End Sub

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

ActiveSheet.Name = ActiveSheet.Range("A1").Value

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.

Private Sub Worksheet_Change(ByVal Target As Range)
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 Sub

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.

Me.Name = Format(Me.Range("A1").Value, "mm-dd-yyyy")

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.

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

 

[xyzabc198]

Couldn't you make it a bit longer? A bit more detailed?

 

[Norie]

jonmo

A couple of points.

1 Why not illustrate the use of With when looping through sheets?

2 Why not use Select Case instead of relying on Match returning an error?

 

[schielrn]

This is very nice Jonmo, just some little notes on some things. The find last column bit right before How to speed up my code could probably be broken into 2 code tags.

One other thing, there are many places where you have comments about the code or place code here within code tags, you may want to put a ' in front of them just so they are commented out if the user leaves it in the code.

I really like this and it will be nice to point people with these common questions to one place or thread depending on how it is set up.

 

[Yokiboha]

1. How to eliminate #N/A errors from a Vlookup formula?

One of the most common problems I encoounter is people forgetting that the lookup range needs to be sorted. Not a major issue but might be worth a reminder

 

[Jonmo1]

Couldn't you make it a bit longer? A bit more detailed?

Really??

Why not illustrate the use of With when looping through sheets?

Hadn't thought of that, I suppose I didn't because the With Structure isn't really part of what the common problem was...

Why not use Select Case instead of relying on Match returning an error?

I like the Array / Match method better because it's easy to make 1 code that you can save. Copy paste it for future use. And it's very easy to toggle the function of the code between
"Do These Few Sheets I Specify"
and
"Do ALL sheets EXCEPT These Few I Specify"

All you have to do is add or remove the word NOT to toggle it.

This is very nice Jonmo, just some little notes on some things. The find last column bit right before How to speed up my code could probably be broken into 2 code tags.

One other thing, there are many places where you have comments about the code or place code here within code tags, you may want to put a ' in front of them just so they are commented out if the user leaves it in the code.

Thanks, good points too. Taken.

1. How to eliminate #N/A errors from a Vlookup formula?

One of the most common problems I encoounter is people forgetting that the lookup range needs to be sorted. Not a major issue but might be worth a reminder

Another excellent point. Thanks. I forgot about that. Probably because I almost NEVER use the TRUE argument in vlookup. If you're going to use TRUE in Vlookup, you might as well just use Lookup instead. And it doesn't always return #N/A in that situation. Usually you just get unexpected results.
But I will definately add that in.

 

[Norie]

Hadn't thought of that, I suppose I didn't because the With Structure isn't really part of what the common problem was...

I think it could be.

Remember all the times you've seen this sort of question:

'I have code that loops through all the worksheets but it only works on the first one.

Now I know that you could fully qualify all references within the loop but using With would be far easier.

I like the Array / Match method better because it's easy to make 1 code that you can save. Copy paste it for future use. And it's very easy to toggle the function of the code between
"Do These Few Sheets I Specify"
and
"Do ALL sheets EXCEPT These Few I Specify"

All you have to do is add or remove the word NOT to toggle it.

I don't know what you mean by make 1 code that you can save.

Personally I avoid using worksheet functions in VBA if there is an alternative.

Also I avoid using the detection of errors to do things.

I'm sure it works but I would still stick with Select Case, which can also be easily changed.

 

[Jonmo1]

Updated after some good input.​

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 a, 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.

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 A 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 used false as the 4th argument, and you receive #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. You can adjust your formula to account for "Numbers Stored as Text"; If column B is either all numbers or all text, not a mix of both.

If A1 is a number and column B is "Numbers Stored as Text"

=VLOOKUP(A1&"",B:C,2,FALSE)

If A1 is "Number Stored as Text" and column B is numbers

=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 15th row and 2nd column relative to the top left cell (G10) of the array (H24).

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 (or both) question(s) 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)
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"






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 is 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 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.

LR = Cells(Rows.Count, "A").End(xlUp).Row

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 in range references like this

Range("A1:A" & LR)

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...

LR = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count – 1

And the same can be done for the last used column #

LC = Cells(1, Columns.Count).End(xlToLeft).Column

Or if you don’t know which row has the most data

LC = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count - 1

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, then turn calculation back on.

Application.Calculation = xlCalculationManual
‘Put your code here
Application.Calculation = xlCalculationAutomatic

Events:
Worksheet and workbook events can be triggered when your macro makes changes to the sheets.
Any code you have in the event modules will then run.

You can turn workbook events off, perform some macro code, then turn events back on

Application.EnableEvents = False
‘Put your code here
Application.EnableEvents = True

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, then turn ScreenUpdating back on.

Application.ScreenUpdating = False
‘Put your code here
Application.ScreenUpdating = True

You can do all those things at once like this

With Application
   .ScreenUpdating = False
   .EnableEvents = False
   .Calculation = xlCalculationManual
End With

And reverse it at the end.

With Application
   .ScreenUpdating = True
   .EnableEvents = True
   .Calculation = xlCalculationAutomatic
End With

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

Application.Calculate

To make just a specific sheet recalculate

Sheets("Sheet1").Calculate

To make just a specific range on a specific sheet recalculate

Sheets("Sheet1").Range("A1:A10")Calculate

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

Sheets("Sheet2").Select
Range("A1:A10").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1:A10").Select
Selection.PasteSpecial xlPasteValues

That can all be done in 2 lines without using select

Sheets("Sheet2").Range("A1:A10").Copy
Sheets("Sheet1").Range("A1:A10").PasteSpecial xlPasteValues

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. You would 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

For Each ws in Sheets
   'The rest of your code goes here
Next ws

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

Sheets("Sheet1").Range("A1:E10").Interior.ColorIndex = 3

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

For Each ws In Sheets
   ws.Range("A1:E10").Interior.ColorIndex = 3
Next ws

If there are multiple lines referring to the same sheet, you can use a With structure.

For Each ws In Sheets
   With ws
       .Range("A1:E10").Interior.ColorIndex = 3
       .Range("G1:K10").Interior.ColorIndex = 6
   End With
Next ws

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.

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

You can also use a Select Case structure

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

To make it do all sheets except a specific few you specify, it's exactly the same, just remove the word Not.

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

Or to use Select Case structure, put the action to perform in the Case Else section.

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 ws

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.

Sheets("Sheet1").Unprotect "PasswordGoesHere"
   ‘The rest of your code here
Sheets("Sheet1").Protect "PasswordGoesHere"

You can also use this

UserInterFaceOnly = True

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

Sheets("Sheet1").Protect Password:="PasswordHere", UserInterFaceOnly:=True

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

Private Sub Workbook_Open()
For Each ws In Sheets
   ws.Protect Password:="PasswordHere", UserInterFaceOnly:=True
Next ws
End Sub

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

ActiveSheet.Name = ActiveSheet.Range("A1").Value

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.

Private Sub Worksheet_Change(ByVal Target As Range)
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 Sub

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.

Me.Name = Format(Me.Range("A1").Value, "mm-dd-yyyy")

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.

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

 

[schielrn]

This looks really good. Does anyone know how this is going to be presetned yet? Will it be in its own forum like the Hall of Fame with a thread for each? I was just wondering what might be the best lay out for it?