I have a column with data but some rows are empty, i want the empty rows to be filled with exact data from the upper cell so that no row remains empty. How can this be achieved?
View 4 RepliesI have a table that I use to generate about 20 graphs. The lower and upper limits can be manually altered in the table. If the user leave those 2 cells empty, I want the upper and lower limits be determined by default by Excel. So that the limits don't have to be entered for every graph in order for the graphs to be generated without any debugging error.
With ActiveChart.Axes(xlValue)
.MinimumScale = y1Lower
.MaximumScale = y1Upper
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
where
y1Lower = .Cells(i, j + 9)
y1Upper = .Cells(i, j + 10)
How can I make this code fire for column 2 AND column 3?
View 2 Replies View RelatedI'm trying to have one cell be the constant place I enter data and then when I hit enter, I'd like the data I entered to be moved down to the cell below it. Then for every new number I enter in that first cell, it would auto shift the previous data down and add the new data to the cell below it.
Basically, I'd like to record data but I dont want to have to scroll to the bottom every time to enter new data since this list will be continuously updated. And I'd like the most recent data entered to be on top.
Then I want to take the top 100 cells in that column, and do a running average of them.
I have been trying to get a certain selection of cells to display inputs as capitalized no matter if entered in lower case. I looked through this forum and found some, I did try some of the suggestions, and I am having no luck. I am wondering what I am doing wrong?
View 12 Replies View RelatedI have many shortcut words to make full name using Auto Correct option under Proofing
I am using this code to make force range in Uppercase
[Code] ....
But when i put a auto correction value in small case "ip" {like ( IP = IRFAN PAT )}
Then the cell value is going in uppercase "IP" but auto correction is not working
I want ..if i type in range "ip" result shoul be (" IRFAN PAT ") not "IP"
I have a long list of values in column F of my worksheet. These values appear in random rows for example: cell F3 is 27, cell F9 is 7, cell F13 is 27, cell F20 is 9 ... The data is not evenly spaced any specific number of rows apart, but there are spaces (rows which are blank in column F).
I would like to put all of these values in column G but without any blank cells, and keep the order the same as well. So using the above example, Id like a way to make cell G1 read 27, G2 read 7, G3 read 27, G4 read 9.
Column F could be a very long column im not sure exactly how long as the data is being put in periodically. Some sort of formula that I could just drag down that would read the values from column F and put them in column G with no places would be ideal.
I have a macro that loops through several workbooks and copies data to one common sheet. Works great with one flaw that I have not been able to resolve. I am using the End Property example below, this works great as long as there are more than one cell with data. With one cell having data this takes you to the bottom of the sheet.
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Need code to select from a specific cell to the last cell in that column with data where when the selected cell is the only one with data that would be the only cell selected.
I'm having trouble with my self designed "program" in excel.
Is there a way to insert a row and copy the value or formula of the upper row? withouy using macro.
Say I have text in Column A and as soon as I input that data, I want it then copied into another column (J). What kind of formula and I looking at to do this in Column J to repliace column A automatically?
View 7 Replies View RelatedI need a macro that will examine column A starting at A2 and working its way down copying the data in the first cell (A2) and then delete that row. If the next cells are empty (usually the next 5 cells) it should paste this copied value in each of the empty cells until it comes to the next cell with data in it. At this point it should copy this next value and delete the subsequent row and copy this value in each of the empty cells directly following (again, usually 5) until it finds the next cell that has data in it. At this point, again the whole process begins again repeating it self until it comes to the end where no more data is.
Here is an example of what I need done:
This sheet:
Sheet1
*
A
B
C
D
1
Item Number
Type
Type 2
Feature
[Code] ...
Should look like this:
Sheet1
*
A
B
C
D
1
Item Number
Type
Type 2
Feature
[Code] .....
I've a workbook with a couple of worksheets in it, where one worksheet has a column of data where I want to link the data into a row on another worksheet, however I'm having challenges and really can't work it out.
I can drag the formula as it just copies to a row - I've tried transposing it but it goes to totally different cells which I don't know why.
I am trying to create a formula which will look at the data in columns A - I (50 rows) and copy this data to columns K - S but ignoring any blank cells. Thereby consolidating the data in the upper rows with no spaces.
View 10 Replies View RelatedI've linked excel to a PLC pulse and download columns of data and these columns are updated live. I would like to copy the data in these columns every hour and paste values only into another spreadsheet for analysis. This will have to continue ongoing.
View 1 Replies View RelatedI have an excel WS1 set up as DB; I want to keep this sheet for data revision. WS2,3, 4, & 5 will be data that is filtered and sorted, using WS1 as source so I want to auto copy the WS1 data. Can I just auto copy WS1 (how do I do that?) then filter and sort in each WS?
View 3 Replies View RelatedOnly have upper case letters.
I am trying to get certain cells to only have upper case letters.
each cell in the code will have a single letter typed in.
I would rather have it as an event code (change while it is typed), but I have not grasped the whole thing yet.
This code does not give me any errors, but it is not changing the lower case letters into upper case letters either.
[Code] ......
I have been working on a macro that compares a existing list of data to an updated list of data and then either moves any data not on the new list over to a completed tab (followed by deleting the record on the existing sheet), and then adds any items not on the existing sheet, but which appear on the new list, to the existing list.
I have come across a stumbling block, i have managed to identify on the existing list the rows of data that have been removed from the new list and therefore need to be moved over to the completed tab, but when i select the data it selects the header row aswell (which will always remain the same row). Obviously this then pastes the header row aswell, and also i can't seem to get it to paste in the new sheet to the next available row (i.e this will be used daily and i don't won't to overwrite the infor already in the completed tab). the next issue i have is then when i go back to existing sheet to delete the data i just copied across, as the header was initially select this also gets deleted.
The code below, is the complete code, including filtering, copying some forumals etc. The area i am getting stuck on is highlighted in red:
Sub Update()
Dim bottomrow As Long
Dim My_Range As Range
bottomrow = Cells(Rows.Count, "C").End(xlUp).Row
Set My_Range = Range("A1:Y" & bottomrow)
[Code] .....
Recently ran into this problem where when I select(highlight) multiple cells, it automatically copies the highlighted cells.
[URL]
This auto-copy also occurs periodically when I select(highlight) a single cell.
Notes:
1. I do not have a copy hotkey on my mouse, as this also happen when I use the trackpad.
2. This only happens in excel. When I select multiple items on my desktop, it does not auto copy.
Any way to have the date auto increase 7 days when we drag/copy down the cells. For example, I have the following:
03/31/13
04/07/13
04/14/13
So, when I copy down (let say hundred cells down), the day should show 7 days apart. Is there a formula?
I have an Excel database of department contracts with a Master worksheet. I have created a worksheet tab for each of the departments. Column G has a dropdown list for each department. When I enter a new contract onto the Master I want it to auto copy to the coordinating worksheet based on the selected department.
If possible I would also like it to enter the newest contract would enter into the coordinating Contract Party entered into column D. (ie... If Hospital A is entered in column D on master, the new entry on the worksheet being copied to will be entered under the last Hospital A, if Hospital B is entered on Master it copies under Hospital B). If this isn't possible then I am not too worried about that part.
I have attached a blank version of the file. Current Master 5-15-14.xlsx
So im setting up a big list of stuff, and basically i have multiple sheets.
One of them is a "master sheet" so to speak, and the rest are sub-sets of data.
Is it possible to have the Master sheet updated automatically every time data is entered on one of the sub sheets?
Basically its an alphabetical list of names, and my sheets are "Master sheet" "sheet A, Sheet B" etc .... so If I enter a name on Sheet B, or format the cells on sheet B, is it possible to have that automatically copied to the master sheet?
its becoming a pain to have to enter the same name on two sheets manually.
I am using the Find fuction to look for values in cells that contain text in both lower and upper case. I am comparing the values that I have stored in a lookup table. I am running into trouble when the lookup table doesn't match the text cells....
View 9 Replies View Relatedmacro which autofilter data & copy to another sheet.
below mention are the steps, i dont knw how to write vb code to autofilter month. Please find sample workbook on
below mention link
[url]
1.Auto Filter Date 2(Column C)
2.Select First Month (eg.May 09)
3.Auto Filter Date 1 (Column B)
4.from, the month, which filter in Date 2 (from May 09 to Dec 09..last month of year)
5.Auto Filter Column A
6.Copy each unique value on output sheet
I just want to check that if it is possible to copy specific cells from one row to another sheet by using macro or any other script.
Attached is a sheet which explains what exectly I want
The sheet one is having Source data
Sheet three is the result sheet
If I put some code numbers (in number form) it should search the data from source sheet and update the same in result sheet in different rows
I'm trying to select a discontinous range, lets say B2:B30 & D2:D30 and this works fine using the code below:
Range("B2:B30,D2:D30").Select
However if I use the code below, B2:D30 is selected including column C.
Dim LastRow As Integer
LastRow = Range("a65536").End(xlUp).row
Range("B2:B" & LastRow, "D2:D" & LastRow).Select
I'm currently using a Excel 2007 btw... I'm a newbie when it comes to VBA programming... I have attached a sample file for reference. I need to automatically copy the contents of Sheet1 to Sheet2 and Sheet3 (meaning everything you edited on Sheet1 should be automatically edited on the 2 other sheets.) Here are the conditions:
- the items with zero (0) quantity from Sheet 1 should be hidden on to the 2 other sheets. but when you replaced an item with 0 qty to other values (any value greater than 0), it should appear on Sheets2 and Sheet3. (Not affecting the format of the table)
- items without price should be highlighted.
- items that have values that are linked to other items should be highlighted. (ex. if the value for item1 for qty is linked to the number of qty to item 5)
- for Sheet3, Column D to Column G should be hidden
I have this code to set a copyrange, which is throwing up a 'Wrong number of arguments' error
[Code] .....
Correct syntax for the code?
I want to create VBA code, or a formula that achieves MaxIf across a Discontinuous Range.
I already have the basic Max If array formula which works in another place:
{=MAX(IF(EditAsset_MaintenanceType="Scheduled Maintenance",EditAsset_MaintenanceDateUndertaken))}
Where each named range is a column of 20 continuous cells.
This actually sits within a larger formula {=IF(MAX(IF(EditAsset_MaintenanceType="Scheduled Maintenance",EditAsset_MaintenanceDateUndertaken))=0,EditAsset_DatePurchased+(EditAsset_MaintenanceCycle*30.4167),MAX(IF(EditAsset_M aintenanceType="Scheduled Maintenance",EditAsset_MaintenanceDateUndertaken))+(EditAsset_MaintenanceCycle*30.4167))}
Now I need to achieve a similar thing, but in a master list of all the data which necessitates a different layout so the data can't be in 2 columns, and can't be continuous. So:
Max the dates in cell BE7, BI7, BM7, BQ7, BU7, BY7, CC7, CG7, CK7, CO7, CS7, CW7, DA7, DE7, DI7, DM7, DQ7, DU7, DY7, EC7...
...IF the value of the corresponding cell two columns to the left of each is "Scheduled Maintenance",
e.g. cells BC7, BG7, BK7, BO7, BS7, BW7, CA7, CE7, CI7, CM7, CQ7, CU7, CY7, DC7, DG7, DK7, DO7, DS7, DW7, EA7
I tried creating 2 named ranges (one for the cells in each bullet point above) and using that in the array formula, but that didn't work and as this formula needs to go down 8,000 rows it would require 16,000 named ranges which isn't really practical.
Ideally I think I would perform this calculation in VBA and just output the answer to the relevant cell. [The VBA code will be triggered via a Worksheet_Change event when one of the date cells, or the 'maintenance cycle' cell elsewhere is changed].
I think this could be done by taking the values from the cells above into two arrays, and then 'max if' those arrays within VB somehow - but I don't know how to create the arrays or then perform 'max if' within VBA.
Alternatively a formula would suffice - I could insert this formula into the output cell via the VBA Worksheet_Change event when an input cell (date or maintenance cycle) is changed, and then have the same VBA code copy this cell and paste the value - can't have up to 8,000 array formulas live in my sheet for performance reasons! But I don't think this will be easily possible via a formula and VBA would be cleaner.
I just joined ozgrid and this is my very first post. I have read the rules but spare me if I make some errors.
My question is related to plotting time series charts using VBA. I have data in the following format.
04/21/08 9:04:47 4.5055
04/21/08 9:04:48 4.5055
04/21/08 9:04:49 4.5055
04/21/08 9:04:49 4.5055
04/21/08 9:04:50 4.5055
04/21/08 10:05:00 3.5055
04/21/08 10:05:32 3.5055
04/21/08 10:05:40 3.5055
04/21/08 10:05:48 3.5055
04/21/08 10:05:51 3.5055
04/21/08 11:07:59 5.5055
04/21/08 11:09:00 5.5055
04/21/08 11:10:01 5.5055
04/21/08 11:11:02 5.5055
04/21/08 11:13:08 5.5055
04/21/08 11:14:11 5.5055
04/21/08 11:15:16 5.5055
The column on the left is NOW() output and the one on the right is data corresponding to the given date and time. You may interpret the above data as snapshots taken at regular time intervals.
Now what I want to do is make a chart with the above data. Where ever there is a break in the snapshots, represented by a blank line, I want the graph to jump to the next data point much as the way stock prices move from the closing price to the opening price on the next day. I am not able to figure how to do that. Since I want the x-axis to represent time, using scatter charts does not work since the intervals are not proportional to the time then.
My sheet goes from monday to friday on the coulombs. On the rows i have various data sections for each day. I would like the data on the last day (any day after monday) that there is data for a sheet to input that data into the monday slot when the "master date" is changed on the cheese sheet. The idea here being that the script will take the last entered data for a given sheet, and put it into the monday coulomb on the same sheet when the date is changed. This is kind of hard to explain so if you need clarification let me know. Attached is the sheet i'm working with.
View 14 Replies View Related