I have data in cells B2:E2 and this can go down 100+ rows.
In column B i have invoice numbers but some cells contain the word "Deposit".
I have sorted this data so that the invoice numbers appear first and then all the Deposits.
I need a code to find the first instance of the word "Deposit" and to insert a row so that all the invoices and Deposits are seperated by a single row.
I have been able to make a excel sheet which takes inputs from userform for First Name, Last Name, Address etc. I have included a duplicate check for column "B" for "First Name". This checks if any existing data is already which matches the new data input for "First Name" through userform.
The userform only checks for the "First Name" check as required and gives a message that duplicate has been found. Then I have to close the userform and do a Control Key+F ( to find the new name for example, James) in excel sheet and validate that new name is same or different from existing name "James". This I want to do since this new name "James" may be another "James" as his "Last Name" is different. So even though First Name is same, since Last Name is different I know they are two different persons. In that case I will add the entry manually in sheet, instead of userform, since I would not be able to input the new data for "James" since the duplicate check with the current code will not allow me to do so.
What I am current trying is -
1. If the new name say "James" is entered through userform, then excel should point me to the existing row where the record for existing name "James" is there, say row 4.
2. Now without closing userform I should be able to see in the background excel sheet the search results for "James", as excel is pointing to that now. There may be multiple "James" in the existing which should be pointed out.
3. Based on the results that I see in the background excel sheet I can now decide that, this new name "James" is different from old "James" (of row 4) since his Last Name is different. Accordingly excel code should then ask me to add this record or discard this new record.
4. Duplicate check for First Name is enough for me. I would not require "Last Name" duplicate check.
I hope I have been able to explain my problem. I have also attached my current code as I am not able to attach any sample test file.
Code:
Private Sub cmdCancel_Click()
Unload Me
End Sub
[Code]....
I have two sheets with data. I wont excel to look for a value in colum A in sheet 2 the value to look for is defined in colum a in shet 1. If value is found it should insert in sheet one the value that is in the cell next to the found value. The data is not sorted. I have attempted and failed with using different functions.
View 1 Replies View RelatedI have the following code to search for serial numbers.
Private Sub CommandButton1_Click()
Dim Message, Title, Default, SearchString
Message = "Enter Serial Number" ' Set prompt.
Title = "Find Serial Number" ' Set title.
Default = "" ' Set default.
' Display message, title, and default value.
SearchString = InputBox(Message, Title, Default)
'SearchString = "Rob"
Set S = Sheets.Application
For Each S In Application.Sheets
With S.Range("A1:IV65536")
Set f = .Find(SearchString, MatchCase:=True, LookAt:=xlWhole, LookIn:=xlValues)
If Not f Is Nothing Then
f.Offset(, 3) = Date
Exit For
End If
End With
Next S
End Sub
I would like to amend this so that (a) if the serial number is not found I get a message box saying "Serial number not found" and (b) if the serial number is found, I would like it to highlight the relevant row (after inserting the date).
I have a spreadsheet which has "Employee: [agent 1 name]" in column A and it may or may not have the word "Break" in the same column before it mentions "Employee: [agent 2 name]". The amount of data between agent 1 and agent 2 varies and am needing code which will insert a row above "Employee: [agent 2 name]" if "Break" is not found, and add the word "Break" in column A on the inserted row. I would need this to loop through the spreadsheet until all 100+ agents have been searched.
I'm also needing this done for the word "Meeting" and would insert a row 2 rows above the next agent.
I need a simple VB Code which will check the vale in cell A11 in a closed workbook.
If A11=Lunch insert a blank row otherwise do nothing
What I have is a workbook where I have to filter on a column called "Code" in a worksheet called "Current" and search for varies criteria and then copy these filerted rows and paste into a worksheet called "Past" and then delete the orginal rows form the "Current" tab.
e.g. filter on a column called "Code" and filter on the criteria that match "Monday", "123" and "Customer Accepted" and paste at the bottom of the "Past" tab and then delete from the "Current" tab. A message should then be displayed saying "Data Transferred OK" with the curser then highlighting "A1" on the "Current" tab.
The only cavat is that the code may find none, some or all of the required criteria, however, in the event of finding no matching data then a message should then be displayed saying "No Data Found" with the cursor then highlighting "A1" on the "Current" tab
As I have to repeat this to filter on various columns for various criteria I intend to modify the code accordingly, however, in once instance I will have to filter on a column called "Code" and filter on the criteria 1,2 and 3 etc but then I have to do second filter on column called "Code Extra" and then do a filter on the criteria 4, 5 and 6 so the additional code would also be good.
HTML Code:
Sub CopyTimeColumn()
Dim tFIND As Range
On Error Resume Next
With ActiveSheet
Set tFIND = .Range("1:10").Find("Time", LookIn:=xlValues, LookAt:=xlWhole)
If tFIND Is Nothing Then
[Code] ............
This code searches the "Time" Column in first ten rows and copies it to another sheet...
If I have encountered with such a column whose title is "Time in seconds" then it will not search this column...What i want is the modification of this code such that it can detect column which contains any part of "Time" word in it ...
I'm having problems with trying to find something in the spreadsheet and inserting a row before it. For example I would like write code that basically goes threw my rows and if it finds the "2800" then it inserts a row before that. I have now bought several books but I have yet to come up with an answer.
View 3 Replies View RelatedThis code inserts a new column to the left of Column CV ...
View 6 Replies View RelatedI am trying to perform a simple insert macro. That will search column A for a value "Jack".
Once the value has been found insert a blank row, 2 rows up from that value. Not below.
I have a code that copies rows to another sheet if a value in D:D is found. BUT I need to add a "create sheet" IF the value exist.
So if VBA finds the value "TWO" in Column D, then insert a sheet at the end named "Two"
Then the code below will copy all the values of "TWO" over to sheet "TWO"
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
If Range("D" & i).Value = "9" Then Rows(i).Copy Destination:=Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
In essence, the VBA should:
Search for "ONE" in D:D, not find anything and move on.
Search for "TWO" in D:D, will find it, Create a New Sheet named TWO, then the above Code to copy all matching rows into sheet TWO.
Search for "THREE" D:D, not find anything and move on....
There will be roughly 12 sheets created on average, so if I create all sheets beforehand.
I would like a macro to order the columns as per the hardcoded list. If the column is not found than add the column at the position as per the hardcoded list. Below macro does the first part of re-ordering however if the column is not found how do I add an empty column:
Sub Reorder_Columns()
Dim ColOrder As Variant, i As Integer, Found As Range
ColOrder = Array("LogicalFileName", "LogicalFilePath", "UploadedDate", "UploadedBy",
[Code].....
I manage to do a proper search and the return value is correct, but the problem is when I don't get the correct value excel gives me an error, what I can add to make the result just to give me MsgBox "Not found"?
Code below:
Set Ran = Worksheets(2).Range("A:A").Find(CompName, lookat:=xlPart)
If Not Ran Is Nothing Then
MatchRow = Ran.Row
MatchCol = Ran.Column
End If
I am looping in Excel to find "NUMBER OF EMPLOYEES". IF found I want to go up one row and replace the first 4 digits with " 9ZZ". The Line 1 data will alway be different so I just want to replace the first 4 digits. I don't want it to effect the rest of the line.
Example:
Line 1 - 100 6300 BRCH TOTALS
Line 2 - NUMBER OF EMPLOYEES
RESULTS NEEDED:
Line 1 - 9ZZ 6300 BRCH TOTALS
Line 2 - NUMBE OF EMPLOYEES
I have a workbook with a sheet for every month. I have a searchbox searching for a client and jumping to that location. The problem I am having is that it takes me to the last occurence in the workbook if there is more that one client with the search criteria. Do you have any suggestions for a searchbox that whould take me to the first occurence and give me an option to go to the next occurence in the workbook. Please see the code below that I am using currently using.
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim cl As Range, rng As Range
For Each ws In ActiveWorkbook.Worksheets
Set rng = ws.UsedRange
With rng
Set cl = .Find(Me.TextBox1.Value, LookIn:=xlValues)
If Not cl Is Nothing Then
Application.Goto cl
Else
I need a VBA code that will search column H for any value that DOES NOT start with "9" (this is a character field). If it finds a cell that doesn't start with "9", I would like it to delete the entire row. It will need to repeat this process for every cell in Column H that has a value and then stop.
View 7 Replies View RelatedI have a list of chronological dates from left to right in a single row starting in H1 and going out to say BY1 or further
In a single column (C:C) I have random dates.
I want to insert a shape when the dates in column matches the chronological list of dates in (1:1).
I want the shape to be inserted on the row on the random date and the column of the matching chronological date.
I have this so far:
VB:
Sub Shape3()
'
' Shape3 Macro
[Code]....
This works OK but for some reason the shape changes when the next random date in column C is not found.
I need a VBA script that can display a search box on multiple sheets within the same workbook similar to using ctrl + F and search values in column B only. If there is text or the row happens to be empty then it should skip that and only search numbers. Also the numbers in column B range from 50000 to 89000 and if there is a wrong number entered then I want to have a pop up box saying Error: invalid value or something like that.
Sub search_box()
Range("B49000").Select
Cells.Find(What:="some#", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
End Sub
In a large spreadsheet that receives external data, I have codes (U, N or V) allocated at various times to different rows as shown in sample worksheet attached. Elsewhere in the spreadsheet, I need to display (for later export) these codes along with their respective number in a sorted list. Please review the attached:
In the spreadsheet you'll see a column of codes, the next column is the data reference number, then to the right is three columns, one for each code. As displayed in the sample book attached, each Code column is to display the data reference number (from column 2)that matches that column's code.
What formula can I use to list these numbers in the appropriate columns?
I was looking at this VBA code the other day and i was wandering what i meant. I thought if somone could expliane line by line,
Sub CommandButton1_Click()
Dim ThisAddress$, Found, FirstAddress
Dim Lost$, N&, NextSheet&
Dim CurrentArea As Range, SelectedRegion As Range
Dim Reply As VbMsgBoxResult
Dim FirstSheet As Worksheet
Dim Ws As Worksheet
Dim Wks As Worksheet
Dim Sht As Worksheet
I have two workbooks, one is used for importing items to the site while the other is a monthly product list. In each of these I have a list of sku codes. I need a macro that will search each sku from the import to the entire product workbook. If it's found then delete the whole row from the product workbook. I have attached the examples below.
View 6 Replies View RelatedI am using excel excel 2007 in windows 7. I have a search button that works great, however, I want it to also highlight the cell green, when it finds what I am searching for. Here is the code below for the search button I have.
Private Sub CommandButton6_Click()
Dim searchthis As String, Found As Range
Me.Unprotect Password:="123"
searchthis = InputBox("Type Number.", "Property Search")
searchthis = searchthis & "*"
Set Found = Range("A:A,e:e").Find(What:=searchthis, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not Found Is Nothing Then Found.Select
Me.Protect Password:="123"
End Sub
I found some useful code for copying specific worksheets into a new workbook based on the sheet name, which I have not been able to alter to suit my needs.
I have a workbook that has a Master Sheet that contains a summary of each claim (Name, type of claim, dates, dollar amounts, etc.) and a sheet for each specific claim.
I know I can use the filter feature on the master sheet to view all of the physical damage claims (and one column has a hyperlink to the worksheet for that specific claim). But every month I have to create a report that shows each type of claim, dates, and so on.
I would like to create a macro that can copy the worksheets to a new workbook based on the type of claim it is. I'm thinking something like for each ws in wb if range ("F15") = 1 & .range ("B4") < 30 days from today copy that sheet into a new wb.
Below is a generic and far smaller version of my workbook. The name column contains my hyperlinks to the specific sheet. Each sheet is also named based on the text in the name column (so John Doe's sheet would be named 'John Doe') and so forth.
Date of Loss
Name
Cargo
BI
PD
Paid
Reserves
[Code] ........
i have a workbook with several sheets in it. i would like to make a userform were i could put a number in a text box eg E045698.then use a command button to search all the worksheets for that number and display the sheet number where that number is in another textbox.
View 5 Replies View RelatedI have a workbook with many many sheets in it. The first sheet contains a single column with about 10,000 different values. I'd like to use each of these as search criteria against ALL data in the other sheets (of which there are a good 50 or so). If matches are found (they don't have to be exact case), then I'd like two things to happen:
1. The rows containing the matched search criteria in the first sheet are highlighted.
2. In the cells adjacent to the search criteria in the first sheet, hyperlinks to the matched data are created and named after the sheet upon which this matched data appears.
I've attached a sample file to this post with ideal sample 'answers' to queries made of the first 2 terms.
I am trying to search through column "I" for numbers that are decimals, when the first decimal is found, use the row number that the first decimal appears in the string that populates the bookmarks stored in a word template I have created. The code I have so far checks for the decimal and then populates the bookmarks, however it requires me to input the ranges manually, meaning I can set them as the first row in my tabel. But if the decimal appears in the second row and not the first I'm bookmarking the wrong data. Here is the code I have so far;
[Code] ........
I've attached a dummy worksheet that shows constant values of 1.00 in columns D and E. In the actual workbook, user action will sometimes cause these values to change to something other that 1.00. The user may wish to delete the action that caused the change.
I need a macro that will search from the last cell in column D upward to the first instance of a cell that is not equal to 1.00 and select that cell and those immediately above with the same value as the first found cell.
For example, in the attached worksheet the search would start in the last cell of Columd D and search upward. It would find cell D23 and upward to D19. These entire rows would be deleted.
I have written a macro to convert data from a report exported from Salesforce.com to a format that is acceptable for upload to our website. One of the necessary conversions is to convert values from a Boolean value to a Y/N value. I wrote a macro with a series of search-replace commands such as this:
Code:
'replace 0s and 1s in "Direct Billing" column with appropriate data
Columns("R:R").Select
Selection.Replace What:="0", Replacement:="N", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
This code generates a warning when no "0" values are found:
"Microsoft Office Excel cannot find any data to replace. Check if your search formatting and criteria are defined correctly. If you are sure that matching data exists in this workbook, it may be on a protected sheet. Excel cannot replace data on a protected worksheet."
Is it possible to search a worksheet that may contain different number of rows (from day to day) and for every instance of a word like "HOLD" is found in column "C" to Color that intire Row Red and also Bold the Row as well?
View 9 Replies View Related