Hello, i've been using the below formula to find the most consecutive "W" (Wins) without any "L" (Loses) in 2 rows, there are between the "W" blanks and and numbers
The problem is at the start or end of the count eg
A B C D
L WWW
W W L
The above returns 5 the result i am looking for is 3 as column B and C are the only ones without an L, there are alot more columns so obviously if there are larger sets of consecutive W then the result would be bigger,
Is there a formula to find a specific word through many columns? Between A1 and L4500 i got a lot of diffrent information. I want to show if there is any cells in the row with the specific text, a formula in column M. Etc. If C100 got "topside" a notice in column M
automate a data table I set up in excel. I need the formula to look through a bunch of raw data I have, and add quantities in each row, but only from the rows that have the same machine numbers. I attached the document of raw data I've collected, and on a separate tab, an organized table. I would like this table to automatically be updated with the appropriate data, as I enter it as raw data.
I manage to use formula find for 1 text in 1 statement, but in confuse how to get another text found if i have "8 text" to find. Generally, there will only be "1 text" for each statement.
Maybe in 1 statement there have only "IC" or "Staff Tagging" or Name" or "Product" or etc till 8.
I have a worksheet with several columns. I need a formula to search column D only and each time a specific location is identified to replace that location with alternate text. Example (ORIGNAL TEXT):
Column Dtext to text help.xlsx BIRD FISH DOG BAT BUG
I need to search that listing and each time the word BIRD is mentioned have it replaced with FEATHERS and each time DOG is listed have it replaced with TAILS Final result would look like:
FEATHERS FISH TAILS BAT BUG
All other text should stay the same and replacement text should appear in the cell of the text it is replacing. This is a sheet used by multiple people several times a day and so the Find/Replace option really won't work.
Have working on this for at least 6 months and it just isn't going to happen for me. I thought I could use a Conditional format, but that is producing no results either.
I'm trying to find vehicle make and model in a cell containing a lot of text and then display that in the formula cell. For example if A1 is a paragraph that contains somewhere within it "Ford" & "Ranger". I want B1 to display "Ford" and C1 to display "Ranger". I have a list of vehicles makes (column A) and models (column B) on a seperate sheet.
I can pick one of these. If i pick Cash then i want a specific cell to run a specific formula. depending on the item chosen.
So in cell A6 is the list.
in cell B6 an amount will be place and in C6 another amount will be place. the number for c6 will range from a -number to a +number.
But D6 looks to see if B6 meet the requirements for MTT, SnG or Cash depending on which item was selected from the list. and uses the formula below with $c$3 changing from to either $D$3 or $F$3 and comparing the values for the chosen item. the formula changes would look this
=IF(ISBLANK(C6),IF(B6>$C$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))
=IF(ISBLANK(C6),IF(B6>$D$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))
=IF(ISBLANK(C6),IF(B6>$F$3,"Do Not Play","PLAY"),IF(C6<B6, "Lost","Win"))
In row 1 I have the numbers 1 through 7 running on a loop so A is 1 B is 2 .... F is 6 G is 7 and then H starts back over at 1 what i want to be able to do is find the last lets say number 2 in the row how would i do this?
I have a database that contains many products. All of them has a number.
I have an cell and a button for search. I am entering on the search cell the 254 number , and when i click the search button , i want a macro that can select me the cell that contains the 245 number from A column.
Till now i had tried with the following procedure.
I had made another cell which contains the following function
=MATCH(G6,A1:A100,0) , where G6 = my search cell for the number. This return my cell number from A column , but i don't know how to select that cell from the vba
I had tried with the ActiveSheet.Range , but i had to input the cell which i want to select , or it's there a way to enter the collumn where to find and the cell number to get from my MATCH function ?
I'm trying to create a macro that will automatically change the prioritization of work that is to be done. There is data in A:10 to M:60. Each row 10 through 60 represents one order. I would like the supervisor to put an "x" in column O. Then select any cell in a different row. Then when he hits the command button, cells A through M of whichever row has an "x" will move to A through M of whichever row has the active cell. This would push the effected cells down a row. If there is no "x" in column O, I would like a message to return, "Please select job by putting an "x" in the corresponding column O". Also, if the active cell is not within rows 10 through 60, I would like a message that returns, "Cannot place job there." I know the messages are dumb. I can change them later.
refine this piece of code so it only looks in column 2 for a matching value.
On Error Resume Next If S = "" Then MsgBox "Delete Cancelled" Else Set r = .Columns(2). Find(What:=S, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False)
On Error Goto 0 If Not r Is Nothing Then Application.Goto r, True
If r Is Nothing Then MsgBox "That APL ref does not exist" Else
Dim rowNum As Long: Rem *** ***** rowNum = r.Row: Rem *** *****
In my worksheet I have a column in which the cells have two possible values: "yes" or "no". In the first positions of the column there are only "yes" values, after there are some "no" values and then the "yes" values come again. Is there any function that identificates the position of the first "no" value in the column? And also the last "no" value? Attached I send a simple excel worksheet
formula to tell me 9 values. These values are on 9 separate tabs/worksheets. They are always at the bottom row in a specific column - but the row changes every week. I have a totals tab in this workbook. I would like to stop manually going to each tab and pressing Control+End to see the total and manually going back to the totals tab to enter the value. Is there a formula to find these 9 values automatically and display them on my totals tab?
I am trying to check some data (about 5000 rows worth) and don't want to have to go through each row. What I need to do is check whether the 7th number in a string is below 5, and then to return a text result for above 5 and a different result for below 5. For example:
B4: 88050170088 7th value = 7, therefore return "Male"
I have tried =IF(MID(B4,7,1)>5,"Female","Male") but it is not working and I can't figure out why..could also just be making a rookie mistake....
Basically, I have 4 numbers, out of 50, as seen above...I want it so the last number (46 in this case) is special; what I mean by that is I want the following conditions to be met.
--If the special number is the lowest, just take the average of the four numbers, and divide it by 50 --If there is a (are) number(s) lower than the special number, then I want it to locate the smallest ---The smallest number should then be "replaced" by the special number (for calculations purposes only, not in the actual table)
So, in this case, since the special number (46) is not the lowest, excel would locate the lowest number (44), and calculate the average of 47, 50, 46, and 46 (the 44 was replaced by 46).
I have a spreadsheet with a stock list. Each item has an individual number. I have a code below, which I have doctored from other various sources. The problem I have is that if I search for number '12' the spreadsheet highlights every cell with 12 in (120 121 122 123 etc...) Is there any way I can change the code so that it returns only the 12 and not every cell that contains 12? The current code is as follows:
I have absolutely no idea how to get starting on this one. I've got a long string in cell B1. At some point there is the word "oms:SomeString," (without the quotes). I need to know whether SomeString is somewhere in the active sheet or not (the workbook running the VBA-code is not the active sheet). I can't just compare the cell B1 because it contains multiple words. Any hints are very welcome.
I need a vba code that will find all the columns (j) which there cell(1,j) includes the word "Date". the word "date" will be a part of a title, for exmple: "due date", "starting date"
Cells A1:A5 all have a vlookup formula that will pull one of the following into it:
1. The word "NONE" 2. #N/A error since it doesn't exist in the table 3. A text value that has the format 13.55.46.91
I want a formula that will look at these 5 cells and give me #3. That #3 value can only appear once in the 5 cells in any of those 5. The other 4 cells will contain either 1 or 2.
3 always has length equal to 11. It always has periods in those places. The digits do change.
Private Sub CommandButton2_Click() Dim myarr() As String, size As Integer, i As Integer size = WorksheetFunction.CountA(Worksheets(1).Columns(2)) user = Environ("username")
[Code]...
when the FOR-LOOP finds the username inside the column B (many times will have more than one result) I would select all the rows that have this username and display that data into a listbox but not only the rows that have the username but also I would like to get the next column C and D in the same row and display all those data into a listbox.
ID username date status 1 usuario1 25/01 ok
[Code]...
taking the table above as example I have USUARIO2 twice in the column B so I would like to get that row and also the DATE and the STATUS from this USUARIO and display it in a listbox. If I'm the USUARIO2 and I click in the button Then I will have a listbox like this:
USUARIO2 26/01 denied USUARIO2 27/01 ok
What should I add into my code to get this result?
I am trying to have a worksheet linked to another workbook, but this workbook name will change. It will always start with Blockbuster - Valuation but the rest of the file name is the date and the version of the workbook. Do you know how VBA can go in a folder and look for the entire file name of a workbook starting with Blockbuster - Valuation.
I have a piece of code used to find a matching date in a column and then unhide that column. Code works because there was only one date on the sheet other than the one's I was trying to find. I now have to store additional dates in the sheet and this is disrupting the search as it stops (as it should upon finding a match). I would like to see if there was a way to modify it so that it will look for the match in specific columns i.e. Specifically the dates are located in AX1:IX1. Here is the code
Columns("AX:IK").EntireColumn.Hidden = True Dim DateToFind As Date DateToFind = Range("a1") Cells.Find(What:=DateToFind, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).EntireColumn.Select Columns(ActiveCell.Column).EntireColumn.Hidden = False
