Macro For Sizing XY Grid With Data Table?
Jan 28, 2014
macro to automatically size an XY grid based on user input (see picture - [URL]......)
I want to enter a value in B6 that will automatically create a new grid or edit the existing grid to increase or decrease it depending on the desired grid size.Once the grid has been sized I want to enter a data table into it, therefore the grid would need to be cleared of contents before it could be resized.I think these are the steps that are needed:
- Enter grid size.
- Area (named range based on existing grid values?) is cleared of the data table.
- new rows / columns are inserted with correct XY coordinates shown based on newly entered grid size
- data table written into the new grid
View 5 Replies
ADVERTISEMENT
Oct 24, 2013
I created a table where based in several competences anyone can evaluate performance and potential. My issue comes when based on that result (colum Y in "Perf&Pot" Sheet) I want to export that to a nine box matrix (Sheet 2 "grid" in the attached excel). As could be several people in the same box I wasn't able to com up with a formula that solves this.
Pasta1.xlsx
View 1 Replies
View Related
Aug 11, 2009
Is it possible to show a table in a user form? For example I have a table that is 4 columns wide and 5 rows deep with lines seperating both. I have tried using a text box and list box to achieve the results I am looking for but can't figure out how to add lines to seperate data between rows and columns.
View 5 Replies
View Related
Aug 13, 2008
about using list as a datagrid type. I was able to research some samples and applied it on my own, but unfortunately I am missing something again here. The data on the worksheet are not being populated when my range is on the AA column. It works with A1 column but not the far ranges. Im not sure about the limit of range anyway here is the
View 14 Replies
View Related
Oct 3, 2013
Basically, I need to extract data from a grid/table (11 columns wide) and transpose it into a list for use in a pivot table. It like sort of reversing a pivot table to access the original data but I only need 5 of the columns repeated for each occurrence.
I have read many internet sources but they don't seems to fit my needs. Here is a solution from a similar question posted on StackOverflow entitled "How to “flatten” or “collapse” a 2D Excel table into 1D?"
The attached simplified mock-up explains what I am trying to do. I have colour coded the data to understand. The number of rows (10) will always be the same but the number of columns (Branches in this case) can extend for many columns.
View 4 Replies
View Related
Jun 20, 2014
I want to create a function called PasteGrid(SourceRange As Range, GridHeight As Integer, GridWidth As Integer)
What this will do is take a long single row or column of data and paste it into a grid with a defined GridHeight and GridWidth, I have came up with the below so far:
Code:
Function PasteGrid(SourceRange As Range, GridHeight As Integer, GridWidth As Integer)
Dim rRange As Range
Dim rCell As Range
Dim DestinationRange As Range
Dim GridWidthCount As Integer
Dim GridHeightCount As Integer
[Code]....
Something appears wrong and when following the code when it hits this line:
Code:
Cells(Destination.Row + GridHeightCount, Destination.Column + GridWidthCount).Value = rCell.Value
The function just stops and I'm not too sure why?
View 1 Replies
View Related
Jul 1, 2014
I'd like to automatically populate a 9 box grid using data from Excel. The number of names for each rating can be anything from 1 to 100 so the size of the grid would need to be adjustable. I would like one table for each function and grade. Attached is what I'm trying to do. In the 'example tables' sheet, I've shown how I'd like the tables to look, but with my live data the tables would be bigger so I'd like to do one table per worksheet (or possibly one function per worksheet - so 3 tables).
View 3 Replies
View Related
Jan 16, 2007
I sometimes import data in the copy-paste way from completely different sources, eg. a program on the web or whatever, which has arranged the data in a table.
It is often succesful with the data neatly arranged in the spreadsheet in columns and rows and all, but sometimes the gridlines disappear even though 'cell format' is set to 'none' in 'grid lines' and the settings have a mark for 'visible grid lines' and their color is 'automatic'. Only the imported area misses the gridlines.
It is of course possible to set some grey gridlines manually in cell format, but then I get them on the print also and I don't want that.
View 9 Replies
View Related
Feb 9, 2010
I have the this code in ThisWorkbook to open the workbook with two windows, each with a different tab showing, and each taking up half the screen:
View 3 Replies
View Related
Jul 10, 2009
I've written a macro to add buttons to a sheet of mine. The buttons call up various other macros. The problem I'm having seems to be a screen update. I've set the button size to be greater than the number of characters it contains. However when I run the macro, it first adds the button at its default size (width which is too small), and bombs out.
Below is the code I'm using:
Dim Width As Integer, Height As Integer, HzSpace As Integer, VtPos as integer
'Dimensions
Width = 144
Height = 20
VtPos = 400
HzPos = 275
View 9 Replies
View Related
May 27, 2006
Once again this is a general best practice question.
What does the forum believe is the best practice sizing for command buttons and which font and size is best.
I am trying to give a solution I am working on astandard Microsoft application feel to it.
View 3 Replies
View Related
Feb 14, 2007
I have a workbook where each name in a list has a picture of a flag of the country for that name. The flags are initially assigned to a cell with the country name (see sample attached). Later in the project the flag is copied to a cell in another sheet and assigned to the correct name in the list. The row heights in this list of names is larger than the row height of the sheet with list of countries.
The flags in the sample have been manually sized and positioned as accurately as possible. I am struggling with the VBA code to size and position each flag exactly so that there is a very small gap between the top, bottom and left of the flag and the cell border. Each is set to move and resize with cell.
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
Feb 14, 2013
I'm trying to streamline some processes at work and have encountered a bit of an issue.
I have a source file containing a grid of data (9 columns) with a series of dates they relate to down the left side e.g. 13-Feb-2013. This data covers a whole year.
What I want to do is pull through data relating to a discrete month into a grid in a seperate workbook.
I have a feeling that date ranges and possibly some kind of lookup might be useful but am struggling to see how to pull it all together.
View 3 Replies
View Related
Mar 23, 2009
I'm trying to come up with a MapsCo grid to "overlay" geoLoc data. Given the coordinates of a single box within a MapsCo page, I'll can figure out the others once I know how to "from this point, add .5 miles due North and mark another point; from that point, add .5 miles due East and record the next point; etc".
View 5 Replies
View Related
Mar 4, 2014
i've a excel grid with data in rows and columns with values in each pair (row/column).
i need to transform each combination in raw data to process to a database with for ex:
row 1 col 1 value (33)
row 1 col 2 value (5)
row 1 col 3 value (56)
col1
col2
col3
col4
[Code]...
View 1 Replies
View Related
May 27, 2014
I have the following code:
[Code] ....
When I run the macro, some columns are already hidden. The macro doesn't seem to autosize cells correctly. For instance, one cell in a row appears to have some contents hidden (or below the reading area of the cell). In other instances, the rows are auto-size to huge heights and widths.
View 7 Replies
View Related
Oct 7, 2008
I have a large table of data which is currently just in alphebetical order. I also have a blank template of the exact table format on another sheet in the same document.
I am trying to create a macro which will pull everything into the current blank table but ranks each row by the overall score (%).
View 3 Replies
View Related
Jun 18, 2009
I'm trying to automatize the filling of 4 different tables regarding time (hours , minute)
In other words first i need to select from about 500 products names about 180 products i need. I solved this with an automatic filter like this
View 14 Replies
View Related
Nov 28, 2011
I have a data table which looks like this:
1 2 3 4 a b c
5 6 7 8 d e f
And it should be converted to look like this:
1 2 3 4 a
1 2 3 4 b
1 2 3 4 c
5 6 7 8 d
5 6 7 8 e
5 6 7 8 f
So basically, the data in the last columns should be transposed, and the data in the first columns need to be copied in the cells of the new rows.
View 1 Replies
View Related
Aug 26, 2009
Im trying to do is create two tables from data evenly spaced throughout columns A to I. To make the screenshot easier to view I moved the second table under the first. The data is arranged so that each Subject has 42 columns and is arranged by Type and then by Subject.
The tricky part is that the number of subjects will vary so everything has to be done in some sort of loop. From the little I know about VBA I was able to record and edit a macro to add spaces every six rows and average the MaxResponse. (thats what stage the screenshot was taken at) To make the table I think I need to write some sort of Dim statement but that is way beyond my comprehension.
Here is a link to a photo of the table http://yfrog.com/77excelshot3j
Excel 2003
VBA 6.5
Win XP Pro
View 9 Replies
View Related
Jun 27, 2008
I've got a bunch of pivot tables with nothing in their data fields and I want to add two things to the data fields of each, but I keep getting this error as soon as I start my loop:
Run-time error 1004: Unable to get the PivotFields property of the PivotTable class
Here's the code that is highlighted when the error comes up:
ActiveSheet.PivotTables("Master_" & Count).AddDataField _
ActiveSheet.PivotTables("Master_" & Count).PivotFields( Sheets("raw data"). Cells(1 + Count, 2)), _
("Sum of " & Sheets("raw data").Cells(1 + Count, 2)), xlSum
I recorded doing the first one by hand and it looks like this:
ActiveSheet.PivotTables("Master_1").AddDataField ActiveSheet.PivotTables( _
"Master_1").PivotFields("10/2 Spread"), "Sum of 10/2 Spread", xlSum
I don't understand why I'm getting the error because I added watches and my variables are all saying what I want them to say (Master_1, 10/2 Spread)...
View 3 Replies
View Related
Apr 13, 2013
This is the macro I am using
Sub Macro3()
'
' Macro3 Macro
'
Range("C2").Select
Selection.Copy
Sheets("Table").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
[Code] .....
It does everything I want but take F2 info and send it over. But F3-F6 does go over.
View 6 Replies
View Related
Feb 28, 2014
Macro to extract data from cells and populate them into a table. Go to the links below for the images. Why I can't attach images here.
HTML Code: [URL]....
View 2 Replies
View Related
Aug 1, 2014
I have a spreadsheet where I am tracking several entries in a table that will keep growing. Three fields are Data Validation Drop Down Lists. The macro below works well to clear the two lists to the right when the first one is changed by the user.
[Code] .....
I want this to affect the rows below it in the table as they are added.
View 2 Replies
View Related
Apr 4, 2014
I'm having difficulty trying to get the second file appended to the bottom of the 1st imported file. I get "run-time error '13' type mismatch". There is no difference between the two files. I'm thinking there is a problem with my range statement in the second file import, but this range works fine in other macros. Here's my code so far:
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\server1usersmydataIMPORT1.CSV", Destination _
:=Range("$A$1"))
.Name = "IMPORT1"
.FieldNames = True
.RowNumbers = False
[Code] .........
View 7 Replies
View Related
Jun 11, 2014
I have a leave history report with data for leave taken for the 2014 year. The sheet contains the following headings:
Name; Leave Start Date; Leave End Date; Date Paid; Days Taken; Roundup; Offset
The roundup column rounds the days taken up, for example if someone took 0.4 days off it would round it to 1.The offset column just subtracts the Roundup value by 1 (=Roundup-1) I think we will need this for what I am trying to do.
I have created a new spreadsheet labelled gaant chart, this shows the name of the employee with the working dates for the year and has the following data:
Name
1/01/2014
2/01/2014
3/01/2014
6/01/2014
7/01/2014
8/01/2014
9/01/2014
10/01/2014
[code].....
What I would like for the macro to do is lookup Employee 1 from my gaant chart table and search the leave history data if it finds a relevant start date for the employee it marks that date with a 1, it then looks in the offset column and marks the number of offsets to the right. For example if employee 2 took leave from 1/1/14 to 3/1/14 the macro would mark a 1 on the 1/1/14, and search the offset column which would have 2, it will then mark a 1 in the 2 cells to the right of 1/1/14 also (these being 2/1/14 and 3/1/14)
View 1 Replies
View Related
Nov 28, 2006
The "Sum" sheet can change its number of rows. The pivot table is based on it. I'm having trouble with the SourceData portion of the code in my macro ....
View 9 Replies
View Related
Nov 26, 2007
I want to have 2 cells where I can enter 2 numbers (length & height) so that a grid can be plotted. For example, a big sheet of paper size is length 30" x height 8", if first number is 10" (length) is entered, it will plot 3 lines across 30" (cos 30" divided by 10" equals to 3) and if I enter 2" (height) on 8", it will then cut it into 4". Attached a file for better understanding.
View 14 Replies
View Related
Jun 15, 2009
I'm not worried about the order of the data. What I want is information on turning a grid into a list.
View 4 Replies
View Related
