I have two questions. Below is a code snippet that demonstrates what I'm trying to achieve.
Const D_WIL_SearchString As String = "[HTML ON]*<a title=""*"" target=""blank""href=""*"">*</a>*[HTML OFF]"
If D_Info(i).WIL Like D_WIL_SearchString ThenDo AElseDo BEnd If
The double quotes ("") are supposed to represent a single double quote within C_WIL_SearchString.
The asterisks are supposed to represent any characters of any length.
All other characters (except for the initial double quotes (")) should be present within the string D_Info(i).WIL
I'm having issues ensuring that this code works correctly though, as it does not appear to search for the string that I'd like it to.
As a second question, I would like to know if there is a way of ensuring the asterisks (or another similar character) only allow whitespace? (i.e. spaces and new lines)
i have a worksheet called 'week 1' and want to write vba code so that a new sheet called 'week 2' can be created. However i want to write code so that it doesn't matter what sheet i am on, a new sheet would be created with the name week and the next number up. ie. the last worksheet is 'week 7' then the code should be able to create a new worksheet called 'week 8' ect. I currently have this code but it keeps returning an error message. anyone know how i could do this???
Sub nSheet() Dim nm, i As Long nm = Worksheets(Worksheets.Count).name i = 1 Sheets.Add Do While WorksheetExists(Left(nm, Len(nm) - 1) & i) i = i + 1 Loop ActiveSheet.name = Left(nm, Len(nm) - 1) & i End Sub
I need the code to create a find box into which a search string is input. The code should find the string at a particular cell, then copies this cell and four cells to the right to a location in sheet 2 of the same workbook
The code runs but comes back with a runtime 424 error. I believe the issue is being caused by "nextCell" but I cant see why.
Sub FindStrings() Dim firstCell, nextCell, stringToFind As String ' Show an input box and return the entry to a variable. stringToFind = _ Application.InputBox("Enter J Number and Stage Number. For Example J1234 ST1", "Search String") ' Set an object variable to evaluate the Find command.
This forumla generates a number for each player, the higher the number the more inline they are to get a game
Problems are this works for the 29 weeks of this year but more weeks need added to the end of the year as we get there. Can i generate the array numbers from a formula and shorten.
This is a sample data ignore row 2( just a count of players) and data actually goes back to 6th Jan
Is it possible to create a string with a range of numbers
1 3 4 5 6 8 10
Result: 1,3:6,8,10
Or
1 2 3 4
Result: 1:4
Or
1 3 5 7
Result: 1,3,5,7
Where consecutive numbers are separated by a ":" and other numbers by a ",". I use the following function to create the ","-concatenate, but I don't know how to create the ":"-ranges.
Function AConcat(a As Variant, Optional Sep As String = "") As String 'By Harlan Grove, March 2002 Dim Y As Variant If TypeOf a Is Range Then For Each Y In a.Cells
This is column a and b. Looking at b I am looking for a formula that will pull the first capital letter out of each word like ACR to create color codes.
I'm using the sum if function but can't get the correct answer because the text that I'm searching for includes * which I assume is the wildcard symbol. I'm looking to sum on the text integrated projects* but it's returning a value which also includes integrated projects* VAT.
I don't want to have to change the text (e.g. by using find and replace). Is there any way excel can incluce just the exact text I need (including *)?
I am trying to create a combobox to filter a set of data by the month that is entered.
The below code worked fine when there was just the month entered, but now all the entries in the sheet are in the format 01 January 2009. So I need a section of code which will search for the combobox value as part of a string in my range.
I need to write a macro whereby it queries a cell and if there is any value (it will be either blank or contain characters) in the cell a new row is to be inserted directly above the cell.
on one page I have a list of dates in one column and a list of numbers in another column. I want to look up January say in the dates column and sum the relevant numbers in the other column, (all the dates start with 01, i.e. the first day of the month), so I tried:
If I put in the exact date, i.e. replace ???? with 2008, then it works fine, just returns the number for that one date, but with the wildcards, I always get zero.
HOWEVER when I do the find & replace I don't want it to change whatever the current value is to D3 I want it to stay as whatever it was already....so how do I exempt the "D" value from the search?
I'm working with several columns of data that represent the types of employees we have at my organization. I'm trying to count the number of each type by status, as well as determine the salary payments to each type.
My problem is that we have titles like "PAA I" "PAA II" and "PAA III" and I've not been able to quite figure out the right formula to deal with this problem.
I have an excel file where the user has to enter his user name and password. But the password that is entered into the cell should appear as Wildcards(*) to everyone (Like our Windows login). Is there any way to do it.
I've got a column with client codes (col. B) and another one with material codes (col. C). Both columns mix numbers and text. First condition: If cells in "B" column equal a specific code ("DA2")
Second condition: AND If the cells in "C" column start with a certain pattern ("30") followed by some other pattern (four random digits, a dash, and two or three more random digits). THEN a new cell should say "T1", Else it should just copy another cell.
Is there a way to use this formula, but instead of looking in Column B, and having to list 00.00.01, 00.01.01, that it only looks at the last two digits of the ##.##.##.
Here is the issue. Each month, the numbers that make up what I need to add together will change. However, all I am really doing, is combining any accounts that have the same sub account (Column B) ending in the same digits of either 00, 01, 02, or 08. So I could have 01.01.01, 01.00.01, 01.02.01, etc. and I am just focusing on the .01 on the end. I would like to say, look at all the sub accounts in B, and only add (G:G) on those that the last two digits equal .01.
I'm trying to use wildcards in the Search formula and it isn't working the way I thought it would. I'm trying to use the following two strings:
????1234
compared to
1234????
Shouldn't search find these two strings the same since given the wildcards? Or am I missing something? If I use the above, I receive a VALUE error rather than a match.
In cells G2 to G57 I have the months from Jan-09 to Aug-13. In H3 to H57 I have the monthly data. In cell k2 I am trying to average all of the December data without individually clicking each cell (Ex: Dec-09, Dec-10, Dec-11, Dec-12). I was thinking of doing Averageif formula like =AVERAGEIF(G2:G57,"Dec*",H2:H57) but I get the dreaded #DIV error.
I have a problem with the code below. I've borrowed parts of it from various online sources and attempted to make it my own. I need it to go through a column of data that is imported into column A. The unique search criteria are rows that goes sequentially (i.e. 1, 2, 3). The format for the search is (#. text). I need to copy those rows to sheet 2. The problem with the code is that it only copies the first occurrence of the sequence (the row with 1.) to sheet 2 and stops.
Here's an example of the data:
Kalé
1. Kalé
$$ Japanese, Sushi Bars
Shigezo
2. Shigezo
Southwest Portland, Downtown
Code: Sub Copy_FSE_yelp()
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row 'Finds last row For i = 1 To LastRow 'creates loop for column
If Cells(i, 1) Like "*" & "." & "*" Then Range(Cells(i - 1, 1), Cells(i - 1, 1)).Select Selection.Copy
When I use a lookup to look for "*"&A1&"*" where A1 contains "AB" I get a value not available error despite the fact that in the postcode table there are loads of postcodes beginning with "AB...". Is it the multiple entries of "AB" in the lookup table that will be messing this search up?
I can locate all the cells in question using EDIT>REPLACE,
FindWhat=VLOOKUP(*,*,*,*)
But when I put this =(0.01*VLOOKUP(*,*,*,*))*$F2 in the ReplaceWith box, and try to replace, I get an error message saying the formula contains an error.
Once I figure this out, I'm filling down about 60000 rows, so I'd like to get it right the first time.
I have a worksheet that conatins multiple columns that are populated from a sql query.One column is a parts list.This has 1705 rows.
I have another worksheet that contains a list of parts in one column.This list is varying in row length.
I want to be able to produce a list that only contains matching data , including () around parts and if not too difficult an indication of data that is not in first list but is in second list.
When I do an advanced filter using the second list as the criteria i am not recieving all of the data from the filter , ie in the first list there is parts in (1234XYZ ) but in the second list the part is 1234XYZ , this part is not being resulted in the advanced filter unless I put brackets around the part in the second list . There is no way of knowing which part is in brackets in the first list.
I matching up some ip numbers against some ip ranges and I'm finding I can only use the wildcards in the first argument and not the second array argument. Is that the case? Here's my files:
IP_List 141.188.146.000 =match(A1,IP_Range!A:A,0) Doesnt work 156.140.216.202
IP_Range 141.188.???.??? =match(A1,IP_List!A:A,0) works
I really need the first case to work (looking up an ip in the ip range). Is there a way to do this?
I have a workbook (Data) that I am entering "job title" into column G. Based on key words in the job title I am then manually entering "level" into Column V.
I have created a worksheet (Level Matrix) that has the following
Column A = Job title Column B - Level Column C = Exceptions
What I have in column A are the key words - as an example *Director* (wildcard Director Wildcard); in column B is the word Director
What I want the system to do is look for the word DIRECTOR anyplace in the job title entered into Column G of the data worksheet (hence why I have the title between wildcards. IF it finds it then I want to add the level automatically (from Column B of the Level Matrix worksheet).
However if the compare finds any of the words in the title that match any of the words in the Exceptions Column (Column C of the Level Matrix) then I DO NOT WANT To autoload the Director level- it would load DNA. As an example- an Art Director would not load "Director" becasue the word "art" is one of the words in the exceptions column of the Level Matrix.
I have discovered that I can use a wildcard in a function such as =COUNTIF(B3:H14,"L*"). This will count all cells in the range that start with "L". However, I can't get it to work with conditional formatting. Is that correct? I am using Office 2000.
I know that I can use Ctl H to Find and replace say all APTs by typing APT*. Here's a small sample of my data. How do I get rid of the letters at the end of the street numbers so that I can sort properly?
KOGASAKA561 KAWAINUI ST MORIMOTO570 KAWAINUI ST KERNS587 KAWAINUI ST TOI587 KAWAINUI ST UNO606 KAWAINUI ST CAVACO611 KAWAINUI ST TAKARA315A KAWAINUI ST NAKAMOTO350A KAWAINUI ST NISSLE419B KAWAINUI ST MONTANA419C KAWAINUI ST LIU428A KAWAINUI ST HABERSTROH533A KAWAINUI ST IIDA545A KAWAINUI ST MEYER328 KAWAINUI ST AKITA331 KAWAINUI ST
