I am manually scrolling through Excel right now to find all blank cells in column L. Some cells have dates, others are blank. IF L is BLANK, I go to row K and copy/paste row K to row L. IF K is BLANK, I got to row I and copy/paste to row L. Is there a statement I can put into row L to basically say "If blank copy/paste from row K. If row L and K are blank go to row I and copy/paste to L." ???
The following code works fine to determine if a particular character occurs within the selected range of cells:
Code: Sub CheckIfCharacterIncluded() For Each MyCell In Selection If InStr(MyCell.Formula, "#") Then MsgBox ("The " & "#" & " character was found in cell: " & MyCell.Address & " at position " & InStr(MyCell.Formula, "#")) End If Next End Sub
However, I would like to extend this functionality to check for multiple characters, using some sort of array that contains all the characters I want to check for e.g. "#","*","Â£" and so on, without having to repeat the above code for each character for which I need to check.
I would like to select all cells in column A that begin with the text "SP". Some cells will be contiguous but others will not. For instance in one case, I would like the macro to select cells with the text "SP" which would result in cells A1, A2, A3, A10, A15 being selected. I am working on building a macro that will then do other things to these cells/rows so this is the first step.
The below code will select the first cell with "SP". How would I alter this code to only search Column A and select multiple cells? or totally different code.
Code: Sub test() Dim r As RangeSet r = Cells.Find(what:="SP", LookIn:=xlValues, lookat:=xlWhole) If Not r Is Nothing Then r.Select End Sub
VBA in Excel 2007. Essentially, what I am trying to do is this:
I have to two columns (A and B) that have a drop down list in each cell containing text options. I created the drop down lists using simple data validation. Let's say that A has the following options (East, West, North, South) and B has (Up, Down, Left, Right). For each combination of these, e.g., A1 = East and B1 = Down, I want C1 to pull a unique piece of data (a $ amount) from a separate worksheet. I can get this to work in its most basic form but I want to be able to be able to do this for cells in rows 1 - 300 without having to write separate lines of code for each.
Additionally, I am having trouble getting C1 to update automatically once A1 or B1 are changed.
I have a spreadsheet that i want to create a part number that contains 4 parts. Ex 1rl9. The cell i want this to be in is blank. What formula do i use to make the second character in the blank cell = r?
My spreadsheet is multiple rows and columns of sales data with two blank rows between salespeople. Ex:
$100 ABC Inc. John #101 $150 XYZ Inc. John #101
$200 Golf Inc. Sue #102 $150 BBall Inc. Sue #102
What I need is this:
$100 ABC Inc. John #101 $150 XYZ Inc. John #101 $250
$200 Golf Inc. Sue #102 $150 BBall Inc. Sue #102 $350
Where the sum of each salesperson's sales figures is shown and formatted. I've been doing this manually. I'm having difficulty figuring out how to do this using VBA. I can do the formatting, and conceptually I see what needs to be done.
The problem is the blank cells in the range that I am calculating (M2:M25). Everything is alright if there were no blank cells in M2:M25. If there were any blank cells, however, the STDEV was wrong (the countifs part was correct). It treated the blank cells as zero.
I have a large list of cells in excel: 15, 33, 90, 102, 149, 159, 217, 228, 238, 247, 305, 312, 369, 417, 428, 486, 538, 548, 606, 621, 671, 679, 737, 805, 816, 874, 915, 923, 981, 1029,1038 .
Under each of these cells I would like to insert 20 blank rows. I have tried various codes but i'm struggling with the fact that as soon as I insert 20 rows at cell 15, all the other cellnumbers change.
This is a reformulation of this post: [URL] ...........
I work with a spreadsheet every week to input values and subtotal them. These values change constantly and instead of going through and manually deleting each row in a 100+ row spreadsheet to be able to import into another program, I'm looking for an quicker way to keep my data in order but consolidate by getting rid of only the rows where both column A and B are blank.
Here's an example of what I'm working with:
5 10 15 20 50 2 4 6 8 20
This is what I need the final product to look like:
5 10 15 20 50 2 4 6 8 20
So I would like to quickly delete rows 2, 5, and 11. All of the other answers I've found only show how to delete rows based on empty cells in only 1 column. How can I quickly delete the rows where both columns are empty?
I need to fill the following table in another sheet counting the amount of Blank cell there are according to Month, Team and if the name row is filled. I have tried Sumifs, sumproduct,countblank typing them in as arrays but don't seem to be getting anywhere
I am fillin out a spread sheed with a num ber of codes. I want to create a excel formula that will look at the entire column and search for each of the codes. If I have forgotten to add one of the codes into the column I need the formula to let me know.
What I was thinking might work is this...
To look for one of the codes I would do this. =IF(MATCH("FLA",E5:E36,0)>0,TRUE,FALSE)
This works great if the Value is in there. If it is not in there I get #N/A instead of FALSE. Is there a way to get the MATCH to return another value if it is not found in the range?
Is there an easier way to look for multiple values? So far the only solution I have found is like this
I have been using the VLOOKUP function to search a table of data for a specific value in order to then give me the corresponding data associated with that value. I can do this and it works!!
However, as I am searching for names some people have more than one set of data and the VLOOKUP only finds either the first set of values (if the FALSE command is used for ‘range_lookup’) or the last set of values (if the TRUE command is used for ‘range_lookup’). I have attached a small example:
How can I get all values to be displayed?
I know that this may have been explained horrendously but I have written and re-written this several times and this is the best I can get!
i am trying to set up an interface through Excel for a database of record also held on excel. I have very very basic VB skills and have no idea what im doing lol but what i would like to do is have a search box and button, something similar as you would find on most websites. I would also like (if possible) for it to search up to 8 workbooks to look for a name or vaule.
I want to search a longer text field for a shorter string that may be within it. However, I want to do this for multiple short strings.
PhraseHidden word Please search the text for the hidden message: Blue The hidden word, Green, may be at any point And the word may be different lengths like Yellow
I have a table of the hidden words (Blue, Green, Yellow, Pink)
I know I could use search to find one word, but is there a way to look for multiple words, and return the value of which one it finds? I have many hidden words (and the list is dynamic) so I don't want to just split into multiple searches.
Fall - Columns A-G Spring - Columns I-O Summer - Columns Q-W Unduplicated - Columns Y-AD
I am needing to take each student in the Unduplicated section and see which terms they attended. If they attended a specific term, I need to grab a piece of data from that term and put it in over in the undulicated area.
The only way I could think to do this is with three separate searches. This is probably grossly inefficient and so far it has not worked at all. I have posted below the code I am currently using. Again this code is not workin at all.
I need to do the following:I have multiple worksheets/ workbooks that hold user info such as address, country, first name,last name, ID,dob, gender, occupation etc I need to make a search dialog whereby the user is allowed to do this:
1-Search by ID by selecting thru the calendar tool OR
2- search by first and last name. When he clicks search, I need the particular worksheet holding the data to pop up and show the user the data that was requested for.All data in ID column is unique...but for first n last name, i have a few people who have identical names
i have a spreadsheet that i use for work which has 1 page that contains all the data for the workbook. on other pages i use this data, and tables, to pull information to those pages or other pages which complete my work in a more timely fashion.
that issue i'm having is when i try to look up specific values within the data page tables and there are multiple values.
i need a way to choose different values rather than the "first" value in the instance of a vlookup which only finds the first value.
in the pictures i have picture 1 (vlookup samples) has a section where i can type in the "office" code and it will auto pull "switch name" and "switch clli" via vloookup.
in picture 2 (switches sample) i have created a table, which is on another page as stated above, but if i try to search for the example "LSGT5" it only returns a switch name value of "LFTYINXFDS0" and there is also a value of "LFYTINXFDS1".
there are many more within the table(for this office code alone) but this is just an example of how i need a way to choose the proper one or somehow have a drop down without having to create a bunch of tables for each office itself.
I have two lists. One is a database (Worksheet 2) with a lot of information, and another is the list (worksheet 1) I need to fill up the data for. What I want to do is to search for the names that are in worksheet 1 in worksheet 2, and if there is a match, retrieve the missing data in those rows, and paste it back into worksheet 1. For example: Worksheet 1: Joe Tim 23 Matt
Worksheet 2: Alex 45 Joe 23 Mark 47 Tim 44 Matt 24
I basically want it to be able to search for the corresponding name, and then pull the number associated with it.