I have to do a project, but I'm not really sure how to go about it. Here's my problem: I have about 200 stock ticker symbols already given(ie. AAPL, MSFT, NEE, etc...), and i have to create an excel file where i would copy/paste or enter in another set of stock ticker symbols and would give me back the answer if any of the newly entered symbols match the ones i already have(either in form of 1 or 0, YES or NO etc...).
View 4 RepliesUsing the search macro code below, could someone please help to add in more codes what I'm currently using, and also where to insert it. The Search function works well for what I need and it helps me to locate data. When using the search function somehow it search all sheets within the workbook but I only want it to search an array of sheets when using this macro that is needed to complete the task for what I'm after.
Macro
Public Sub FindText()
'Run from standard module, like: Module1.
Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer
myText = InputBox("Enter the text that you want to search for:", "Start Search!")
If myText = "" Then Exit Sub...................
I'm from Bavaria, Germany. Right now, I am doing an internship for my studies. my problem: I need a search procedure which shows an Input Box where you can enter a word to search for. It should work like the original Excel search (Ctrl-F), but with a simpler design, like with my own Text "Enter your Query" and a Button "Submit Query" / "Quit search". Is there the possibility to Highlight the Search Target? The problem hereby is that this highlighting should not be permanent. That means the user sees the target for which he searched for, the cell highlighted in a different color, etc. But as soon as the user clicks onto another cell, etc., the highlighting vanishes. If there is no fitting match, there should be a MessageBox like "Sorry the Target xyz cannot be found"
View 9 Replies View RelatedI' having trouble using the =IF(ISNUMBER(SEARCH formula to search multiple cells.
I can get it to work to search one cell (as below):
=IF(ISNUMBER(SEARCH("Same",G10)),"No Change",'Aug 09 Matrix'!F10)
(In this case the respone is No Change as Cell G10 contains "Same")
However cannot get it to work for several cells. I have pasted below and highlighted the function I would like it to perform:
=IF(ISNUMBER(SEARCH("Same",G10:R10)),"No Change",'Aug 09 Matrix'!F10)
Essentially the other cells in that row (G10:R10) all contain "Same", however for some reason it is identifying this as FALSE and putting in the data from the 'Aug 09 Matrix' sheet.
I have an 'Update' Module that search the value of the Userform1.txt_sc.Value in Column B, but I want to search to 2 criteria, how can i amend the below code to search for 2 criteria ( userform1.txt_sc.value & userform1.txt_linenum.value )
View 2 Replies View RelatedHow I can confirm whether a particular value exists in a range. i.e.
ABCD11Yes/No22
Does the Number 4 exist in the range
A1:A8?334455667788
Whenever updating an Excel file and saving it as a new file, a "Confirm Save As" window appears. The default is "NO" (No is Highlighted). Conversely, in MS Word, the default is "Yes" (and, YES is highlighted). I would like to change the default from NO to YES in Excel.
View 1 Replies View RelatedI'm trying to prompt user when he tries to delete a value from a cell so he can confirm is action but not the same way to all cells.
Let's say that I have cells A1:F10 with values, and user should be able to change this values but not to delete them (can't be a blank cell) and I want to prompt him that info (something like "Cell can't be blank!").
In other range a want a different thing: let's say cells H1:K20. In this cells user should be able to change cells values and also delete them but I want to ask him for a confirmation (something like "are you sure").
pop up form within userform to confirm choice. I have a button within a userform that clears the form:
View 5 Replies View Relatedi have a macro button designed to wipe all user entered data into a workbook. I want it so you cant accidently press (as you cannot undo the operation). Anyway for making a dialog box appear asking if you are sure to wipe date, with a yes or not option.. no will obviously abort the macro.
if possible, another box to come up asking if you want to save the file first with a different file name (comes up with a save as box).
this is the code so far:
Sub MonthCleaner()
'
' MonthCleaner Macro
' Macro recorded 17/10/2005
'
Application.ScreenUpdating = False
Range("A10:G10").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("H10").Select .........
I have a button. When pressed, it runs a macro. Instead, after pressing the button, I would like the user to confirm to run the macro and display some info about it. This is to make sure the user does not accidentally run the wrong macro (button). I just need a prompt box, with a line info, and the two choices YES and NO. If NO is selected, nothing happens. If YES is selected, macro continues.
View 6 Replies View RelatedI have an excel form where users can input modifications by selecting a range and then pressing a command button to input a value in the selection.
My goal is: to restrict the location where a user can select a range of cell and then execute the macro . The cells where the user can use the macro are merged and in my code, I used the address as show in the address bar in excel for their location. If the user select a cell that is not permitted, a message box is displays and exits the code.
If (Selection.Address <> Range("AE7").Address) _
Or (Selection.Address <> Range("BM7").Address) Then
Msgbox ("Wrong location") Else
For some reason it doesn't work since the message box appears even if I am within the permitted locations.
Note: AE7 is the first cell of a merge, same as with BM7
I'm trying to use the following formula to verify if the "," is within a cell. Something is not right, because even for True conditions, I'm getting a False return. I think my True logical test is not written right.
View 3 Replies View RelatedI'm using Excel 2002 on and XP machine. I am creating a spreadsheet to track a work plan.
Column A: Name
Column B: Date
Column C: Time
Column D: YES
Column E: No
Column F: New Date
Column G: New Time
What I'm looking to do is input click able buttons in Columns D: Yes and Column E: No.
When a user clicks on YES, a pop up window should appear with the data in columns A,B,C.
The p0p-up should contain CONFIRM and CANCEL buttons.
Asking the user if they want to confirm the date and Time.
If CONFIRM is clicked Excel should then send an email to me and the user to say that the work schedule has been confirmed.
If CANCEL is clicked the pop-up window closes.
Secondly if the user clicks NO, a pop-up window should open with two free text boxes. One for Proposed date and One for Proposed time. And again 2 buttons for Confirm and Cancel.
If Confirm is clicked then Excel should record the data entered in the free text boxes into Column G and Column H and send emails to me and the user with the Proposed date and Time Data.
If CANCEL is clicked the pop-up window closes.
I've written some VBA which creates worksheets to temporarily store data in. At the end of the code I delete the worksheets. However, for some reason I am getting a message box which asks for confirmation. How can I avoid the user of my report having to click OK for each of the worksheets which need to be deleted?
View 3 Replies View RelatedI am trying to do a macro that looks to see if the date is different from one cell to another going down in rows. I got it to work until the cell is empty then it says the date dont match but I just want it to stop the loop. so it should go down a list check to see if the date is the same all the way down the list, stopping if cell is blank, if not give a message box, if it is the same stop.
Is As follows
Dim rowNum As Integer, colNum As Integer, currCell As Range
rowNum = ActiveCell.Row
colNum = ActiveCell.Column
rowNum = rowNum + 1
'get first cell
Set currCell = ActiveSheet.Cells(2, 3)
'loop while cell not empty
Do Until currCell.Value = " "
If currCell.Value = ActiveSheet.Cells(2, 3) Then
If currCell.Value = "" Then
End If
I have a macro which works well. All it does is save a file to a specified location on my network. However, after a file is saved there is no prompt telling the user that the file was saved successfully leaving them wondering whether the macro actually worked.
VB:
Public Sub SaveAs() ThisFile = Range("X2").Value
ActiveWorkbook.SaveAs Filename:="legdcAudits" & ThisFile & ".xlsm", FileFormat:=52
End Sub
1. I can't remember it, but I know there is a command so that the view hold and that the selected tabs and cells does not show and the same view remains until the "unhold" is called in the macro. THEN the view is updated. What is this command again? Could not find it...
2. The macro needs to delete tabs at the click of a button (easy part) but for each deletion, a message appear:
Data may exist in the sheet selected for deletion. To permanently delete this data, press Delete. Is there a way to prevent the message from popping out when the macro is executed?
I would like to use the search function in vba to look for a 5 digit number in sheet1(at a specific cell) from a list in sheet2- ie the range. Am I using the range correctly? This code is in a command button in sheet1.
Dim Z As Integer
On Error Resume Next
Z = Application.WorksheetFunction.Search(111, Worksheets("Sheet2").Range("a1:a100").Value, 1)
[Code].....
I need to create a function that searchs one definitive word or phrase inside of a text in the Excel and creates a vector with the address of the cells that contains it.
Inside of this function, to create the option to change the color of the source or the color of deep, or to erase the indicated cells
Function SearchValue(SearchedValue As String, Interval As Range)
Dim Célula As Range
For Each Célula In Interval
If InStr(1, UCase(Célula.Value), UCase(SearchedValue)) 0 Then
If IsEmpty(SearchValue) Then
SearchValue = Célula.Address
Else
SearchValue= SearchValue & ";" & Célula.Address
End If
End If
Next Célula
End Function
Can you use the Search function in VB? If so what's the code? Do you have to call it first ( Call Search .....)
View 2 Replies View RelatedI am trying to workout a formula to look-up and categorize data from a bank statement, without having to complete manually monthly.
So far I have a formula that returns the amount spent, but would need an extra column and separate table for each category.
IF(OR(ISNUMBER(SEARCH(" "&$G$3:$G$11&" "," "&A2&" "))),B2,0) where
Instead of returning B2, I would like to return a value from the table in column H3:H11 that corresponds with G2:G11, as shown below.
ColumnG ColumnH
Aldi Food
woolworths Food
Coles Food
saint Nicholas School
Blooms Medicines, toiletries, hairdressing, personal items
vodafone telephone
So for example if Aldi is in the text in A2, then I would like to return food ( from a lookup table similar to above) into the column where my formulae is ( say F2).
how to type a name into one cell and have all the places in a particular range (a1:H12) that displays that name to highlight a color.
View 14 Replies View RelatedI have recorded the below code, is it possible to adjust this so that instead of using "USD" the macro will reference whatever currency the user inputs into say sheet1 cell A1?
View 2 Replies View RelatedHow I could create a search function with this document attached. I want to be able to pull the kids name, location & information over to a search engine when looking for them because I will have over 40 different worksheets with numerous names on each of them.
View 5 Replies View RelatedI'm having trouble finding the correct way to use this formula. Basically, I need the formula to populate what is in another cell, minus any apostrophe's ( ' ) that are in the text. Sometimes the text contains the apostrophe and other times it does not. I just want whatever text is in the cell to populate, whether it contains an apostrophe or not.
Here is what I've been using so far, but it only produces a value if there is an apostrophe. If there isn't an apostrophe in the text, nothing populates.
=RIGHT(Input!G2,LEN(Input!G2)-SEARCH("'",Input!G2))
I would like to search a cell and look for a certain value in it, and if it is, for there to be an X placed in another cell. Column A has the values, I would like to put the function into column B, and the X to appear in column C. I will attach a spreadsheet if it is needed, for this particular function, I will say we are searching for PP in the cell
View 2 Replies View RelatedI have 6 worksheets with data. Column C of every worksheet is filled with unique Tracking Numbers.
I want to create a 7th worksheet with a search function. Users will be able to type in a Tracking Number (on cell A2), and information associated with that Tracking Number (from Columns A, D, E, F and G of worksheets 1-6) will show up beneath cell A2 on cells A4, B4, C4, D4, and E4.
I'm trying to develop a formula that can incorporate the search function in amongst a countifs formula. I have a column that contans the string "2.3 Manage Project Delivery" in a single cell. However, a single cell could also contain this text string in amongst other text and be in there multiple times - E.G; "2.1 Manage Customer Support, 2.3 Manage Project Delivery, 2.4 Close Program, 2.3 Manage Project Delivery" etc
My formula currently looks like this: =COUNTIFS('RDC Register'!$AW:$AW,"Not Overdue",'RDC Register'!$C:$C,"2.3 Manage Project Delivery") but it's not counting the cells that have 2.3 Manage Project Delivery in it more than once.
So basically I need to modify my formula to search for this text string in the cell and add all occurrences to the final count.
Formula 1 : =IF(SEARCH({"spinning"},A2),"AUTOGEN")
Formula 2 : =IF(SEARCH({"typing"},A2),"Manual")
I want to combine above two formula's with "OR" function.