Disregard whether field has caps in it

Lball

Board Regular
Joined
Oct 4, 2005
Messages
197
Hello everyone,

How do i get my macro to NOT care if cell has caps in it while using an if statement. Here is a bit of my code:
Code:
If Cells(myRow, "H") = "IndyMac" Then
        With Range(Cells(myRow, 2), Cells(myRow, 10))
            .Copy Sheets("INDYMAC").Cells(Rows.Count, "B").End(xlUp).Offset(1)
            .Delete xlUp
        End With
Else........(Blah blah blah)

If my cell in (myRow, "H") has INDYMAC or indymac or Indymac, then the macro doesn't do what i want it to. Is there a way to tell the macro to just look at the letters instead of which letters are caps? Or do i have to add additional If statements for other possibilities?

Thanks in advance for any help!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,299
Office Version
  1. 365
Platform
  1. Windows
There are various ways to do this.

One way is to put this at the top of the module.
Code:
Option Compare Text
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Another way is like this:

Code:
If UCase(Cells(myRow, "H")) = "INDYMAC"
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Hi:
Change your first line
Code:
If Cells(myRow, "H") = "IndyMac"
to
Code:
If UCase(Cells(myRow, "H")) = "INDYMAC"

HTH
lenze
 

Lball

Board Regular
Joined
Oct 4, 2005
Messages
197

ADVERTISEMENT

Adding that line alone creates a compile error, "Invalid Inside Procedure"
 

Lball

Board Regular
Joined
Oct 4, 2005
Messages
197

ADVERTISEMENT

nevermind i forgot to capitalize "INDYMAC" Its working now, THANKS!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,299
Office Version
  1. 365
Platform
  1. Windows
That line should go at the top of the module, not within any procedure.

How did the UCase not work?

What exactly did you try?
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Since my friend HOTPEPPER and I BOTH gave the same response, I must doubt how you have implemented it. Can you provide details of EXACTLY what is happening?

lenze
 

Lball

Board Regular
Joined
Oct 4, 2005
Messages
197
In case you missed my earlier post.....it was my error i forgot to capitalize INDYMAC in the if statement after i added ucase. Its working now.

As for Option Compare Text, i added that line right after my Sub line and it came back with the compile error that i mentioned. NOt concerned with it now cause the other suggestion worked.

THank you all for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,648
Messages
5,660,095
Members
418,549
Latest member
malmattos

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top