Making This Macro Non Case-sensitive
Dec 17, 2007
in making this macro non case-sensitive. So when a user searches a company name in the worksheet they do not require to type in the company name exactly as it appears.
For example; when searching Microsoft they can type "microsoft" and the macro would take the user to Microsoft.
I would also like to know if it is possible to add a feature that keeps the search dialogue open so the user can search the next possible match. If the user was to type in "mirco" and the search would show the user any company name with the word micro in it.
Sub Button3_Click()
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer
View 9 Replies
ADVERTISEMENT
Jun 8, 2009
I have been battling with excel for weeks now trying to get some data to display the way I want it to.
The plan is to enter (into individual cells) some letters, some lower case, some upper case ie, A A a A a C d. Some of these will be displayed together in another cell like this: AAaA, AaCd. My problem is that I want them all to be displayed Upper case letters first, lower case second like this: AAAa, ACad. No amount of my inexperienced fumbling with excel's tools has worked so far.
I am playing around with a table for genetics so if my results in a cell are aAbBCcdD, I need it to display: AaBbCcDd. Is it possible?
View 14 Replies
View Related
Jul 1, 2006
I have a spreadsheet which I use to count the occurrences of a value against a range. Basically if the value is uppercase I count as 1 and for lowercase 0.5
This spreadsheet is used as a holiday chart and I have previously used the formula below however, for some reason it no longer works. Not even if I change the lookup value.
The exact function takes a single cell as its value but I have a spreadsheet which allows a range. Although I cannot change it….
=SUM(IF(EXACT(B1:AF99, "LB"),1))+SUM(IF(EXACT(B1:AF99, "lb"),0.5))
View 3 Replies
View Related
Nov 2, 2006
is it possible to make a SUBSTITUTE finction non- case sensitive?
For example I want to replace all letters "e" and "E" in a cell.
View 3 Replies
View Related
Aug 12, 2007
I have recently taken on the task of creating a large protected program (without macros) that automatically updates a PowerPoint presentation. I created corresponding workbooks for each sheet in the original (master) workbook. The corresponding workbooks are then used to paste object into the power point presentation. The only problem is it will not update format (text color) changes into the corresponding workbook. I have solved half the problem with conditional formatting, but I want to conditionally format the color of text to two different colors blue if Aa and Red if all CAPS. how to differentiate between Standard word and All CAPS in the conditional formatting.
View 7 Replies
View Related
Feb 25, 2008
I need a macro that will format a a column based on content that is case sensitive. Example, a cell that contains a capital 'P' will be shaded gray with red text where a cell that contains a lower case 'p' will not change format.
View 3 Replies
View Related
Sep 12, 2009
Is it possible to have this type of case sensitive sorting (first all words beginning with a capital later and then all cells beginning with a lowercase letter):
A
B
C
D
E
F
G
a
b
c
d
e
f
g
View 4 Replies
View Related
Jan 17, 2014
I've 2 lists that contain similar info but 1 of the lists holds info I need to join.
I've a unique case sensitive id that I'd like to use to join the lists.
I usually use VLOOKUP but it is case insensitive and I cannot understand how to make it case sensitive.
The 2 lists are in 2 separate worksheets as they each hold a lot of data.
View 3 Replies
View Related
Aug 15, 2007
I have a user form to get some input from the user and want to make sure that in some textboxes user should be able enter only text i.e A to Z or a-z no numbers or special charecters.
Private Sub CommandButton1_Click()
Dim RegEx As Object
Dim Strng As String
Strng = CStr(Me.TextBox1.Value)
Set RegEx = CreateObject("vbscript.regexp")
With RegEx
.Pattern = "^[A-Z]{2}/d+/d{2}$"
If Not .test(Strng) Then MsgBox "Invalid Format: TextBox1"
End With
Set RegEx = Nothing
End Sub ..............................
View 9 Replies
View Related
May 22, 2013
Suppose I have some data in a column like below
Blue
Red
Green
green
red
RED
BLUE
I need a macro or excel formula so that it will remove duplicate products- case sensitive(removing all duplicate prod using using excel,that i know.) i.e. it should not consider Blue BLUE as duplicate. only consider Blue Blue as duplicate.
So the macro should work same as data -> remove duplicate function already exists in excel, except it will be case sensitive..
View 7 Replies
View Related
Mar 2, 2012
I am using the this formula to look up data using 0 to show not found. VLOOKUP is acting case sensitive. The Master Array data is in caps and the input is in lower case. There are no spurious spaces. Here are the results:
Code Name
0 River Branch Foundation = not found
X RIVER BRANCH FOUNDATION = found - in array as caps
The formula:
=IF(ISNA(VLOOKUP(B2,'[Master Array - 2012.xlsx]Master Array'!$A$2:$B$3053,2,FALSE)),0,VLOOKUP(B2,'[Master Array - 2012.xlsx]Master Array'!$A$2:$B$3054,2,FALSE))
View 4 Replies
View Related
Aug 25, 2013
I have an issue with case sensitive while creating forms in Excel VBA. When i compared the text "EXCEL" and "excel" it showing as both the words are not equal/same.
Is there any code to compare the words/inputs without case sensitive.
View 2 Replies
View Related
Dec 8, 2013
I'm looking for a way of keeping case sensitive data in a range of cells, before using Data Consolidate, which when merged afterwards, Consolidate removes the case sensitivity and combines the quantities into one.
Check out this simple table as an example >
A
B
C
D
E
F
G
H
1
TEXT
QTY
TEXT
QTY
TEXT
QTY
TEXT
QTY
2
and
3
and
2
and
10
and
5
[Code] .........
Cells A1-B7 and C1-D7 are two sets of original data, before consolidation.
Cells E1-F4 are the result of applying a Data Consolidate operation to the A1-B7 and C1-D7 ranges - note how the merged result ignores the case sensitive condition in the original ranges.
Cells G1-H7 is the post-consolidation result that I'm looking for, where the original text case is maintained.
View 3 Replies
View Related
Nov 13, 2008
i am trying to get the following function to work
i want it to look at 2 cells, depending on the number in the second cell i want it to multiply the first sell by a specific number.
for exaple (Below) function in a3 looks at a2 sees it is a 2 and multiplies a1 by 1.09 shown in the code below
A1=load
A2=factor
A1 A2 A3
100 2 Function
Function Pmax(Load, Factor) As Double
Select Case (Factor)
Case "1"
Pmax = Load
Case "2"
Pmax = Load * 1.09
Case "3"
Pmax = Load * 1.12
Case "4"
View 9 Replies
View Related
May 8, 2008
Sub Addy()
Do Until ActiveCell. Offset(0, -4) = ""
Renamer = Proper(ActiveCell)
ActiveCell = Renamer
ActiveCell.Offset(1, 0).Select
Loop
End Sub
fail? Trying to remove all capitals from names/addresses. Error message is "compile error - sub or function not defined"
View 6 Replies
View Related
Aug 26, 2009
I have a string of names that run together without spaces or commas between each name.
"Danny TrejoJean Claude van DammeVincent SchiavelliGabrielle FitzpatrickDavid 'Shark' FralickPat Morita" for example.
Is there a way to add a comma and space between a lower case and upper case letter?
View 7 Replies
View Related
Jan 5, 2010
I have an excel spreadsheet with two tabs.One called "menu" and one called "website"
I want to put a button in tab one (menu) and assign a macro. I need the macro to run/output in sheet 2 (website) not in sheet 1 where the button is placed.
No matter what I do I can't figure it out. The macro always runs in the same sheet as the button.
View 5 Replies
View Related
Jun 3, 2009
I've got a pretty intense macro already written, a lot of Select Case components. At the end, if nothing matches I'd like to just copy the cell above to the cell below. However, there is a range of about 400 cells in length, so I'd need some sort of wildcard for range.
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Dim Cell As Variant
For Each Cell In Range("A1:OL1")
Select Case Cell.Value
Case "Eng1"
Cell.Offset(1, 0).Value = "Engine One"
tons more in the middle here
Case Else
Cell.Offset(1, 0).Value = "N/A"
Rather then returning "N/A", how could I reference the cell above and just copy it instead?
View 9 Replies
View Related
Sep 1, 2007
I have the following code as part of my macro. What I was wondering is a) Can anyone think of a way to shorten this? and b) I currently have this exact code copied 8 times, I did a find and replace to change 'if year = 2003 then' to the year 2010. What I would like is to have 1 code that will automatically change depending on the year the user inputs in a cell. (I should mention that 'Year' is a cell on my input sheet where the user types in the year they are currently working on and the macro automatically runs)
'2003
Sheets("Cash Crops").Select
If Year = 2003 Then
' This copies the cash crop BPU's.
ActiveSheet.Unprotect
Workbooks.Open Filename:="F:xdataCAISCAIS spreadsheetsBPU's.xls"
Application.Goto reference:="Cash03"
Selection.Copy
Windows(Filename).Activate
ActiveWindow.SmallScroll Down:=21
Application.Goto reference:="CashCrops"
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-30
Range("A2").Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True..............
View 9 Replies
View Related
May 12, 2006
Basically trying to make change the active worksheet using a macro code. I want the user to be able to click a button and it will automatically switch the worksheet to another within the workbook.
View 5 Replies
View Related
Aug 6, 2014
I'm fairly new to using VBA and have come into what is probably a simple problem to solve. However, I am looking to sort out my code.
VB:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
If IsNumeric(Target) And Target.Address = "$H$3" Then
Select Case Target.Value
[Code] .....
From the above the macro runs only with the Range H3, I would like have this code operate for the Range H3 to H10... I can't quite get it to work with a few formats I have tried.
View 3 Replies
View Related
Feb 21, 2007
I have just recently found that I can do case correction with Excel but I am manually having to do it how can I add it to my macro? The function for doing it does not seem straight forward to me on putting in macro I am sure it is simple but just missing some element of it.
I need to have Proper case for columns C, G and H from rows 11 and down.
View 9 Replies
View Related
Mar 31, 2007
And last but not least, is there a macro where I can perform a "change case" on the titles of all my graphs to be using "title case"?
To recap, this is what you have all come up with so far:
Sub DoAll()
Dim wb As Workbook
Dim ws As Worksheet
Dim objCht As ChartObject
For Each wb In Application.Workbooks
View 3 Replies
View Related
May 7, 2014
In my VBA macro, it copies data from this.workbook, and paste to another "destination" workbook. That destination workbook must be opened first before the macro is run.
Within the Macro code, I had to specify the exact name of the workbook which the data is pasted to.
The problem is that the exact name of that workbook which it paste to changes every week.
Is there any method where my macro can be changed and updated with the name of the destination workbook each week without manually opening and editing the code. The user of this.workbook is not technical enough to edit and maintain the macro each week
View 8 Replies
View Related
Nov 19, 2013
I want to use wildcards and it seems I cannot do it with case select, is it possible?
Its required because for the list of PSV's I have more than 2000 names and I would like to use *PSV* for them all instead of typing them all.
Here is the code below:
Option Explicit
Sub Colorize(Rng As Range)
Dim Cel As Range
Application.DisplayAlerts = False
Application.EnableEvents = False
[Code] .......
View 4 Replies
View Related
Jul 15, 2006
The following code works well unless the user entered the value in lower case.
How can I have it ignore the case of the "W"? I tried case match= false but I must not be using it correctly.
I found another macro on this site that will change the case to caps (note data comes in via copy / paste). Howerver, I was just wondering if it I can do this all in one step instead of two.
Sub DeleteSpecial()
Dim end_row As Integer
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
end_row = ThisWorkbook.Worksheets("sheet1"). Range("D65536").End(xlUp).Row
For i = end_row To 2 Step -1
If ThisWorkbook.Worksheets("sheet1").Cells(i, 4).Value = "W" Then
ThisWorkbook.Worksheets("sheet1").Rows(i).EntireRow.Delete
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Also, while I'm in the area, how can I rewrite the above code to actually look for a value of "S" at the same time it looks for "W" and upon finding either delete the row?
View 9 Replies
View Related
Jan 25, 2007
I am using this macro to ensure that a range of cells appear in Proper Case. However I am encountering a drawback, sometimes I have text which I want in Upper Case but which is changed into Proper Case. I was wondering if there was a way to work around this. Example: Practical W/W appears as W/w or Woodturing (GMC) appears as Woodtrunign(gmc)
Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''
'Forces text to Proper case for the range A15:A40
''''''''''''''''''''''''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("A15:A40")) Is Nothing Then
Application.EnableEvents = False
Target = StrConv(Target, vbProperCase)
Application.EnableEvents = True
End If
On Error Goto 0
End Sub
View 2 Replies
View Related
Jul 4, 2014
I am creating 4 Sheets from a Main Sheet by Filtering Data based on different values in a fixed column, then the Filtered Rows are automatically pasted in the respective sheets.
Then these 4 Sheets are creating another file.
The same thing done manually by Grouping the 4 SHeets and then Move Copy into a New workbook also makes the file heavier.
Everything works fine however though the data is not much the file is very heavy, so what do i do to make it of appropriate size ?
View 1 Replies
View Related
Apr 11, 2012
I am trying to make an extensive action items log for work.
On Sheet 1, rows 1-3 are blank. Row 4 contains column headers.
Column A: Item #
Column B: Description
Column C: Drawing #
Column D: Conforming
Column E: Nonconforming
Columns F, G, H don't really matter
What I am wanting is to be able to put an "x" or check mark in either column D or E (conforming or nonconforming). Then I want to run a macro with a push button (I know how to make these) that when pushed scans the spreadsheet row by row until it finds the "x" or check in Column E. I want it to copy every instance of this and send it to an Action Item Page that I have temporarily called Sheet 2. (This way I can go here to see all nonconforming issues). I do not want it to remove the information from Sheet 1.
Then, I want to be able to change the "x" or check on the Action Item Page to Column D (back to conforming) and the macro (push button preferably) copy and send that information back to the original spreadsheet in the SAME row it was.
So, in short I want this to be a fluid way of checking off a punchlist of items.
To complicate things even further, if possible, I would like to have several worksheets send information to the one Action Item List rather than just the one.
View 2 Replies
View Related
Apr 19, 2012
-I have two columns
-In column "A" are listed values in black and red font color
-I need a SUM of values inside of column "B" where in the same row in column "A" value is written in red color.
Example:
AB15,00060,0000Result:35,00070,000068,000025,000050,00002,00008,00006,000018,0000
How to make this automatically at larger tables?
View 9 Replies
View Related
