Using online examples I am usually capable to reconstruct whatever I need. However, it's the combination of things I need to do now, which has left me banging my head on the keyboard for days now
Here's the data I am working with:
Sheet 1:
Sheet 2:
And here's what I need to get done:
From sheet 1, select first ID entry from Column A.Find matching ID in Column A of sheet 2On match, find in Column B the earliest date belonging to the concerned IDCopy that earliest date next to the corressponding ID in Column B back on sheet 1Return to step 1 and repeat for next ID entry. Do until end of list (sheet1)
So the result should look as following on sheet 1:
The major issue I am having is the combination of step 3 and 5. Because it probably means something like moving through an array that's within an array through which one is moving. And I am just missing that bit of experience that allows me to make that thinking step. I just keep falling off if you know what I mean...
I want to get a return value of date in column C whereas column A represent a date and column B represent a number. Simplest formula in excel is Column C (Feb 26, 2014)= Column A (Feb 21, 2014) + Column B (5). I want it to have in macro code.
column a has dates & column d has values - what is formula to find highest number in values and return it's date (using =large(Dxx:Dxxx, 1) to find highest number
I have column A with a bunch of dates (in order) Column B has the number of support calls. I can find the Highest number of calls no problem using MAX(B:B) How do I then return the date that that number occured? I know its a lookup / match / offset thing but like I said my brain has frozen .
I'm trying to figure out a formula to use that will exclude negative numbers. I have two dates. I'm trying to find the difference between the two dates assuming there is a date in the first column. At times there will be a situation where the date in A1 is before the date in A2 and it returns a negative number. I'd like to return a blank if there is a blank cell in column A and also return a blank if the return number is a negative.
Example:
A1 = 1/15/2013 B1= 1/10/2013 C1(formula) =IF(A1="","",(A1-B1) C1 will then reflect 5 A2 = (cell is blank) B2= 1/18/2013 C2 (formula) =IF(A2="","",(A2-B2) C2 will then reflect a blank cell A3 = 1/5/2013 B3 = 1/15/2013 C3 (formula) =IF(A3="","",(A3-B3) C3 will then reflect -10
I'm trying to figure out a formula that would also allow C3 to reflect a blank cell since the formula returns a negative.
I am using the MONTH function in a formula. The Month value of an empty cell is returned as "1". Why is this? Is there any way to return a null value or zero?
I'm writing a macro that will import data from one workbook to another, based on yesterdays date. I have the import functionality working, and I've been playing all afternoon, but I can't find a way to find yesterdays date in a range and use that row number as the row offset value in this line:
Rather than having a set row offset value (in this case, 14) I would like to use the find function to lookup yesterdays date in range "B50:B80" in the worksheet I've designated as "sh1" and return the row number of the cell that has yesterdays date and use this value as the row offset value, replacing the hard coded 14 that is in there currently. The date values in the range are formatted as per Date Format.jpg.
I have working code that returns a row number within a for loop based on parameters I set.
Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.
Code: for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row) if x = y then *storedRow = rowNum end if next rowNum *
Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.
All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.
1. look in column X to see IF there is an X 2. there's over a thousand rows to get through, and many of them have X's 3. IF the row has an X, I want to take some of the information from that row and transfer it into a new spreadsheet (the tricky part is I don't want to copy the entire row, but only certain columns in that row) 4. LOOP... so repeat for the next X down in the column X.... another tricky part is I don't know how to paste the information I want into 1 row lower in the new spreadsheet)
I am trying to take a column of data located on one sheet and move into two different locations on another sheet. My range is from A1:A10. The below code works, but as it loops, it replaces G1 and H1 with the other values from the original range. Once the first pos and neg values are placed in G1 and H1 respectively, I want the next cells to be evaluated and placed in G2 and H2 etc. What am I missing here? How can I get it to stop pasting over itself?
Sub test() Dim bcell As Range For Each bcell In Range("a1:a10") If bcell > 0 Then bcell.Copy Destination:=Worksheets("Sheet2").Range("G1") Else: If bcell < 0 Then bcell.Copy Destination:=Worksheets("sheet2").Range("H1") End If Next bcell End Sub
I have 5 coulmns of data and in col F I have the following formula in row 18 =VLOOKUP(E18,$C$18:$D$800,2,FALSE) the results come back " TAX CONSIDERATIONS" however the same results comes back in row 21 and row 24......how can I modify this lookup to change when column B changes
I have a worksheet with 2 columns. The first column contains names and the second column contains numbers associated with the name.
How can I specify a name, go through the sheet to find all the occurances of that name and then paste all the numbers associated with that name in another worksheet in a set position.
I need a macro to loop through a dir and the sub directories to find xlsm files, when it finds one open, go to the sheet named data, look at c1 and if the value is between 12.1 and 13.4 then i need it to write the file path, filename to a1 in my excell sheet and then write the value from c1 in the original file to A2 of line in my excel sheet.
I have hobbled pieces of code together without any good results.
I found a use for the “Looping Through Workbooks” code (recommended by Dave in my last thread - thanks Dave).
I have a number of small files in "C:Files" that I’d like to loop through and find a match with data from a Master (in another folder) workbook as follows.
1. Match columns ”C” of each of the files with column ”H” of the Master. 2. For each matching cells, insert data from column “I” of the Master (“ Date” field) to into col “D” for each row of the individual files (about 300 rows) 3. Save & Close the workbook and loop to the next one in that folder
All that's needed here is code to search, match & paste, to insert into Dave's "Looping Through Files" code.
Items in Column A1 are calculated by (B2/4+5)*1.4 Items located under the columns 2000, 3000, 4000, etc... 10,000 are calculated by taking the top number, eg 2000/(A1 cell value)+the column B number. 2000/7+0 = 286 (rounded numbers)
I need to find an way to look up for x number (2000,3000,4000, etc...) find the smallest number in that column and then return the value in column A1.
Cell A1 Number >2000300040005000600070008000900010000 70 2864295717148571000114312861429 84 24236148059971883795610751194
I am trying to write a formula, I have 6 sets of criteria with a lower and higher range, if the number falls within the criteria I would like it to return the Alpha number,
I need to compare three cells of random dates shown in Column E, F,& G with Row's H5:AK5, H7:AK7, H9:AK7 (the Dates to these rows is Static on row H3:AK3.) EX: ROW 5 has Start Date, End Date (1) and End Date (2). Compare Cell H3 between Start Date & End Date (1). If H3 falls between or equal to Start and End Date(1) then highlight cell H5. Proceed till AK3 (higlighting only the cells H5:AK5). Then compare cell H3 between or equal to End Date (1) and End Date (2) (higlighting only the cells H5:AK5). Then do the same for ROW 7 and ROW 9.
To make things a bit more difficult I need to have " WK#" in Row 14 (these WK# is on another tab called "Task" of the workbook) needs to be displayed in Row's H4:AK4, H6:AK6, & H8:AK8. EX: Compare Date in D15 between or equal to Start Date & End Date (1) then display Wk# in D14 in H4. Continue till all dates in D15:Z15 are compared to Start Date & End Date (1) and WK#'s in Row D14:Z14 are inputted if applicable in Row H4:AK4, H6:AK6, H8:AK8. I hope this is not confusing. I can't seem to use the upload option so here is alink to download a jpg of the sheet
How to create a formula in cell M2 that looks at the second row of numbers (0 and 500s) and tells me the rightmost value that is greater than zero. In this example it would return 12.
Second Question: Is there a way to return any counted value, for example the second-to-last number that is over 0?
I need this for a tracking sheet of scores. For example, 1 gets 100 points, 2 gets 90 points, 3 gets 80 points, etc. I need to set it up for 10 places. I have no idea and have fiddled with it for two hours now. I need to be able to put a 1 in the cell and 100 appears after I hit enter, etc.
I have a have a userform that uses a macro to save data to a worksheet and i want to display the row number using a loop ie if column B3 is not empty/contains a values add a number 1 in column A3 and so on everytime a record is added.
I have a table of data (lets say A2:H30) i want to populate so i want the macro to loop based on how many columns i have? I am using this to post the data based on # of rows..
Sheets("Main Cal").Select Dim bottomA As Integer bottomA = Range("D" & Rows.Count).End(xlUp).Row Dim c As Range Dim ws As Worksheet For Each c In Range("D3:D" & bottomA) Set ws = Nothing On Error Resume Next Set ws = Worksheets(c.Value) On Error GoTo 0 If ws Is Nothing Then