I need to ensure that the client names entered don't contain certain banned characters (* / : ? [ ]) as the name entered will be used later to create a file name. This is what I have written (in the Worksheet_Change event):
If Target.Column = 1 Then
If Target.Value 0 And Target.Value "" Then
Dim intFound as Integer, ranFound as Range, i as Integer
Application.EnableEvents = False
Application.ScreenUpdating = False
intFound = 0
For i = 42 To 93
Set ranFound = Target.Find(Chr$(i), LookIn:=xlValues, lookat:=xlPart)
If Not ranFound Is Nothing Then
Select Case i..........................
Which is all very well, but it treats character 42 (*) and character 63 (?) as wildcards and even good client names are rejected. How do I search the entry to find if there is an actual * or ? in the target.value?
I'm faced with receiving data for time, in a text format, as follows:
Example of possible cell contents...
20secs 40m20secs 2h40m20secs
I'm not interested in the secs value but need to isolate the h (hours) and m (mins) values to use in another formula as numbers. The time could be shown in either of the above formats with either; (a) just secs, (b) mins & secs or (c) hrs, mins & secs... and of course the hours or mins values could be 1 or 2 digits in length. How can I determine (using formula only, not vba) what the values for hours and/or mins are dependent upon what is present?
Currently I am using the Kickbutt VBA Find Function of Aaron, but I would like to have something that works more efficiently. What I currently do is (assuming all possible values for Column J are A - F):
although I just want some code that says: delete all rows except those that have "F" as content in Column J. I already tried something like:
Range("1:65536").Select For Each cl In Range("J:J") If cl.Text = "A" Or cl.Text = "B" Or cl.Text = "C" Or cl.Text = "D" Or cl.Text = "E" Then Rows(cl.Row).Delete End If Next
but it also takes much to long. The major problem I think, is that the number of records is variable so I search the entire worksheet...
i got a a sheet with over 10000 entries, problem is that it has been compiled by many people, each which has his or her own way of entering the data. Here is the problem i have a field in which they can enter a coordinate X,Y and a corresponding area. the sytaks is Area XXYY so it should look like this P20522. Where P2 is the Area and X=05 and Y=22.
most of the entries has the area in a different field and is written X5,Y22 but some is done correct some without the comma, further more i need to add the 0 in fron if it is a number below 10.
I have several hundred columns of data in this format: |#########|. I need to remove the non-numeric characters on the edges of the numbers so I can manipulate them. Is there an easy way to do this? I do not want to manually remove all of the characters.
I need a formula which can clean up a huge data set. Essentially i need to delete the entire word which contains the characters "aceae". note that "aceae" is a suffix, but i need to delete the entire word not just the suffix, plus keep the rest of the string. i have tried the "find and replace" function of excel with wildcard, but that deletes everything before/after without deleting the entire word. i have tried a combination of formulas to isolate the unwanted words, but that method is inefficient and inaccurate. below is a schematic of what im looking to do:
Column A ------------------------------ Column B l. planeri asteraceae africa laselva-----> l. planeri africa laselva l. planeri moraceae europe singer------> l. planeri europe singer origin l. fluviatilis bignoniaceae asia----> origin l. fluviatilis asia alternate l. fluviatilis piperaceae asia---> alternate l. fluviatilis asia
I want to search a string for specific characters. f.e. Begin = "bfPaa2" I want to look for "P" So, the answer has to be: Letter = "P" after searching the string
In one column I have different objects separated by a comma. I need to select one of these: 11,20,30,60,61 and copy it into another column. I have used this For counter = 0 To not_empty_cells
For counter_dep = 1 To 5
position = InStr(1, (Cells(counter + 3, 4)), department(counter_dep))
If position > 0 Then symbol_dep = Mid(Cells(counter + 3, 4), position, 2)
Cells(counter + 3, 10).Value = symbol_dep End If
Next counter_dep
Next counter
It works, however, once in the first column there are the following objects: 60,6128,CZ, it takes 61 but it should take 60. Unfortunately, the position of the object can vary, it is not always on the first position.
I have a large column of text with multiple entries similar to this: PC3L-10600R-9-11-A0. I need to replace the "11" with a 13. However I have other instances where the 11 appears (PC3-12800E-11-11-D0 or PC3L-12800R-11-11-A0).
I have found that I can use SUBSTITUTION
{=SUBSTITUTE(A50,H50,I50,1) and =SUBSTITUTE(A61,H61,I61,2)}
to handle the specific instances but I'd like to have a way to combine this type of function logically in one command.
I have a column of values of peoples initials and I want to cound how many times one persons initials occur in those cells. I can do this if the cells just contain one person i.e. "CH" or "DH" but if the cell has two or more peoples initials i.e. "CD DH" I can't do it as it only matches the exact search criteria.
How can I total the numebr of times a required set of initials appears in a specific column of cells? Not bothered is it requires a macro or a formulae, just can't seem to figure this one out.
I have one column with many numbers. Some have one dash and some have two.
Example: 123-123456-65 012-789546-1 98B12354-889
Is there a way that I can remove all characters after the last – (dash) in the number? Example:
If number is 123-123456-65 Then 123-123456
If number is 98B12354-889 Then 98B12354
If someone could just lead me in a direction, I might be able to figure it out. However, my code is elementary and most of the time, I record macros and the play with the code until it does what I want.
I have a very large file of data, over 500,000 rows, opening in one sheet in Excel is not an option with my current version. In each row I need to change the characters in positions 41-44 from whatever they current are, to '9999'. I'm sure there has to be a way I can do this using vba, does anyone have a sample snippet of code, or another post they can point me too?
I have a worksheet that will have tabs added as time goes by and a macro within the workbook currently selects all of the sheets and copies and pastes formulas within all of the sheets simultaneously. Right now, if a tab is added, the macro needs to be updated to select that specific tab name as well.
I would like to simply have the macro select all of the sheets starting with "IL" until the sheet before "reference", or all sheets except the first 3 and the last 2 if that is easier. Any ideas? And thank you for your help, you people really amaze me sometimes!
I have a querry- file search application macro which searches all directories and subdirectories found within the provided string path and brings in specific data from spreadsheets in those directories. I would like to have the ability to exclude some of the subdirectories which are currently being querried.
I'm wondering if it is possible to add additional code that will allow me to either include or exclude specific directories being querried.
I am trying to detete rows that have several specific values, upper and lower case, (A-H) in any of several columns (B through H). I can delete upper and lower case "D"s in column B, but I'm having difficulty stringing together several variations and getting at the "D"s that are midstream (such as: ADeC), I tried ("*D*") to no avail.
With . Cells(Lrow, "B") If Not IsError(.Value) Then '**** If LCase(.Value) = LCase("D") Then .EntireRow.Delete
So we have people's names in a table. First name in one column and last name in another column. We have a 3rd column where we can use 8 characters to do a combination of First 5 of Last Name + first 3 of first name. However, if someone's last name is only 3 or 4 characters, we'd then want to take more from the first name to fill out the 8 characters.
So: Charles Johnson -> Johnscha John Smith -> Smithjoh Willian Wu -> Wuwillia
What's the best way to do that without creating some crazy formula with tons of if/thens?
I have number in cell (A1) = 100. when I enter number in any cell of column(B) for example (B1)=10. then in cell (C1) the result of (A1) - (B1) = (90) and if I add in cell (B2)=10 then in cell (C1) the result of (A1) - (B1+B2) = 80 .accumulatively in cell (C1). and any number in column (B), the result will be (A1) minus any number in column(B) accumulated in (C1)
second question
I have number in cell (A1) = 100. when I enter number at cell (B1) = 10 then the result would be in the adjacent cell (C1) = (A1) - ( B1) = 90 and If I enter a new number in cell (B2) = 10 then the result would be in the adjacent cell (C2) = (A1) - (B1+B2) = 80 and If I enter a new number in cell (B3) = 10 then the result would be in the adjacent cell (C3) = (A1) - (B1+B2+B3) = 70 and so on. I want the result to be add automatically to adjacent cell in column (C)
I'd like to be able to search a spreadsheet for a specific word or series of characters. Once all of the ROWS that contain the characters are found, I'd like to be able to have another spreadsheet with ONLY those rows (all columns).
I can't use the "" sign as delimiter to separate them into different columns because the age,city,name and height fields are in random positions on different cells.The good thing is person's name will always come after "name" string, age is alwals followed by "age" string, so it cannot be like nameheight40Michigan180
I think the following would be the easiest method(not for me tho).If on B1 I had a formula that said "find the string "name" and write anything after it until you reach the next "" character".On C1 field I could have a formula "find the string "age" and write anything after it until you reach the next "" character.On D1 I would have the same for "height" string,then on E1 for city string.
My question is somewhat similar to this one Extract A String Between Two Characters
Formula which outputs the data between 3rd and 4th instances of the "_" character.Can we substitute "3rd and 4th" with a specific strings like "age" or "height" ?
I would like to change the greater than number to the value in cell 'I11' in sheet 'Linear Programming Data' and the less than number to the value in cell 'I12' in sheet 'Linear Programming Data'. The code I am using was done by recording a macro since I don't know how to code in vba.
Code: Sub Results2() ' Results2 Macro ' copy table filter power by greater than and less than Sheets("Finalizing Results 2").Select Cells.Select
I have a protected template and unprotected source worksheets - - - what I would like to happen is for the macro to start and if the source worksheet cell B3 equals "Report Total" then stop - otherwise copy template worksheet then copy 6 specific cells from the source to paste values to specific cells on the newly created worksheet (B_ to C7, D_ to I7, E_ to C9, F_ to K9, A_ to C11, M_ to K11 and then K13=F13-30)
After that then start all over again unless the next row’s cell (B4, B5, B6, . . .) is "Report Total" then stop - - - the row count could be from one to a couple hundred.
Here is what I have so far but I know that with each copy the name will change and as it goes down the source file each row will change and I also need help with that.
Is there an Excel formula to remove the spacebar + characters in red, as shown below? I need to be left with only the last name, first name and the semicolon.
Mouse, Mickey ;
Microsoft Outlook has changed the way that email addresses from the global addressbook copy and paste (from version 2003 to version 2010).
I'm trying to search through multiple worksheets (that are closed) to see if a value in cell B12 (of every worksheet) matches a value in a seperate worksheet (which is in a seperate workbook)
