I am trying to simplify process for the users of a workbook. This workbook has about 20 tabs and I would like the users to manually paste a value in C1 of sheet1 and have a macro button that will find that value in the whole workbook.
View 3 RepliesI've scoured the internet for this answer and have not found a formula that works.
I have a list of domain names/URLs in column A of workbook 1:
jhjh.com
hajfh.co.nz
123.123.12.12
I have a list of domain name extensions in workbook #2:
.com
.co.nz
.net
I want to determine if the cells in column A workbook one contain any of the strings in workbook 2 - note that I need to look at the string, not the entire cell in workbook one. If there is a match, true, if not, false for that cell within workbook one.
It's sort of a reverse array lookup with text. It seems that cross workbook and text is what causes the challenge.
I'm running this code to find a text in a workbook but i need to know if this code find nothing .
View 12 Replies View RelatedI want create a macro which will find a text "9:55:00" in the cell of column A and if found copy that entire row in a new workbook which i have opened. the text "9:55:00" will be there every workbook i will open. Once the row is copied in the new workbook, when i again do the second find the row must be copied below the first find in the new workbook.
View 4 Replies View RelatedI'm trying to figure out a way to find a specific sheet in a workbook that does not contain the macro. Within the macro I have a cell which holds the name of the specific sheet I would like to find but I can't get it to work for some reason...
'Dim officen As Integer
'Dim thiswb As Workbook
officen = Range("A2").Value
Set thiswb = ActiveWorkbook
' Open the Active Info file
Workbooks.Open "C:My DcoumentsActive 20080616.xls", , , , "xxxxxx"
' Dim sourcewb As Workbook
Set sourcewb = Workbooks.Open"Active 20080616.xls"
Sheets("officen").Select
RowCount = ActiveSheet.UsedRange.Rows.Count
Range("B2").Select.............................
I want to create a macro that will find a text string called "Season Average" in the entire workbook and delete a range of cells above it 4 cells longs starting right above the text and then going left.
Example is if "Season Average" is in cell D11, than I want the cells D10, C10, B10 & A10 Deleted and the cells to shift up
This happens throughout the entire workbook and multiple times on each work sheet in different spots not consistently same cells.
I am having trouble getting my IF statement to test if the cell contains the text "sale" return "X" if not "Y". I need it to search through the text string in that cell and find a certain word, and if it finds that word, retrn a value. I am really having difficulty with is what symbol or function do I use for the logical test? (i.e. =, <>, MATCH, INDEX?)
View 4 Replies View RelatedI have to open another work book to get a certain cell with a value.
ex. in my source file (prod plan) i have columns A,B,C,D. in column B it contains Line1 up to Line10. In column C it contains Model and Column D it contains Quantity.
Prod Plan workbook
Sample:
A-----B-----C------D
---------------------
AAA--Line1--M1----87
BBB--Line2--L2-----45
CCC--Line3--X1----0
up to Line 10
I need to find all Lines (column b) as my reference to get the column D records using Excel Macro and paste or copy to another worksheet. Is this possible to use the IF to get the data from Column D or Looping. How to make this in Excel Macro.
Desired Result:
Line1|87
I have Quote Master.xls open
I have a value in AA1 that carries a number
I want to open Quote Log.xls
I want to find the cell in Column A (Quote Log.xls) that AA1 (Quote Master.xls) directly corresponds to
I want to offset from that found cell 3 cells to the right and insert from T7 (Quote Master.xls) into this offset cell. Here is the code so far
Private Sub InputIntoQuoteLog7_Click()
Dim CostSheetBook As Workbook
Dim QuoteLogBook As Workbook
Set CostSheetBook = Workbooks("Quote Master.xls")
Set RFQQNumber = CostSheetBook. Sheets("RFQ").Range("AA1")
Set RFQQStartDate = CostSheetBook.Sheets("RFQ").Range("T7")
Workbooks.Open ("\ACT3ENGVAULT EngineersLsheriffDocuments (2008)Quote System MashQuoteLog.xls")
Set QuoteLogBook = Workbooks("QuoteLog.xls")
Set vOurResult = .Find(What:=RFQQNumber, After:=[A1], _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3)
vOurResult.Value = RFQQStartDate.Value
End Sub
I get an un-numbered error on the .Find
I am trying to complete these steps:
Take a value from AK59 from the "EQF" tab in a file called "QuoteMaster.xls"
Use that value to find match in column "A" in a Seperate workbook "ITMSTR.xls" on the "Sheet1" tab
Offset to the right 1 cell of the found value, take back to the "QuoteMaster.xls" and put in cell "AN59"
My code comes up with no errors, it opens the file and closes is as shown, but it doesn't copy the value over for whatever reason...
I am needing a macro that will search all excel files in directory C: and open the workbook with cell F4 value equal to "Checklist".
View 9 Replies View RelatedWhat I have is a collection of about 500 .xls files that I want to import into a single worksheet in a new workbook, but I only want to bring in specific cells from the source sheets which are unfortunately not always in the same row numbers.
I have found this link on loops:
http://www.ozgrid.com/VBA/loop-through.htm
Which looks like it will do the work of digging through each workbook in a certain folder, but do I need to further specify a sheet within the workbook once it's opened in that loop code? Even if there's only one sheet in each source workbook? The sheets are named differently in each workbook, so that may be a problem.
Now the really complicated bit (I think) is the conditional stuff. Each source sheet is three columns (A, B and C) and I want to import the string (some will be numbers, some will be text) from the C column to a single row of the destination sheet, but only if the string in the source's B column matches what I'm looking for. There will actually be a few dozen of these conditional searches based on different strings from the B column, but if someone could just show me how to do it for a single string, I can expand that to all the other searches I need to do.
Let's say the search term is 'Application ID,' how do I look through the source worksheet for that string in column B and if I find it bring the string from column C on the same row into my destination sheet?
what I am trying to do but I'll give it my best shot with examples.
A1 = date
B1 = drivefolder[A1.xls]sheetname'!cell
If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F"
and also t = time()
I have a main workbook that is meant to summarize data from other workbooks
In Row 6 from column H on I have workbook names in each cell
Column G in all workbooks (including the main one) contains our branch #'s for our offices
For each workbook listed in row 6 , I need to open that workbook (I have that setup with the code below---notice there is an AX.xls that is appended to the file names listed in row 6 in order for the names to match what is in the windows directory)
In the newly opened workbook, I need to, for each value in column G, copy the value from adjacent cell in column H (the dollar value) then search column G of the main workbook for a matching branch and paste the value to the appropriate row under the workbook name column (remember workbook names are in row 6)
The trouble is, for each branch in column G in the newly opened workbook that cannot be found in the main workbook, I need to paste the new branch # at the bottom row of/in column G and the $ value (H column value from the newly opened workbook) to the corresponding row under the workbook name column
Sub OpenWBs()
Dim Rng As Range
Dim WB As Workbook
Dim MyPath As String
Dim lastCol As Integer
Dim newRange As Range
lastCol = Cells(6, Columns.Count).End(xlToLeft).Column
Set newRange = Range(Cells(6, 1), Cells(6, lastCol))
MyPath = "F:AccountingAPAdvertising AccountsLA TimesAgentExtractorCompletedLIST"
For Each Rng In newRange '
file storage
1. Look at the "Days so far" section
2. Ignore cell C1
3. All the other cells in that row are shaded blue. Look at these.
4. Look at the "Volunteer" row - all the cells in that row are shaded blue. Look at these.
5. Wherever a name - any name - appears in the "Volunteer" row, 1 is added to the previous number in the "Days so far" section and the result is displayed in cell from the "Days so far" row above that Volunteer's name.
6. For example, we begin in C1 with a count of 12 days so far - this was manually entered
7. In cell H5, we see Henry has volunteered 1 day. The total no. of days so far should now be 12+1. Therefore, the number in H1 should be 13.
8. In cell G47, we see Joseph has volunteered 1 day. The total number of days volunteered BEFORE Joseph volunteered is 15 (see cell D43). But now, with Joseph volunteering 1 day, the total no. of days so far should now be 15+1. Therefore, the number in G43 should be 16.
9. what formula l must put in the "Days so far" row (excluding cell C1, which is manually input) to give me the "should be" results predicted in that row? I'm guessing it will be a formula which looks at each row fragment of the "Days so far" row, row by row, right up to the previous cell in that row, all within one formula.
P.S. I just want to leave the "Days so far" row blank, for any columns where there are no volunteers in the "Volunteer" row, so please don't give a formula which inserts zero for days with no volunteer, and then sums the cumulative total.
I have a drop down list in a merged cell B12-F12 and B13-F13 and B14-F14 . . . B30-F30.
I need the adjacent merged cell to populate a reason (text) based on the text answer in the drop down list or the entered text in the first merged cell. For example in the cell B12-F12 the user picks from the list or types in "Amiodarone." I want the adjacent merged cell G12-J12to automatically fill with "Heart Rhythm." I also want to be able to set up multiple if - them statements like if Amiodarone is entered then fill adjacent cell with Heart Rhythm and if Toprol XL then fill adjacent cell with Heart / Blood pressure and if simvastatin then fill adjacent cell with Cholesterol, etc. I have about 30 different options for cell 1 that I want to have auto fill in cell 2 based on the contents of cell 1. I've attached my file.
I want the user to be able to choose from the list or type the drug name in.
I need a macro that will find a cell by its contents (text) and then it will add the value of 1 to the cell under it IF the is data in the "E" cell of the same row. I would also need for it to do the same (add +1) for every other "A" cell under it.
View 9 Replies View RelatedI'm trying to do a column of individual discount calculations using a discount percentage found in a cell two to the right of a cell containing the text "Total SP:" that is always upwards and to the left of the cell where the formula goes (but could be two rows or could be 20). There are multiple "Total SP:" cells in the sheet - I always want the first one upwards. I have created the following but I get #NAME? where I hope to see the discounted value.
VB:
Dim Discount1 As Double
Discount = Cells.Find(What:="Total SP:", After:=ActiveCell, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True).Offset(0, 2)
Range("R9:R" & LastRow).Formula = "=IF(J9>0,$E9*(1-Discount1),0)"
I've just thought, the Cells.Find needs to be redone for each cell where the formula is inserted to ensure it always catches the correct discount and this isn't going to do that - it's going to find it once and always use that single value.
I think this needs a Loop or something and to move the ActiveCell down one after the formula to get it to redo the Cells.Find.
I need to find the "x" in range C2:C44 and replace it with a value (variable) specified in cell I2
The catch is that I then need to be ablt to change that character when I2 changes.
Coloum C
*VER
*T’x’STATUS
*ON
*OFF
*T’x’BANDS
EG
I2 - Variable = B
*T’x’STATUS will become *T’B’STATUS
I2 - Variable = A
*T’x’STATUS will become *T’A’STATUS
But " *OFF " should not change.
I can change *T’x’STATUS to *T’B’STATUS but then I can change *T’B’STATUS to *T’A’STATUS
Please see attached example file as it shows it better than I can explain it.
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.
View 2 Replies View RelatedI'm not sure if my requirement would be possible but I am trying to locate a text in cells on a separate table and then return a corresponding amount beside such cell to a cell beside the text I am trying to locate. I attached the excel file with arrows and everything.
View 5 Replies View RelatedI have the following code to find text on a sheet. It works perfectly except that I want it to find partial text in a cell as well.
Thus, if the text in a cell is Goodwood, it will find it if I type in Goodwood as my search. I also want to find Goodwood if I only type in Good.
The Lookat:=xlPart does not seems to work.....
Dim rFound As Range
Dim iCount As Long
Dim Text As Range
Dim MyRange As Range
Sheets("All Codes").Visible = True
[Code]...
Type/Unit Sold
Car-A/20
Bike-A/5
Bike-B/20
Car-B/50
Car-C/100
Bike-C/100
I need a formula if it's Car then sum it up to 170 and for Bike is 125.
The issue is to find the address (like A1) to the cell that contain specific text string?
I import text from a web page and I want to calculate some numbers from this information. All the information is spread out in 10-15 columns and 20-25 rows, and the specific value I'm looking for is imported into different rows as the dynamic web page changes, but the column is always the same.
The cell next to the left side of my wanted cell always contain the same text, so I thought I could search or scan the area A1 to P25 for this text and then extract the value of the cell on its right hand.
I have seen a few examples which find the last used cell... but these seem to grab cells that have formulas in them, even though no text has been entered. I need to know how to find the last cell in a worksheet that contains text.
View 6 Replies View RelatedHave read a few codes that allow you to automatically save a workbook using the value of a cell as the name of the file.
BUT, I can not find the code to save it if the cell is a date.
The 17/01/2009 wont save due to the forward slash.
Is there anyway within code that I can save the workbook by first removing the slashes?
I need to find a specific word from a cell and to paste it in another cell. Say example:
column A Column B Column c
1 Message to come Message
2 Print the following Print
3 logged in logged
I need to find a word "message" in column B and have to paste it in column C.Column C to be populated like the above.
I'm not very good at programming with excel and ive looked every where for how to do this but i cant find it ....
View 9 Replies View RelatedI'm evaluating the effectiveness of a keyword list we use to identify certain, high priorit,y medical situations from a string of text.
So, I have a column on a sheet of data (EIS Report!"B:B") in this case, and a seperate column on another sheet with keywords (Keywords!"A2:A47").
I use the formula ={OR(NOT(ISERROR(FIND(keywords!$A$2:$A$47,$B2))))} to identify if any of the keywords exist in the string.
This works well but I would like to improve my work in two ways:
1 - Identfiy, in another column on the EIS report sheet, which keyword it is that's been found within the string,
2 - On the keyword sheet, add another column of words, which if found within the string, would act as an exclusion.For example, I might use "STAB" as a keyword to look for "Male stabbed" etc, however might want to exclude "STABBING" to stop the solution triggering on "STABBING PAINS IN ABDO"
How might i adjust my formula?