I think the title pretty much says it all... Now I have a file I can't send to anyone to do anything with... I've googled a few things but I've found nothing to work with... It was only a few hours worth of work but this seems ridiculous...
View 3 RepliesIn the attached file, the "importer.xls" is the file that has the ADO code and range of area for the data to be pasted, while the "Data to be imported.xls" is the source data that i want to copy which is in range "B1:D5" from each sheet "nightshift" & "dayshift". If you run the code, if would only copy partial of the data, could anyone plz fix the problem for me
View 5 Replies View RelatedI used vlookup function in cell C3 to find ID of employees in Sheet 1 in source workbook and then, I imported the Sheet 1 in master workbook.
The vlookup didn't work in master workbook. The cell C3 in sheet 1 that I imported is blank.
The original got the ID in cell C3, then I don't see the value in cell C3 in master workbook.
I have a question in regards to excel. I have a set of data arranged as follows:
Date System Desc
3/7/14 IPE Alphamo
3/7/14 IPE Beta
3/7/14 i3 Gammana
7/7/14 ews Pisqrt
7/7/14 IPE Jaque
8/7/14 saa Mistiqe
And I need to put them into a calendar with the following format:
3/7/14 7/7/14 8/7/14
IPE Alphamo Jaque
IPE Beta
I3 Gammana
ews Pisqrt
saa Mistiqe
I have tried index and match, vlookup and etc but it didn't work.
I changed the code below to add an extra column(E) this worked. However it does not keep the formatting from the top row in column E, How can i change the code to column E to keep the top row formatting ??
Also i added a list to cell A2, but this moves down, I would like to have the selected data move down but the list stays in cell A2, Was this possible ?
If possible I want to know if the code below can be changed. At the moment it searches for cells not marked with an X and then prints the remaining cells.
View 8 Replies View RelatedI have a calendar in the sheet attached. If there are leave dates that are marked in red, can I create a formula to count the number of leave dates for the entire year ?
View 5 Replies View RelatedIn Excel 2003, I have a checkbox in a cell (C15). I want a range of cells (F15:O15) to respond to this box. When the box is not checked and someone attempts to alter one of the cells in the range, I want a message box to appear, and then to have the data input in that cell removed.
The cells in the range are a mix of drop down lists using data validation and check boxes, so I need to make sure it doesn't erase those, just the choice made from the list or the mark in the box.
Possible?
inputting a formula that will look at the dates in row 2 on the tab marked “Cap Plan” on the sample sheet and then go to tab “Monthly Mode” and match the date and then take the percentages for each work type on that tab and put them in the percentage columns in the tab “Cap Plan” for the correct date. This document will be a rolling 52 week one so a simple = formula will not work.
View 2 Replies View RelatedBelow is a part of my code. But I am getting error on the bold&Red marked line.
View 2 Replies View Relatedi'm having a bit of a issue with a do while loop. It might just be my complete lack of understanding, but i'll briefly explain what I am doing before I show you the code. I am marking cells with what I consider an invalid type for that cell red. Well unfortunatley in some sheets the number of invalid types is so high the macro crashes. So my bright idea was to inclose the check statment into a do while loop, that will only check until so many cells have been marked red. So I created a public variable, assigned it a value of 1. I then assigned the macro that marks the cell red to also take y and add 1. So in theory (mind you what I take for theroy might be in my head) it should only run until the paramaters of the do while are met which in this case are do while y < 20.
y = 1
Do While y < 20
Set MyRange = Intersect(Columns(7), ActiveSheet.UsedRange)
For Each r In MyRange
CheckDate
Next r
Loop
That is the snippet from the program calling the function, and here is the function
Public Function CheckDate()
If IsDate(r.Cells) = False And IsEmpty(r.Cells) = False Then
r.Select
MarkDate
y = y + 1
End If
If r.Cells < 1 / 1 / 1910 And IsEmpty(r.Cells) = False Then
r.Select
MarkDate
y = y + 1
End If
End Function
what i'm doing wrong here? Oh and y is assinged as a public varaible, integer data type.
I have this excel workbook that when i tick the first sheet ("251" in temp), it copys the row onto the second sheet ("order" in temp). This all work wonderful, but now if i would like to add additional pages to this excel workbook and have it do the same thing (by same thing i mean adding additional sheets but keep only one "order" worksheet and have all the information go onto the order worksheet.
So for example:
I would add an additional page name 252 into the workbook, it would look and function just like the 251. So after all the ticking on 251, all the information would go onto the "order", i can then move onto 252 and do the same type of ticking of information and those information would also continue onto "order". And i can keep adding multiple worksheet onto the workbook and do the same exact thing.
I started playing around with the macros feature, and hit "record" but didn't save anything. Now, whenever I try to close my sheet, it gives the "save changes" prompt, even when nothing's changed. There are no saved macros to delete...no idea what I did or how to get rid of it. I tried copying the cells to a new worksheet, and the new one gives the save prompt as well.
View 9 Replies View RelatedI have a spreadsheet that keeps track of my travel. Column A has the date I arrived somewhere, and Column B has the date I departed, and Column C has the name of the city I went to.
I am wondering if there is a way to generate a calendar using my list that will mark those dates. For example, a calendar for the month of June 2008 that would show I was traveling from June 3 to June 14, either by marking those dates with a different color or labeling them with the city names, or even just putting an x in the box.
I run a football sweepstake involving 120 people. Each week everyone picks a premiership team who they think is going to win. If that team loses or draws they are out. This carries on until there is one person left and they win the money. I need to make a table with all the premiership teams and names down the x and y axis. When the team is marked as "lose" everyone who has chosen that team gets deleted.
View 5 Replies View RelatedI have a very large spreadsheet with the following columns: WO# (number field); Start date (date format MM/DD/YYYY); Frequency (text); and craft (number). I am trying to have code that checks the frequency and if is "Monthly" or "Weekly" it just goes on to the next row; if it is "Annual", it adds 163 to the start date (start date needs to changed to a numeric field); if it is "Semi-annual", it adds 82 to the start date; if it is "Quarterly", it adds 45 to the start date; and so on, there about 20m different frequencies. After it adds the above value to the start date, I need to check if that number is less than today's date (the day I run the code). If it is, it needs to flagged as "LATE" and the whole row of info copied to another worksheet with LATE as the title and all the column headings and info copied to the worksheet. I hope this makes sense to someone because I am a beginner in Excel and even less informed when it comes to VBA. Any help would be greatly appreciated.
The way the process must work is that I need to check the frequency and if it is "Weekly" or "Monthly" , it is ignored and goes on to the next row. All other frequencies are cut in half, i.e., "Annual" is 183 days, "Semi-annual is 92 days", "Quarterly" is 45 days, "2-Year" is 365 days, and so on. This number needs to added to the scheduled start date (now formatted as a number, not a date, and checked to see if it is smaller than today's date (also a number). If it is, it is reported on the second worksheet (titled Late).
I am trying to build a user form where people are filling in their holiday requests.
I need once the date is choosen to be marked somehow on a calendar in the same workbook.
Besides the date, I need in this form, people to upload a file "Holiday Request" which, after the submission of the report to be sent to a specific email, and to be stored as Object in another sheet against the name of the person who is submitting it.
I am using a For Next statement that doesn't return the results for all the rows. The statement is as follows:
For Row = 1 To 100
If ActiveCell.Value = "CHANGE" Then
ActiveCell. Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RIGHT(""0000""&RC[-1],20)"
ActiveCell.Offset(1, 0).Range("A1").Select
ElseIf ActiveCell.Value <> "CHANGE" Then
ActiveCell.Offset(1, 0).Range("A1").Select
Else: Range("A1").Select
Exit For
End If
Next
Range("A1").Select
I hope I did that according to the rules. It only returns the result in the first cell that does have a value of "CHANGE". It seem to be going through the entire range of cells, but I'm not getting any results.
Formula to automatically do these operations in the table below?
"column I1" contain data which I need to find in between columm B1:H1; and marked them red.
I need to do same operation for row2 to row4. I need a formula that can automatically find and mark the data in red.
I need to put a formula in B6 to count the data marked in red for column B1:B4 and do the same operation for C6,D6...H6.
A
B
C
D
E
F
G
H
RESULTS
p1
11
01
12
22
21
M1
10
11
[Code] ..........
I need to cut and move data in a worksheet where the bottom always changes. The header row should stay in worksheet 1 and row 2 down to the bottom needs to be cut and moved to worksheet 2. One time it could be 100 rows and the next time a thousand or more.
View 5 Replies View RelatedI need to do a final ranking for the employees those who have been already ranked on three different segments. And there will be also employees who are not qualified for the ranking in any one of the three different segments. So in the final ranking need to find the final qualified employees who are ranked in all the segments and we need to rank them, and the employees who are not qualified also has to be mentioned.
View 9 Replies View RelatedAutomatically copy rows to new sheet in excel when column marked with an X. If a column is marked with an X, I need to copy this row to a new sheet. So if a column C is marked with X, I need to copy this row in Sheet2 , if a column D is marked with and X, I need to copy this row below next empty row in Sheet2, if a column E is marked with X, I need to copy this row in next empty row in Sheet2.
View 2 Replies View RelatedI have spread sheet and values in some columns have highlighted in color.What I have to do is I need to quickly separate the highlighted data and put in the other spreadsheet.
View 5 Replies View RelatedIf a column is marked with an X I need to copy this row to a new sheet. So if a column C is marked with and X I need to copy this row to Sheet2 , if a column D is marked with and X I need to copy this row to Sheet3 and if a column E is marked with and X I need to copy this row to Sheet4., Please can someone help with the VBA code to make this work?
View 10 Replies View RelatedDATE
PRODUCT
BALANCE
1/2
KEY CHAIN
4
1/2
KEY CHAIN
8
1/2
KEY CHAIN
27
[code]....
How to get final figure each product from sample data above:
the result:
Key chain = 27
Gift = 4
Hamper = 1
Finalrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Finalrow2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
I need my "Do until" loop to run until the activecell in sheet1 =finalrow
I tried "Do until activecell.row=Finalrow", however that didn't work because it ended my macro too soon.
I tried "Do until sheets("Sheet1").Activecell.row=Finalrow
But it won't accept that.
If I have a columns of numbers that vary in length, and can have empty cells between the final value and the formula to pull the number (of varing lengths), what formula can I use to repeat the final value?
For example - here are two columns:
4,800,000
0
46,594
space
space
<formula to pull 46,594>
or
56,000
48,150
36,528..............
I've modified some code to perform a loop which creates a new worksheet for each item in a pivot table Page By field (EmpName), copying and pasting values for the displayed pivot table with each name selected. Everything is working fine until it gets done with the last item in the list, at which point I get a Run Time Error 1004, "Unable to Get Properties of the Worksheet Class".
I want the MsgBox to pop up at the end, stating how many sheets have been added.
Sub Loop_PivotItems()
'Turn off screen updating
Application.ScreenUpdating = False
'Store the sheet with the Pivot Table
Piv_Sht = ActiveSheet.Name
'Loop through every PivotItem in the PageField (Filter) of the Pivot Table
[Code] ...........
I am attempting to sum the final two values in a list. Each week, I add a new value to the bottom of the list. I would like the formula to calculate only the final two values, and update each week. For example, in Week 1, I would have the values 4,10,12,6 in Cells A1, B1, C1 and D1. The formula would calculate the sum of C1 and D1 = 18.
In Week 2, I would input the value of 15 into Cell E1. I would want the formula to now calculate the sum of D1 and E1 = 18.
If coded correctly, there are various Excel functions & tricks for creating a formula to send someone on a wild goose chase through many worksheets and cells before finding the final cell that contains the final value. I was able to use =Cell("address",...) function to cut through the many paths and retrieve the address of the final cell. Is there a VBA trick for doing this to the active cell? I would like to be able to run a small VBA routine that would trace back the fomula of the active cell and return the final cell's worksheet and cell address.
View 9 Replies View Related