How To Insert Data Into Table
Apr 26, 2012
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?
View 3 Replies
ADVERTISEMENT
Jul 9, 2014
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 Related
Mar 23, 2014
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 Related
Jan 16, 2012
How to insert a column to table depending upon name of the other column using VBA.
Like i need to insert the column in a table besides the column name x.
So how to search for column name x and insert column besides it.
View 2 Replies
View Related
May 21, 2008
RowCount = Worksheets("T_Stunden").Range("A1").CurrentRegion.Rows.Count
With Worksheets("T_Stunden").Range("A1")
[code]
I need to change it so it goes into the table as at the moment it goes one line below when I finish with the entry via Userform.
View 9 Replies
View Related
Sep 23, 2009
I'm getting extremely frustrated with trying to find some code that allows me to paste an ADO recordset into a database table!
What I am doing is on a weekly basis is creating a plan of which containers my company needs to devan. We have a system that stores shipping information about these containers and I get shipping information delivered to me via e-mail from one of our major shipping companies. Their data is more accurate so what I do is paste the data I get from them into my container planning sheet and import it into out Teradata Warehouse to query against using ADO and I import this one record at a time. This is extremely slow!
In order to get around this I want to create a recordset from this data and then paste the recordset into a table in the database. So far this is my ....
View 9 Replies
View Related
Dec 11, 2013
I am working on an excel table in my worksheet contains formulas and locked cells. lets say table range is from column "a" to "f" and there is formula on cell in columns "c" and "f". I just locked cells in column "c" and "f" to prevent people changing the formula accidentally. but when the sheet is protected i am not able to insert or delete rows as the rows has locked cells in columns "c" and "f".
I tried the below macro to unlock the sheet, insert row and lock the sheet again:
[Code] .....
So by running the macro it asks the password and do the job but the issue is it needs password and i do not want people to know my password. I don't want them able to unlock the workbook and make any undesired changes. I want them to be allowed insert or delete rows and input data in unlocked cell. I want the sheet to be locked all the time and only when people want to insert or delete a row it be unlocked and be locked again after inserting or deleting whit no need to insert password in this case only.
View 1 Replies
View Related
Apr 9, 2009
I currently have a 200 row table spanning 10 columns. There is one numeric column and the rest are free text. The table charts the different contents on one of the shared drives here at work. The drive is frequently in use with things being added and removed from it.
I have an excel table already which can sort the contents in order of size (numeric row) or name of the folder (the first text column). I have two buttons at the top.
The first inserts a row, which I did by recording a macro and then copying the code into the button.
The second I would like to do the following: When a user selects a cell in a row, the button, then clicked, deletes the entire row. Not just the data in it, but excises the row itself and stitches the surrounding materials together. The code to delete a row I could obtain easily, but I can't work out to say "find the selected cell, and take out the row it belongs to".
View 2 Replies
View Related
Jan 27, 2010
I'm trying to create a macro that will allow me to insert two rows to extend two separate tables. The problem is that one table needs to be directly below the other. So if the number of additional rows exceeds the margin between the two tables, the macro will just be inserting two rows into the top table.
I guess I'm looking for a way to get the macro to insert a row, not at a specific row number, but at the first row of the bottom table, which will change as rows are added to the top table.
View 3 Replies
View Related
Dec 18, 2008
I want to insert a frequency column in my pivot table. See frequency.jpg for an example.
The column has to count the number of times "artikel" is represented in the pivot. Is it possible to do this in a pivot table, and if so, how?
View 9 Replies
View Related
Jun 7, 2013
I need a Module that allowed me when I run it to add Rows down according upper cells Value for Example;
Total
Names
2
Sami
5
Rami
Then after apply the Module I need like this;
Total
Names
2
Sami
[Code] ............
View 4 Replies
View Related
Mar 27, 2008
I currently have a spreadsheet with 4 columns that represent Surface corridinates and Bottom Hole Corridnates.
Surface X, Surface Y, BH X, BH Y
We need to get the Bottom Hole XY to be directly under the Surface XY.
Attached is some sample Data and What I mocked up for a temp solution is in VBA Module2
RunAll it will complete 399 rows of data.
In Module1 I have the starting Code that I modeled off of a macro that I started to get the base code.
I just need this to loop till it has no more data to copy. and not use module2 at all.
I commented everything for my personal benifit and to help me understand the steps. also know its a monster right now but it does most of what I need it to do.
Sub CopyPasteBHXYs()
'Wrote Based on recorded macro
'Selects second data row and inserts a row above
Rows("4:4").Select
Selection.Insert Shift:=xlDown
'How to make it loop till the last data is reached?
End Sub
Side Note I'm finally taking a Excel VBA course Apr 8th and 9th.
View 5 Replies
View Related
Jul 5, 2012
I have a table refreshed from a MySQL database that I use to create a report. I can use this data quite well using formulas in the cells.But in order for me to add more information to the report I use without making it cluttered I need to add some data as comments.
I have created a loop that picks up some cell values that I want to use as criteria but I cant work out how to use these variables to search through the table and get the info I require to put in the comment. In a cell I would use offset/match or index/match to get the required cell value but how do I do this in VBA?
View 2 Replies
View Related
Jan 3, 2013
I decided to format my data as a table so that excel will auto-fill my formulas when inserting new rows which works quite well. I have one snag though, when trying to insert a new row at the very top (i.e. inbetween the header and first row) and choosing Format As Below, it also applies Data Validation and Conditional Formatting to the header. The inserted row, however, is actually formatted fine and works well.
How do I stop this from happening, and why would Excel do this anyway (as it is effectively applying formatting to TWO rows when only one row is being inserted)?
View 3 Replies
View Related
Aug 30, 2013
I'm using excel 2010 and I need a command button to add rows to the end of the dynamic table.
View 3 Replies
View Related
Jun 1, 2009
I have a table of data which represents data in different categories by week.
My ultimate goal is to have another table representing the data for each month - for instance - for each of the categories the data for :
06/04/2009
13/04/2009
20/04/2009
27/04/2009
is summed to make the data for the month of April.
The way I'm doing this at the moment is very long-winded
I'm using a whole new table - the size of the original weekly one - for each month. A calculation decides whether to effectively leave a cell blank or insert the appropriate data based on a date being within a particular range.
So in each "month table" there is the same list of week values:
06/04/2009
13/04/2009
20/04/2009
27/04/2009
04/05/2009
11/05/2009
etc.
but for each "month table" only the cells adjacent to the dates within the relevant month will return actual numerical values within them
This is an example of the forumula I am using in these tables:
=IF(AND(($AD7>=MIN($AE$5,$AE$6)),$AD7<=MAX($AE$5,$AE$6)),$C7,"NA()")
Then a master table sums the totals for each month.
I want to be able to keep this table but get rid of the ones for every month as the sheet is getting unwieldy!
I have tried several times to attach the sheet for clarity but each time upon trying to "Submit New Thread " I am getting page not found errors - the sheet is only 133KB and I have tried zipping and sending also - I can't make it any smaller.
View 14 Replies
View Related
Jun 12, 2013
I have created a table in Excel 2010 (pls see attached table named post.xlsx).
Then copied the above table into PowerPoint 2010, using "paste link" (I tried to attach the PowerPoint file but the system says "invalid file type" and I cannot attach it).
Question:
I have received income data for another month - the new month is 13 and the corresponding new income is 100.
I typed 13 and 100 into the Excel table post.xlsx and thus extended the table by another column.
Then I went back to PowerPoint slide, then right clicked on the table there, then clicked "update link".
Specific Question:
The newly-typed column in Excel table is not get updated in PowerPoint table.
View 2 Replies
View Related
Feb 1, 2010
I have enable Refresh on Open for my excel pivot table, but user need to click "Enable Automatic Refresh" , only solution i came across is to change the registry setting. Which i dont have access to edit registry(admin disable the access).
Alternate solution i try to use Access macro to automate the process and use Outputto save it as a excel file A. Then use excel file B to update pivot table from excel file A.(as excel A data is always latest)
The problem is i will get "....A file name already exist...do you want to overwrite.." prompt.
Which defeat the automate process.
Any other solution to enable the automatic refresh on open the excel workbook?
Or Access can overwrite the exist file or save it as another file name with timestamp ?
View 14 Replies
View Related
Mar 14, 2014
In the attachment you will see an example of what I am trying to accomplish. What I am trying to do is find VBA code that is either specific to this worksheet or in a macro. When the sheet is opened I enter a date in B2. I then enter data into A7, B7, and C7. What I would like to happen is when the data is entered into A7, B7, and C7 the sheet goes and finds the same date that I entered in B2 and copies that data from A7, B7, and C7 into F7, G7, and H7.
View 2 Replies
View Related
Apr 14, 2009
I would like to have a macro to automatically generate a statistics table (on the "statistics" tab) with the 5 following fields:
Fragment names / # samples / # of failed samples / % of success / # of variations in the fragment (SNP). At the bottom of this table, I would like to have a cell with the average % of success for all fragments. The data to generate these statistics are on the "gene name" tab (please note that this name will change every time I will work on a new gene). To make things easier, I think the macro should be run from this tab.
1. The Fragment names are displayed in row #5. I use one column per variation per fragment. If one fragment has 3 variations, there will be three columns and I will merge together the fragment name cells. The fact that some cells are merged can be a problem when copy-paste to the stats table (as I would like to get rid of the merging).
2. # of samples corresponds to the number of cells in blue in column A. The number of samples can change from one report to another but is always constant in the same report.
3. # of failed sequences. In the table, I type "Failed Sequence" (if the analysis has failed) and "Missing Sequence" (if the analysis has not been done). When a sample is failed or missing, it is for the who fragment, no matter how many variation there is in the fragment, so I usually merge the cells of all variations for this failed sample.
4. % of success: this is quite easy #sample/#of failed+missing sequence for this fragment
5. # of variation is equal to the number of variations for this fragment (can be 0, 1, 2, etc.). When there is no variation in a fragment, I put '-- in all cells of the corresponding fragment on the "gene name" tab. Fragment 3 on my file is an example of 0 variation.
View 3 Replies
View Related
Sep 11, 2009
Is there a function to convert the data shown in table 1 to table 2 without rearranging the columns and rows? because i don't want to use TRANSPOSE. I want a function, somthing like SUMIF with OFFSET or INDEX and MATCH or any other function.
Table 1
Team 1Team 2Team 3Team 4Team 4Team 5Team 5ABABCity 12531642City 231173705City 367891125City 436251348
Table 2
City 4City 2City 1City 3Team 4BTeam 2Team 5ATeam 4ATeam 1Team 3Team 5B
View 2 Replies
View Related
Mar 13, 2013
Table 1
January-12
February-12
March-12
Table 2
Sr. No
Name
Dep
Lates
CL / SL
AL
Lates
CL / SL
AL
Lates
CL / SL
AL
[Code].....
View 1 Replies
View Related
Jun 2, 2013
I created a lookup table that works quite well. It even has if statements in the LookUp Formula. However, I have to update the table it pulls the information from each day. I wind up recreating the range each time because the table always has more rows each time. Is there a way I could just paste the table in each day and not have to change the range? The columns never change.
View 9 Replies
View Related
Jul 15, 2014
I have a large table that I want to reconstruct. For simplicity sake, let's just says it's 3 rows (excluding headers) by 3 columns.
Item Description
1/1/2014
1/2/2014
1/3/2014
Cheese Burgers
2
3
4
Hot Dogs
5
12
6
Beverages
2
5
3
I want to reconstruct it so that the column headers become values in the table. The table headers are dates, in this case, if that gives clearer picture. So the new table would have 9 rows, (3 rows of data, excluding the header times four columns).
Item Description
Date
Quantity
Cheese Burgers
1/1/2014
2
Hot Dogs
1/1/2014
5
[Code] ....
The above example is sorted by date but I would be indifferent if it's sorted by the Item Description.
Is there an easy way to do this? Pivot possibly? Again, my data table is large: 36 rows x 181 columns. Using the copy/paste/transpose feature is pretty impractical.
View 3 Replies
View Related
Jul 14, 2014
I was wondering if there is any way possible to link your pivot table filters to filter the data the same way in the table that it comes from? So if i had date as one of my headers and i filtered the date to a specfifc date, is there a way to also filter the date in the data source sheet?
View 2 Replies
View Related
May 13, 2013
I have a table below that looks like this:
LOC
# of days
DTX
3
RTC
3
PHP
12
IOP
12
The # of days column will be a manual input. Then I have a larger table that will take those values and convert them to look like this: (Obviously the dollar values are pulled from somewhere else)
1
DTX
$ 1,292.00
2
DTX
$ 1,292.00
3
DTX
$ 1,292.00
[code]....
I've been thinking of trying to use a macro but not sure that is the best way. Using a button or something doesn't seem very elegant either.
View 7 Replies
View Related
Dec 14, 2006
i have a pivot table an extract of each data contained in this table.
[img]Count of NAMdate
SERVICENAM12-oct10-déc11-décGrand Total
Commercial-lauralaura11
Commercial-laura Totalgh11
custody-jonathanjonathan112
k11
custody-jonathan Totalgh1113
settlement-ludovicludovic11
settlement-ludovic Totalgh11
SPQC-elodieelodie112
SPQC-elodie Totalgh112
Grand Total1337
View 9 Replies
View Related
Feb 22, 2007
I did a data table yesterday and it worked. I tried again today and the results are incorrect. They are coming out as a constant (the same result as the original formula). Has anyone had this happen before and figured out how to fix? My spreadsheet is fairly complex. Does the data table formula need to refer to the "base" cells? For example, if the formula refers to cell F15, but in F15 the formula is +C15, does my data table formula need to use C15?
View 3 Replies
View Related
Jul 21, 2013
I have a table thats acting as a database analysing player data and I have a second table in a report sheet based on the database table. In my report table I have a drop down with the player names at the top and I want the data cells underneath to populate based on that particular players data from the database.
View 1 Replies
View Related
Jun 17, 2002
How to (by vba or whatever):
1) convert the Matrix data into the data table, and;
2) convert the data table into the matrix data
Matrix data (example)
share Ashare Bshare C
springsellholdbuy
summerbuybuysell
autumnsellholdhold
winterholdsellsell
data Table (example)
springshare Asell
summershare Abuy
autumnshare Asell
wintershare Ahold
springshare Bhold
summershare Bbuy
autumnshare Bhold
wintershare Bsell
springshare Cbuy
summershare Csell
autumnshare Chold
wintershare Csell
View 3 Replies
View Related