Find Date String And Paste Into Different Column But Same Row
Feb 5, 2014
I have a report where I would like to have a macro search for a date string in columns N-S and copy the date to Column C, same row. The number of rows will vary from day to day. I would then like to format all of Column C to 'mm/dd/yyyy'.
Attached is a file where the date string is included in Column N on row 2 and Column O on the row 3. The dates for both these lines would need to be copied and pasted to their respective rows in Column C.
View 14 Replies
ADVERTISEMENT
Jul 22, 2014
There are two problems to solve :
My Excel workbook is composed of 2 sheets. The first one is a Dashboard (Sheet1) and the second one (Sheet13) is where the data is.
I would like to press a button on the first one, enter the text to be found into an Inputbox and then copy-paste cells from the second sheet (containing the data) into the first one.
The data is structured in rows, from A to V columns.
I would like, according the row where is located the found cell, copy given given cells from Sheet13 to Sheet1.
Right now, I have a macro performing the search like desired and selecting the found cell :
Code:
Private Sub CommandButton1_Click() Dim FindString As String
Dim Rng As Range
FindString = InputBox("Entrer le contrat de support - rechercher (DSI....) ")
If Trim(FindString) "" Then
With Sheet13.Range("V:V")
Set Rng = .Find(What:="*" & FindString & "*", _
[Code] .......
I would like to copy cells located in the columns A, B, D, E, F, K, S, (T:U) from the row where the searched string has been found in Sheet13. It has to be copied on Sheet1, on two rows : (N29:Q29) & (N30:Q30).
When a new research is done, the previously copied cells should be cleared out.
2. The second point is about duplicating a button with an associated macro. The macro is running like I want but I have to insert 299 more buttons, with the updated formula according to the row where it is located.
However, one part of the macro has to stay the same because all of these 300 buttons increment a single counter located on Sheet1.
Code:
Sub Button2600_Click() If MsgBox("Etes-vous sûr de vouloir ajouter 1 année de support pour" & vbNewLine & Range("E7") & " " & "(" & Range("F7") & ")" & " ?", vbYesNo + vbQuestion, "Modification du Contrat de Support") = vbYes Then
Sheet13.[A7] = DateAdd("yyyy", 1, [A7])
MsgBox "Contrat étendu d'une année."
Dim x As Integer
x = Sheet1.[R11].Value
Sheet1.[R11].Value = x + 1
End If
End Sub
You can get the excel workbook at the following link : [URL] ....
View 2 Replies
View Related
Feb 8, 2014
This follows on from my previous posting [URL] ..... which produced a solution using an ActiveX Combobox that unfortunately does not work on Mac PCs!
I tried to replace the ActiveX with a Form Control Combobox but could not make it work.
So I am trying to use the alternative of "find, copy and paste" the relevant information.
As shown on the attached 140207 FINDALL test.xlsm, I need to find all records containing whatever string is entered into the "Search" cell, and copy data form three columns onto the Entry sheet.
The User will then select whichever of the entries they want to use, which will populate the relevant cells.
Problem: The following Code is not recognising any of the data in the Column being searched.
VB:
Option Explicit
Sub FINDPARTS()
Dim ws As Worksheet, i As Integer, k As Integer, z As Integer, CL, myFind, CHOICE As Range, lr As String, lrG As String,
[Code] ......
View 2 Replies
View Related
Aug 29, 2007
How do I search a given text "GNMA" from a cell (A1) and (A2) and return it in other cell (B1) and (B2).
Example:
A
1 GNMA-4565X18-ROE
2 895GNMA-12
View 9 Replies
View Related
Mar 11, 2009
I have the file here i work with, basically the first column is a legend and the column to the right of it is a pointer column to help me find out where a legend is located in another file. So i was wondering if a macro could be made to basically find where the "legend column" A, C, E ect ends ( every other column is a legend column , one next to it is a pointer column ). and then combine the ends all of the columns contents and put them into 1 column.
In the file with this question i have showed you what i start off with, i highlighed in yellow where each column legend ends, ( normally these are not highlighted and i find them manually ). In the 2nd tab i show what the end result should be. All the columns are now consolidated into 1 column. 1 after the other.
View 4 Replies
View Related
Dec 9, 2006
I work for a chicken hatchery. So, a couple preliminary things by way of explanation:
1. The day we place our eggs in the incubators, we fill in an excel file containing a list of all the farms from which those eggs came. This is called the "Egg List" workbook.
2. After 21 days have passed, and the eggs have turned into chicks, we open that original file. We copy cells from one column of that file (the workbook is called the “Egg List”, we copy “Sheet1” cells B7:B50), and paste them into another Workbook called “Chick List.”
My question is this:
Is there a macro I can put into the “Chick List” workbook that will automatically locate that particular “Egg list” workbook file that is 21 days old, and paste the contents of its cells B7:B50 into cells A7:A50 in my “Chick List” Workbook. It is preferable if this can be done without opening the old “Egg list” file. But if this is not possible, I’ll take whatever I can get.
Here is what I have, which works okay as far as finding the file. I bootlegged this from another part of this helpful website. But I cannot figure out the syntax for getting cells B7:B50 from the Egg list to copy into cells A7:A50 of the Chick List.
Dim FSO As Object
Sub ProcessFiles()
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim this As Workbook
Dim cnt As Long
Dim filenew As Object
View 5 Replies
View Related
Mar 12, 2009
Currently there are 3 sheets in my wbook, however there are likely to be 30 or so soon enough. Each sheet has loads of Cols of data and no sheet is generally the same.
I'm after my "Control" sheet to display a replica of the columns entitled "Destination" found in all other wsheets. i.e. each sheet has a destination col and I need an automated way of getting these lists side by side.
The challenge I'm faced with is that the destination col in each sheet is never usually in the same column, i.e. sheet1 could be colA, sheet2 colB etc and each sheet might contain more than one destination col,
View 9 Replies
View Related
Jul 9, 2014
Why my code is not working. I am working with Excel 2010. It will only delete the column on the active spreadsheet and not go to next worksheet. Not all worksheets contain the word "Broker".
[Code] ......
Not sure if its something to do with Activecell, try After:=ws.cells(1,1) or passing it in as the active cell stays the same i think.
View 3 Replies
View Related
Aug 18, 2008
whats the most effiecient way to find all values under column B2 and all values in column O2 and paste them in column U2?
View 9 Replies
View Related
Aug 1, 2006
The idea is that i have several monthly data in columns. I also have a subtotal column that can be a "Total Trim I", "Total Trim II", "Total Trim III" or "Total Trim IV". I also have a yearly total: Jan Feb Mar Total Trim I Apr May Jun Total Trim II Total 2005
I wanna cut the "Total Trim I" column, paste it after the Yearly Total and cut and paste the "Total Trim II" after the "Total Trim I" column for every year... It'll look like this: Jan Feb Mar Apr May Jun Total Trim I Total Trim II Total 2005. But not every trimester has 3 months... I've been thinking about a find function but i have troubles with the implementation.
View 2 Replies
View Related
Apr 12, 2007
how does one go about finding a specific valuein a worksheet, then selecting the value in the next column, then pasting the value in a different worksheet?
For example, I want to find a particular person's name in a worksheet, upon finding the name, I want to select the number associated with the person which is in the column to the left of the name. I then want to copy and paste the number in a different worksheet.
View 4 Replies
View Related
May 23, 2009
I've attached a workbook which contains two sheets:
PAYM
DEVICE_PAYM
Data is updated on DEVICE_PAYM each day with the figures I need located in column "AN". I need to be able to copy the data from "AN" and paste it into a column on the PAYM sheet. The destination column on the PAYM sheet needs to be specified by inputting a specific date - ideally, I would like to use the calendar Add-In as part of this process. I've already input some code for the calendar but am unable to develop this further so that it finds the correct date column and pastes the data across from the other sheet.
View 2 Replies
View Related
Aug 17, 2009
My goal is following:
I do an inventory of my schools equipment and have much info in many columns. But I just want to concentrate on column A.
This is example of the room names at my school and look like this:
Column A
A01
A01
A02
A02
....
View 10 Replies
View Related
Oct 9, 2009
Essentially, I have 2 Sheets:
"SheetWSS" = data to be copied
"SheetWSD" = destination of copied data.
Below is a sample of the data in "SheetWSS"
----------A------------------------B-------------------------C
1------Trade ID -------Description---------System no.
2--------579----------------Loan ---------------------- 7
3--------580----------------Deposit---------------------22
4--------702----------------Deposit--------------------- 11
5--------703----------------Loan ----------------------- 58
6--------732 ---------------Loan------------------------66
7--------733----------------Deposit-------------------- 99
etc...(no more than 10000)
Now, an explanation of the data:
1) I work for a small bank (CORP) that takes deposits and gives loan.
2) CORP books these trades using only system no. 7 and 11; other system nos. belong to customers.
3) When a single trade is booked, the 2 sides of the transaction is recorded (by Trade IDs) . E.g rows 2 and 3 relates to one trade. So if CORP loans money with system no.7 to CUSTB, who uses system no.22, it shows for CORP a loan and for CUSTB, a deposit.
4) Everyday, the data in "SheetWSS" is updated with a different number of rows from previous, but the number of columns remain the same.
So, here's what i'ld like the macro to do:
1) Go into Column C, find system no.7 and 11.
2) For each 7 and 11 found, Copy the next row . So if for e.g, system no.11 was found in C4, i want the ENTIRE ROW 5 to be copied.
3) Paste the entire next row in "SheetWSD" until we have a list of all opposing sides of the same transactions initiated by CORP.
View 3 Replies
View Related
Jul 31, 2008
1. For a file moving from one cell to the next, down the column, get the values and seach for the values in file number two.
2. If that value is found, copy a cell from file 1 to a cell of a column on the same row where the value was found on.
3. Do what was done on number two, but with a different column.
this is what i have so far...
Sub replace()
'
' Macro2 created by david
'
' for the entire sheet, moving from one cell to the next down the column, changing rows
' search for the contents in another sheet, and then if that is found,
' copy the row number to a variable, and then
' copy column K from sheet 1 to column N of sheet 2, using the same variable above
' after that same, but L goes to P.
'
'
Windows("file1.xls").Activate
Rng = Selection.Rows.Count
Dim toFind As String
ActiveCell.Offset(0, 0).Select
Application. ScreenUpdating = False
Dim i As Integer
For i = 1 To Rng
Windows("file1.xls").Activate
toFind = Range("A + i")
Windows("file2.xls").Activate
View 7 Replies
View Related
Apr 4, 2014
I have a 2 groups of column headings with a different month and year in each heading so
1st Group of columns range
Columns AJ through AX
Column Heading example "Expense Ratio February 2013......next Column over is "Expense Ratio March 2013"
2nd Group of columns range AY though CE
Column Heading example "Capital Balance February 2013......next Column over is "Capital Balance March 2013"
Each new month I need to add a new Expense Ratio column after the most recent expense ratio Column. (i.e. Find "Expense Ratio March 2013" and I need to add a column after that with heading "Expense Ratio April 2013"
Same thing for Capital Balance - add a new Capital Balance column after the most recent Capital Balance Column. (i.e. Find "Capital Balance March 2013" and I need to add a column after that for "Expense Ratio April 2013"
Because the ranges keep changing month over month, how do i do this.
View 4 Replies
View Related
Dec 14, 2008
I have a workbook that has 397 sheets. On many of the sheets (but not all) they have some of the same data. On sheet 1 cell B5 is ContactID, Sheet 15 cell B32 is also ContactID. Sheet 1 A5 needs to reflect Contact ID and Sheet 15 A32 needs to reflect Contact ID. I need to find all sheets that contain the data "ContactID" and place (paste?) Contact ID in the same row, but in Column A.
Right now I am doing a Find all and clicking through the sheets 1 at a time (some of these finds though have 30+ sheets).
View 14 Replies
View Related
Jun 25, 2014
I need to create a data base from figures extract everyday. I would like according the date to copy the figures in the sheet "Extractor" to paste in the column corresponding to the day of today in the sheet "Database". If possible as well, the figures of the dates before today must be delete (in the color tab)
I join a sample of what I have. I tried with the indirect function, but didn't find how to keep the figures of the previous dates.
View 2 Replies
View Related
Apr 10, 2009
I have 2 columns: Locations (Column A) & Dates (Column B).
I want to find the most recent date for a selected Location (stored in $G$1).
currently I have in C2: =If($A2=$G$1,$B2,"") copied down the column to the end of data (currently C153). Then in C1: =Max(C$2:C$153).
I also have in D2: =If($C2<$C$1,$C2,"") copied down to D153, and in D1: =Max(D$2:D$153) to find the 2nd most recent date.
Is there some way to condense this into just 2 cells?
See attached file for example (note the columns are currently sorted by date, but that is not always the case).
View 14 Replies
View Related
Nov 14, 2012
I need a formula to find the latest date (Column A) a given value appears in Column B.
Column A is a list of dates.
Column B can contain any of 6 values (For example: A, B, C, D, E, F).
And, to make it even more fun, the system I'm working on doesn't allow macros.
View 2 Replies
View Related
Dec 19, 2011
I need to find the last instance of "IO" in column E and copy cells in columns B to E for the row below to another tab called "OP" cell O9.
I need the VBA code for Excel 2003.
View 1 Replies
View Related
Jun 1, 2012
I am trying to work out some code that will allow me to search worksheet2 to find the last filled cell in column A.
I would then like to copy the contents of the cell.
Then past this data into the first empty cell in a column in worksheet2
View 1 Replies
View Related
Dec 22, 2008
I am trying to find the formula to find the greatest number/date etc in a column, I'm not sure which one I should be using?
For example I have a list of dates in Column B and I want in cell A1 to show the latest date in that column, so if the dates are
21/12/2008
05/02/2009
it would show 05/02/2009 in cell A1.
View 3 Replies
View Related
Jan 2, 2013
I've been using the following code to go the current date on a spreadsheet. I now have to add the current date somewhere else on the sheet How do I modify the following code to only have it look in A:A ? Date is formatted -- m/d/yyyy (1/2/2013)
HTML Code:
Sub gotodateday()
Dim C As Range
Set C = Cells.Find(Date)
If Not C Is Nothing Then C.Select
End Sub
If I name ranges as Months, (Example: January_13. -- I can use the month without the year if it makes a difference),. How would I change it to look for the current month in A:A.
View 6 Replies
View Related
Mar 3, 2008
What do I have wrong with this? I want to find a cell that matches today's date and then start with that row by copying a time value in another worksheet to the one that has the dates already.
Sub PunchClock()
'This will copy the current time punched and place it into the correct cell
Range("B3").Select
Do Until ActiveCell = Now()
ActiveCell.Offset(1, 0).Select
Loop
All I need is some direction with this. I tried setting up a variable then check that variable against the current date and I didn't have that one figured out either.
View 9 Replies
View Related
Jun 6, 2012
I want to find the most recent date in column D, when the variables in column E are all the same. There are many different variables in column E, and for each group of the same variables (eg. all cells containing "A"), I want to find which is most recently dated (dates are in column D). This is my code so far...
Sub adddiv()
Dim ticker As Variant
Dim freq As Long
Dim csheet As Worksheet
Set csheet = Worksheets("Sheet2")
[Code] ....
View 3 Replies
View Related
May 16, 2008
I have created a simple command button for tallying that will increase the count in the output cell by one every time it is clicked. It has basic code like this:
Private Sub ProductRegistration_Click()
[D4] = [D4] + 1
End Sub
On top of that, I want the output cell of this command button to switch every day. In this case, it would move to E4 tomorrow. The columns are dated but I can't figure out how to get the output cell to automatically change with the system date so I don't have to manually change it every day.
View 3 Replies
View Related
Mar 22, 2008
I have a spreadsheet with a column A of dates April 08, May 08, June 08 etc. Adjacent to each of these dates is a value in column B. I want to select the appropriate value relevent to the current date and use it elsewhere. Therefore if it happens to be Oct 08 when I open the spreadsheet I want the value in column B, adjacent to Oct 08 to be represented.
View 3 Replies
View Related
Dec 26, 2013
I have a spreadsheet where a date is entered in column A with data in B-F. I am trying to write code to look in column A for a specific date and enter additional data into columns G-L on the same row. It needs to be able to skip the rows that are blank. So I have the dates of 12/1, 12/2 and 12/3 in column A rows 1,2 and 3 and I want to enter data for 12/2 on row 2 skipping the blank cells in row 1 for 12/1. Here is the code I have below.
VB:
Private Sub CBSecure_Click()
Dim my_name As String
Dim r As Variant
Dim l As Long
sFind = DockDoorCal.Value
If Trim(sFind) = "" Then Exit Sub
[Code]....
View 4 Replies
View Related
Feb 3, 2014
I would like to design a macro that can find a value within a cell in a colunm and copy a value in the row then count occurances of that value next time a match in the colunm is found on a different row, but also do this only if the date range in the row is within a specified range. So a multi-critieria search and copy operation.
To explain a little better here is a simplified example of my spread sheet that is in input for the macro.
A
B
C
D
E
1
PART NO.
date in
date out
comp1
comp2[code]......
Column A lists the coponents found on the same row as the seach critria, in this example "12-3".
Column B lists the number of occurances of the components. This search is limited to a date range input by the user.
So what I can see is the components replaced and the amount of times replaced on a specific item over a fixed time.
*It is difficult to describe this problem without showing the macro output, but this is just an example for the purpose of explanation.
I know a bit of VBA programming and initially I started with an input box that asks for the part number and limit dates.
View 2 Replies
View Related
