I have a list of consecutive numbers (1 to 150) in column A. Sometimes some of the numbers are missing e.g (1 5 6 9 10) and therefore there are less rows. I would like to insert rows to make up the missing numbers so that I always have 1 to 150.
Need the right direction, I have looked through the 150 threads regarding "insert row"
An external data web query points to a web site that offers a foreign exchange rate calculator. In my browser, I selected the specific currency pair I need and used the resulting url in the web query. The "New Web Query" pane resolves the url correctly and allows me to select the table data I want (the little yellow arrow turns to a green check mark). However, when I hit "Import" and select the target cell, the only data returned is the parameters from the url and an error msg (This web query returned no data...). How do I get the query to pass the parameters to the website correctly?
View 3 Replies View RelatedIs there a way to insert a blank row between every row with data? This is for very large spreadsheets when adding blank rows individually is not an option.
Example:
Data Data Data Data
Data Data Data Data
Data Data Data Data
Data Data Data Data
Data Data Data Data
Data Data Data Data
Needs to look like this:
Data Data Data Data
Data Data Data Data
Data Data Data Data
Data Data Data Data
Data Data Data Data
Data Data Data Data
There 2 columns, 1 in which you input data and the other which has a formula which uses the data to do a calculation. There are about 30 rows already filled in.
When I go to insert a new row of data, the formula just appear after I type in the input column. If I click on the cell where the formula appeared before I typed in the data, there was no formula there. It is almost like an autofill, excel knows that I am going to copy down the formula. (It also copies down the formatting)
I have an excel spreadsheet set up as shown in the attachment. When I insert data by using the macro (Insert farm), for some reason it puts a NA in a cell (C17).
I then select the cell click into the formula and click enter and it works. how can this be fixed. Look at the excel spreadsheet to be able to understand.
i have a list of numbers in column A and in column B every now and then a * will appear next to a number. The first number will always have a * next to it. Basically i'm looking for a way to say...that either by copying to another tab or inserting rows on this tab, do the following for every number.
Insert a row above that says
[pf6]
put a " next to the number so that it reads as below
"408425
and then the following in new rows as below.
[enter]
[tab field]
[tab field]
[tab field]
[tab field]
"1.5
[field+]
[pf5]
I need code that can work down a column and insert a blank row when it notices the data value has changed. i attached a file to show what i am looing for.
View 4 Replies View RelatedI need to insert data as following pattern :
1 1
2 1
3 1
3 2
4 1
4 2
4 3
I am having an excel file in which there is information about 100 products. Every product has around 10-15 features. This way I have 1,000 rows. Every product has its own unique ID i.e. every 10 rows have the same ID thereby making 100 ID's. Now I want to insert a blank row every time the product ID changes so that I can discriminate better between the products. Is there some formula?
A
A
A
A
A
B
B
B
B
C
C
C
C
I want to insert blank rows before B and C. If the number of features were constant, it would have been easy for me.
Im trying to find the best way to insert data into table.
source of data is workbook range and/or array the obvious one is
Code:
With Sheets(1).ListObjects(1).ListRows.Add.Range
Cells(1, 1).Value = xxx
Cells(1, 2).Value = yyy
Cells(1, 3).Value = zzzz
end with
but if its lots of data it could get very slow.
Q1. is there a way how to insert multiple records at once, something like
Code:
'does not work
With Sheets(1).ListObjects(1).ListRows.Add
.Range(Cells(1, 1), Cells(1, 3)).Value = arrXXX
End With
I found it faster to store data into array and then paste them
Code:
rw = ListObjects(1).HeaderRowRange.Row + ListObjects(1).ListRows.Count + 1
range(cells(rw,1),cells(rw,3).value = arrXXX
but I had issues with it especially if the destination table has some filters applied. I'd also prefer not to clear the filters during the sub
Q2. are there any other options that would be fast and reliable?
What I'm really looking for is to box in data as the data changes using the border feature.
Column A would contain the data to look at however there may be additional data in columns B, C, D and so on that would need to be "boxed in" with the group of like data.
I can't get the HTML to work so we'll have to go sans spreadsheet on this one.
I have rows of data with each row going from column A to column P
They are sorted by column A (account No.)
I need to insert 2 blank rows between each change in account No.
I want to insert data validation to a cell by VBA. When i record the macro to put a list (value1;value2;value3) to a Cell the following code is generated and the validation works OK.(dropdown of the 3 values)
Sub Validation_by_VBA()
Range("A2").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="value1;value2;value3"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
If i run this code from VBA I have only one value in the dropdown (value1;value2;value3) How can i change this code so it will work from vba as well?
to create a macro that insert [url] for column a infront of the data currently there.
So in coulumn A, it might have default.asp
I wish to insert [url] infront of it for column A from range row 1 to 10.
The list of data is shown in sheet1
In sheet2 I have shown the data that is copied
I have manually shown comments on 2 cells in sheet2 that shows detailed description on how was the total taken.
I need the comment on all the cells from D8 to G11 (not taking the total column) that shows on how was the total taken.
When any entry in sheet1 is inserted or deleted the comment in sheet2 should change accordingly.
I also do not want to freeze the cells D8:G11 as when new entries are made or deleted the columns would increase or decrease.
All blank cells I do not need any comment.
Attached is a sample of what I need completed.
Monthly, I have to do a chart just like this except slightly more complicated.
In the Sample download, there are three charts, "Sample Chart", "Sample Input", and "Desired Result".
"Sample Chart" has a list of accounts from different companies, The first column being their number, the second being their name, and the third being the money they spent.
The "Money Spent" Column is always blank when I start for ALL companies.
I have another chart, "Sample Input", which contains the prices that I'm supposed to put in "Sample Chart, Money Spent" column.
The thing is, "Sample Input" only has the companies with prices listed.. Not all companies have prices, so this means the "Sample Input" is always a shorter list than "Sample Chart".
What I need is the prices from "Sample Input" to be put in the correct position in "Sample Chart". The "Desired Result" chart is what I want it to look like.. exactly like that!
When I do this monthly, I have to scroll through several thousand accounts doing this.
Suggestions:
- Possibly have a macro or formula take the Account # in "Sample Input".. Sample it in "Sample Chart", then copy the price and paste it in the right location.
- Possibly make "Sample Input" have blank rows inserted in the places where it should have the account with no prices.
I have a column with intervals(every 30 min) and with some values. My problem is that if there is that if the value is 0, the interval is deleted. Is there a way(maybe macro? ) for the excel to see that an interval is missing and add it with value 0?
Attached File : intervals.xls
what I need is to insert the data in a cell, and instead of copying this same data to other cells in the same sheet or in other sheets, I need a way so that once I insert the data in one cell, it will be copied to the other cells at once.
For example, if I insert data in the cell [Sheet1,A1], it will be copied to [Sheet2,A4] and [Sheet3,E8] and [Sheet3,I7]
I got a table, some columns are variable data you have to put in by your own and I got some columns with only formulas. After entered the last variable data I want excel to add a new row with the same formulas and format as the other rows in the table.
View 2 Replies View RelatedI have posted in a existing thread at mrexcel as i believe my problem and possible solution is very similar to that already detailed there. However i have not recieved a response there yet and was looking to draw upon more minds here ...
View 9 Replies View Relatedi've got supplier list on the excel file and need to insert two blank rows under each supplier name. the two blank rows need to be inserted no matter whether the supplier may be occupying one row or 100. for example
from this
a ltd
a ltd
b ltd
b ltd
b ltd
x ltd
m ltd.........
Hello, I have a task that would take me hours to complete and I'm sure there's got to be a way to do it in Excel 2007. I need to get part numbers from one master cross reference spreadsheet to individual spreadsheets. The master cross reference spreadsheet has our company 7-digit number AND the manufacturer part number, while the individual spreadsheets only have the manufacturer part number. I need to have the individual spreadsheets match manufacturer part numbers and then insert our company's 7-digit number line-by-line.
Master spreadsheet (snippet):
7-digit PNMFG PN
2206422A04-04L
2209624A04-04S
2203436A04-054
2208761A04-06A
2208897A04-06C
2208942A04-06D
2209292A04-06E
2209108A04-06F
2209627A04-06S
2208764A04-08A
2208900A04-08C
Individual spreadsheets (snippet):
7-digit PNMFG PN
?A04-604
?A04-605
?A04-606
?A06-604
?A06-605
?A06-606
?A06-608
?A06-610
?A08-606
?A08-608
?A08-610
I'm trying to insert a 'space' after the second character/number in a cell.
I.e 123456 should become 12 3456
I have tried =LEFT(A1,2)&" " but this just deletes all data after the space. I've also tried =MID(A1,2)&" " but this makes excel very angry.....
Need a VBA macro that is similar to the subtotals function but without the subtotals? Basically I want the macro to look at column A and say, for each change in column A insert a blank row… I don’t need to worry about the sorting, I just would like to know how I can tell a spreadsheet to look at a column find changes in that column and just insert a blank row. On the second page of the attached spreadsheet is an example of the desired output… I will probably use this for many different spreadsheets so the simpler and more versatile the better, ie I can change the column I want the macro to look at.
View 3 Replies View RelatedThe formula that works is =SUM(IF('Pipeline Input'!$X$13:$X$39=1,IF('Pipeline Input'!$H$13:$H$39="Lead",'Pipeline Input'!$K$13:$K$39,0),0))
I am trying to modify this formula so that the ranges are dynamic to allow me to add new data with an insert at row 13. What would the syntax of the formula look like if I use the INDEX function to allow the ranges to grow with new data? I have tried naming the defined ranges and entering the formula as =SUM(IF((CloseMo)="1",IF((SaleP)="Lead",(LoanAmt),0),0)) but I get a #VALUE! error
So I am trying to write a bit of code that will insert a number in sequence in column A if there is data in column B. I need the column to be numbered 1-### if column B contains any data. I not been able to find this code anywhere, just delete if or count if statements that do not give me what I need. I think I have a few pieces of the code (e.g. Active Cell Offset) but can not seem to fit together in the correct order.
View 3 Replies View RelatedI have spreadsheets I generate weekly that have 100s of rows. Everytime there is a data change in a certain column, I need the same row entered. I found a macro from this site that got me sort of half way.
I am trying to paste the macro that I found, but I am such an idiot I can't figure out how without it losing its formatting. Regardless, it only enters a blank row at every data change. Is there a way to enter a row with the same labels already entered in it?
I have a table I down load from SAP with the following headers;
Route, Description, DChl, YF21 (SD),YF31 (SD),YF21 (MM),YF31 (MM),YF10 (MM),YF11 (MM),YF12 (MM).
All I need to do is look up and match the Description and then insert the value (they are percentages) found under the columes YF21 onwards.
I have tried VLOOKUP until I am blue in the face and cannot get it to work.
I've got a problem that's causing me a headache. i have a document with 1 column and 4000 rows (its an export from active directory).
I need to do a search cell by cell (so A1 - A4000) for any cells that contain the text "changeType". When a cell is found, i need excel to insert a new row above the current cell and insert the text "modify" into the new row (in the first column).
eg.
x
x
x
changeType
x
x
Becomes:
x
x
x
Modify
ChangeType
x
x
The 'Add new material' button at the SMX sheet paste a range of formatted cells and formulas. The user is suppose to type in the description into the yellow filled cells. The problem is once the user enter sthe description in the yellow filled cells, i need the data to be copied into the FastCheck Sheet. Before that, a new row must be generated to copy for the data to be pasted.
View 2 Replies View Related