Loop Through And Create Table
Mar 20, 2014
I have a piece of equipment that dumps data into excel that looks like this:
#1 Asset
#1 Widget
Component1
Horizontal
0.017893
2/21/2014
5
[Code] .......
This order goes on and on for hundreds of rows with different widgets and different components.
I am trying to find write a macro that will sort the data to be summarized into a table like this:
#1 Asset
#1 Widget
Component1
Horizontal
0.017893
2/21/2014
5
[Code] ........
View 1 Replies
ADVERTISEMENT
Feb 12, 2014
I need to create a macro that can create a dynamic copy/paste loop. So far what I have is horribly inefficient. Each row in colmn A(minus the header) has a unique number in it. For each unique number, I need to paste it based on the number of column headers in row 1(minus column A). So, if there are 20 column headers, I need to copy cell A2 and paste it 19 times in another sheet. Then, I need to move to the next number in column A and do the same thing. Here's what I have:
[Code] .........
You can see that this is not dynamic. If I add another row to my table and rerun the macro, it will not catch it. I've attached a sample file to show you the big picture of what I'm trying to do. The data that I have is in Sheet1, and I'm trying to get it into the format in Sheet3. Rows/columns will be periodically added to the table in Sheet1, so the macro needs to be dynamic to catch that. The data in Sheet3 will always remain, and the macro will add the updated data below the old data in Sheet3.
FC_Macro_Sample.xlsm
View 8 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
Apr 26, 2012
Recently, the boss showed me a Pivot table & chart, which consists of a list of about 30 user names in the first column. The row headings were the different items they purchased from a vending machine. & when he clicked on any name in the first column, this created a new sheet, renamed with users name, with a small table of results showing what that person purchased.
Problem is, none of us can figure out how to do this. I have created a new Pivot table & chart exactly like the original, but I cannot get the smaller sheet to generate. (Excel 2010)
View 3 Replies
View Related
Dec 17, 2012
I have a table with list of equipments and asset number assigned as primary key, I wanted to have a history movement of these equipments but my idea is somehow not logical and very primitive to update every time 2 excel sheets:
ie:
asset no
description
received by
day
[Code].....
View 2 Replies
View Related
Nov 5, 2008
I want to accomplish something like this but slightly different:
[url]
I have the same issue but a bit more complex. In my case I have an additional amount of columns (let's say dimensions). So for the example given (sales per month) I would add two columns for 4 different regions and 5 machine types (just examples).
The output (that would serve as pivot tabel input) would then have to be a 4 x 5 x 12 x 4 = 960 row database table.
I have tried to accomplish it by customizing the given code but that provedto be beyond my powers ;o)
and some 3rd party software [url]and [url] but non of them is doing what I'm looking for.
The code I'm looking for ideally would count the number of columns and unique records per column and construct the database table from that. So I am looking for flexibility in the number of dimensions also...
View 6 Replies
View Related
Nov 18, 2008
I have an excel sheet that will have a column with out of sequence numbers. I need a script that will evaluate each row and insert a row and the missing sequence. Below is the code I have written but I keep getting a compile error saying Else without If.
View 4 Replies
View Related
Jan 22, 2012
I'm sure this is easy but I'm trying to create a for...next loop that goes across the row. My final output will be basically be to see if the cell says Saturday or Sunday and offset by 2 rows and highlight the rest of the column.
Code:
Range("A1").Select
For X = 2 To Range(Activecell, Selection.End(xlRight)).Column
Range(2, X).Select
ActiveCell.Value = "Column"
Next X
That's what I came up with but it doesn't seem to be right.
View 3 Replies
View Related
Mar 19, 2009
how to adjust this code.
What I want is a loop.
Highlighted in red near the top is cell a1, this needs to change to a2 then a3 then a4 and so on for as many times as i need it to.
highlighted in blue near to the end of the code is cell a1,this needs to change to a41 then a81 then a121 then a161 and so on(so +40)for as many times as i need it to.
Code: ...
View 9 Replies
View Related
Feb 10, 2010
I want to loop through a list of numbers and add a hyperlink to each number. This hyperlink refer to a sheet with the same name as the number. how I shall use hyperlink line below. The loop and everything else is ok. I use a loop like the one below and want to refer the Hyperlink to activecell each time
View 2 Replies
View Related
Apr 30, 2012
i would like to create loop throug among columns, is xlup or xldown methods will make sensne ?
View 2 Replies
View Related
Jul 10, 2009
I have been trying to create new worksheets, name them and create webquery according to the input on table B4:C13 in test1 sheet attached (code is in module 1). After I run the code it stops after creating sheet with name 1 and shows error 1004. here it is the
View 8 Replies
View Related
Apr 1, 2009
I've got a data set (words and text) where in column J the number 100 appears at random intervals. Each time the number 100appears, i want to take all data from cell A:J and copy that data to a new worksheet and then name it with the word in cell A.
View 7 Replies
View Related
Dec 10, 2009
i created list in Excel and i wrote into (a1 cell-income, b1 cell-expence, c1 cell-kredit and d1 cell-debet). i enter a2 cell income (for ex:1000) and b2 cell expence (for ex: 100) and I give condition in VBA that if income greater than expence then VBA writes a2.value-b2.value into d2 cell else if expence greater than income then it writes b2.value-a2.value into c2 cell but i only define this one row (for ex: c2, a2. b2. d2).
i would like to assign it the rows as long as i want
that is i would like to create loop
(for ex: a(i), b(i) and so on
here is
View 3 Replies
View Related
Jan 27, 2012
I am trying to create a loop to add multiple worksheets
I want to add a new sheet for each company (A2:A14)
I am also wanting to add the sheets after the current last sheet if possible.
Sub addnewsheet()
x = 2
Do Until Cells(x, 1) ""
Sheets.Add.Name = Worksheets("securities").Cells(x, 1).Value & ".ax"
x = x + 1
Loop
End Sub
View 3 Replies
View Related
Mar 1, 2012
I am trying to do payslips. Basically I have one worksheet with about 7 columns. I have a 2nd worksheet that is like a template payslip.Worksheet 1 has hours worked and pay etc.
I want to find a method to loop through worksheet 1 and using worksheet 2 as a template create more worksheets and have the values come from worksheet 1 in the new worksheets.
View 2 Replies
View Related
Jun 23, 2013
I keep getting errors with this vba code. I'm trying to make 10 new worksheets with the name of each worksheet in the Newname array. I'm trying to put the same formula (with the difference that each formula would take from a different "Newname" file. I'm trying to put first formula from A1 to A 560 and second formula from B1 to B 560.
Sub Namesheetformula()
For x = 1 to 10
Newname = Choose(x,"A","B","c","d","e","f","g","h","i","j")
Firstformula = if(isna(match('Master!'A1,'Newname'!A1:A560,0),"",'Master'!A1)
[Code] .....
View 9 Replies
View Related
Oct 19, 2011
I have a static table that contains a list of all posible components for a particular product that we sell. The sheet successfully calculates the qty. of each component required for a particular install.
What I would like to be able to do is to create a new list able from the static one that only shows the components that are needed i.e. those that don't have a zero qty count.
I have seen some examples that come close on YouTube but can't find anything that does exactly this.
View 6 Replies
View Related
Apr 2, 2014
I am having an issue with looping through data ranges. Below is a subset of my macro. What I require is for LegendRng to stay the same and DataRng to move after each loop.
However everytime the vba runs through a loop, the previous range is recorded i.e. the source data for the chart is "A1:D5" instead of "A1:D1, A5:D5").
[Code] .....
View 1 Replies
View Related
Jan 6, 2014
i have a range of rows that change (could be 10-100) how do i loop the range to create new worksheets named for each row?
View 3 Replies
View Related
Mar 11, 2009
I am trying to create a loop which stops once a cell is empty.
Assuming I have declared proper DIM, my code is
Row=1
Do
Row = Row + 1
SheetName = Cells(Row, "A")
Sheets.Add.Name = SheetName
Loop Until IsEmpty(SheetName.Offset(1, 0))
View 9 Replies
View Related
Jul 26, 2013
I have a folder with 20 Excel files. I'm trying to create a master list of all the tab names. I can see all of the files opening, but it only copies some of the names.
VB:
Sub GetTabNames()
Dim wkBook1, wkBook2 As Workbook
Dim stFilePath1 As String
Dim FileList(1 To 18) As String
Dim iLoopSheet, iLoopProg As Integer
Application.ScreenUpdating = False
[Code] .....
View 2 Replies
View Related
May 28, 2014
Trying to create a simple loop that will change the visibility of a large quantity of CheckBoxes when a certain value is selected within a ComboBox . I'm very new VBA programming and loops are not something I've tried yet. My code currently is this:
[Code]......
When I run it, I get a "Compile Error: Invalid qualifier".
View 2 Replies
View Related
Aug 13, 2013
I am trying to design a function that grabs an average of only numbers that are within a certain deviation of the mean. I got the plan down, and have done this before without VBA, its just a user defined function will be much quicker.
The gist of what I am doing: Find the standard deviation of a range and then only find the average of numbers that fall within n deviations from the mean. I am stuck with the part that I would normally use an array code for. Here is what my array would look like:
{=AVERAGE(IF((nums>high)*(nums
View 1 Replies
View Related
Apr 8, 2008
I am looking to create a number of series on a chart (xyscattersmooth) from a datatable. Below is my data Table....
View 9 Replies
View Related
Oct 8, 2008
I’m getting data from query. My data has fields: Item No, Customer ID & Sales rep.
I need to find out for each Item number which customer put order and who was the sales rep. (Excel 2002). see file attached.
View 4 Replies
View Related
Aug 11, 2006
I have two worksehets. The first worksheet contains data on products. The product code is in column A. My second sheet contains a list of product codes in column a. I want to delete all rows in worksheet 1 where the product code doesnt exist in the list in worksheet 2. Can someone provide a outline for a macro to loop over sheet one and look up the value in worksheet 2 and delete the row from worksheet 1?
View 3 Replies
View Related
Sep 27, 2012
I have a pivot table with two columns: Code and Quantity. I want to loop through the Quantity column and hide all values = 0. I can do this easily by incorporating this into my code:
Code:
pt.PivotFields("Code").PivotFilters.Add _
Type:=xlValueDoesNotEqual, DataField:=pt.PivotFields("Sum of Quantity"), Value1:=0
However, I have a "(blank)" code in all pivot tables. I do this because occasionally there is no data to organize in a pivot table. Having a "(blank)" option will allow my code to work even when there's no data. Therefore, I want to keep blank visible at all times. The above code hides "(blank)", however.
The below code is what I've tried with no success.
Code:
Set pf = pt.PivotFields("code")
For Each pi In pf.PivotItems
If pi.PivotFields("Sum of Quantity").Value = 0 Then
[Code] ........
View 4 Replies
View Related
Jan 4, 2010
I'm trying to write a macro that will create a pivot table, and am getting an Error code 1004: Cannot Open Pivot Table Source File "Sheetname". My code is below. I've tried to note what each section does, and it all seems to work well except for the Pivot Table creation.
View 14 Replies
View Related
Feb 25, 2014
Working on data presentation. I have a log that is populated from elsewhere in the workbook. I would like to be able to have a table update itself as the log changes. In the table I need to capture each unique name and all dates associated with the name. I have a sample attached of what I hoped it would look like when working. Basically I have a place for the name and to the right, cells that represent the months of the year. The data captured has the date in mm/dd/yyyy format and I just need each monthly instance to land in the proper month column in the table. No need for date specific, just the month of the occurrence.
View 2 Replies
View Related
