Entering Number Arrays In A Spreadsheet (VB)
May 8, 2009Is there any quick way of passing an array to a group of cells ?
It is easy enough to place an array of numbers in a spreadsheet as follows:-
ADVERTISEMENT
Entering Date/time Data As Xvalue For Scatterplot With Vba Arrays
Mar 31, 2007I am trying to create/modify an XY scatterplot using VBA.
I declare local variables and point them to the chart and a new data series for the chart, such as:
Dim chartone As Chart
Dim chartseries As Series
Set chartone = ThisWorkbook.Charts("Chart1")
chartone.ChartType = xlXYScatterLines
Set chartseries = chartone.SeriesCollection.NewSeries()
I set the series data, using an array, such as:
chartseries.Values = Array(1, 3, 5, 7, 9, 11)
I can set the Xvalues to a set of dates by setting .Xvalues to a woksheet range that includes date-formatted data. (like this):
chartseries.XValues = Worksheets("sheet3").Range("m9:m14")
(where m9:m14) contains dates...
Copying Data From Website And Entering Onto Spreadsheet
May 30, 2014Working in Windows 8 excel. I am copying data from a website and entering that data onto a spread sheet. There are certain websites where the data does not transfer on the first try. It sometimes takes up to trying 3 times before the data will download onto the spread sheet. Why is that? And is there anything I can do to get it to transfer on the first try?
View 1 Replies View RelatedSlow Performance Entering Values On Spreadsheet
Jul 3, 2014I have a home finance spreadsheet that I've been using for a few years and in the last couple of days it's ground to a halt. Whenever I enter a value in a cell and hit [ENTER] there is a long delay with the spinning wait icon (Windows 7) and the Excel window reports "Not responding". Eventually (3-4 minutes) the update occurs and everything is fine.
The workbook has 9 sheets on it but only one summary sheet references the others. The sheet I'm updating has about 2200 rows, 50 columns and the formulas are simple A+B-C types. Performance DOES NOT improve if I change recalculation to manual. The only VBA is some macros to perform tasks and they don't execute dynamically. Adding comments and changing formats responds immediately, it is only updating values that exhibits the problem.
I've tried replacing all formulas with their values (paste special/values) across the sheet but again this didn't work.
Entering Specific Number To Effect Number Section On Another Page
Jan 8, 2014I am working on a concrete pour summary, I would like to have the specific codes on each tab be affected from one summary page. So when I enter a certain number on sheet 1 , it will affect that same number section that is on a different sheet. It will be adding a certain amount to that code section every time its entered. What functions need to be used to make this work..?
View 6 Replies View RelatedEntering Specific Number To Effect Number Section On Another Page?
Jan 8, 2014I am working on a concrete pour summary, I would like to have the specific codes on each tab be affected from one summary page. So when I enter a certain number on sheet 1 , it will affect that same number section that is on a different sheet. It will be adding a certain amount to that code section every time its entered. What functions need to be used to make this work..?
View 3 Replies View RelatedSlicing And Dicing CSV Files - Involves Arrays And Jagged Arrays
May 8, 2013I am retrieving a CSV file from the net. In this file there are 'x' amount of row data and 7 columns. I only care about the values in the 7th column for each row. I also don't care about the entire first row. A graphical version would be represented something like this, with the values I want colored in orange:
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
.
. extending until the end of the data set
.
I've managed to dice this thing into a jagged array by first splitting it using vbLf as a delimiter, and therefore adding those to an array called Lines(). Then I split Lines() up using commas as the delimiter and threw those into a jagged array, let's call it Breadcrumbs()(). I want to throw all the values from Breadcrumbs(i)(6) into an array of its own. Here's my code so far:
Code:
Public Sub CSVparser(file As String)
Dim Lines As Variant
Dim j As Integer
Lines = Split(file, vbLf)
ReDim breadCrumbs(UBound(Lines)) As Variant
For i = 1 to UBound(Lines) - 1
breadCrumbs(i) = Split(Lines(i), ",")
Next i
End Sub
Alert Me Upon Entering Duplicate Number
Dec 1, 2009Is there a way to have Excel alert me (via cell color change preferably, but another method would be fine also) upon entering a number that has already been entered on that worksheet?
The problem I have:
I have 10 columns in a worksheet that I keep track of serial numbers in for work... Sometimes someone enters the wrong serial number or doesn't update a serial number so we will have the same serial number in two different places and another agency will call us to correct our mistake, making us look inept.
I figure the best way to alert people of this input error would be to change the fill color of both, or the active, cell(s) so they could immediately begin looking into the problem.
Macro For Entering Data For A Set Number Of Rows
Apr 19, 2009i want a macro that will start on D25 and enter a 1 for five rows up to D29
than change and enter a 2 starting on d30 and enter a 2 for five rows
up to D34 than change an enter a 3 up to D39 ....... on and on up D225.
Add A Number And For It To Appear In The Spreadsheet
Feb 13, 2009I have borrowed a user entry form from a tutorial which I want to use to enter data in a sheet. I have deleted the stuff I don't need and added 4 more fields that I want to use to add to the data. file attached. I just want to be able to add a number and for it to appear in the spreadsheet.
View 4 Replies View RelatedFormula To Pull Number From Another Spreadsheet
Mar 29, 2014I have WorkbookA that pull numbers from WorkbookB. In Cell D2 of WorkbookA is the name of WorkbookB. The data to be pulled from WorkbookB is in Cells H364 through S364, H being data for January and S being data for December. In January, the data from Cell H364 is pulled into WorkbookA. In February, the data from Cell I364 is pulled into WorkbookA, and so on.
The formula can be written such that, based on WorkbookA indicating in Cell B14 the current month, each month pulls the correct month's data from these cells in WorkbookB.
Spreadsheet That Will Give A Total Number For Each
Aug 7, 2009I have a table with 5 columns and approx. 85-90 rows.
Column A has the Branch name in it e.g. Beavers or Bedfont (11 Branches in total)
Column B has User Type - Adult, Child, Guest (Adult), Guest (Child), Catalogue
Column C has Session Type - Booking, Drop-In
Column D has Total Session Time (mins) - which gives a number in minutes of the total session time used
Column E is not needed
I currently get a calculator and add up e.g all of the adult Bookings for Beavers and enter them onto a Report Sheet, then all of the Adult Drop-Ins for Beavers etc. I want an Excel Spreadsheet that will give me a total number for each so I can do away with the calculator.
I am thinking of creating a new sheet with a number of cells that have a formula similar to this
=IF(AND(A2="Beavers",B2="Adult",C2="Booking"),E2,0)
But I want it to see Adult, Guest (Adult) and Catalogue as the same thing / and I want it to pick up Child and Guest (Child) as the same thing.
Pull A Value (text) From Another Spreadsheet Corresponding With A Number
Nov 11, 2008I am trying to pull a value (text) from another spreadsheet corresponding with a number. I can't use vlookup because none of them are in a left column. HEre's an example:
there are 2 columns with names and percentages. I am trying to pull a name that corresponds to a certain percentage.
Max Number Of A Spreadsheet But Exclude 3 Numbers
Jan 4, 2010i have a worksheet filled with numbers in each cell (the worksheet might have a total of 150 rows and 150 columns of numbers).
the numbers typically range from 1-3 or 1-9 or 1-150.
however, there are also multiple 997, 998, and 999 numbers in the cells.
i need to identify the highest number in the worksheet that is not 997, 998, or 999.
VBA Write Text To Spreadsheet As Number?
Jul 22, 2014I have a userform with a textbox that has numbers in it, but when I write them to Excel they are put in the column as text, not as numbers.
[/CODE]Cells(erow,6).Value = txtbox_Amount[CODE]
How can I force it to write the information as numbers?
P.S. My computer won't let me paste into these posts, even when it asks me to allow access to my clipboard. What setting can I change so I can paste my code instead of typing it.
Count Number Of Formulas In A Spreadsheet
Jun 11, 2009For various reasons, I need to determine how many total calculations are being performed in a spreadsheet. These can be IF statements, sum, max, vlookups etc but I am looking to determine the quantity. I do not need to know how many of each just how many in total.
Is there a relatively simple method of conducting this search? I don't mind VBA code, etc.
Spreadsheet Form Number Format
Jun 29, 2006I have a userform with a spreadsheet in it and I am inserting data into a workbook(my data base) with this application. In the spreadsheet changing event I used "0.00" number format (this is what I need) but the results after I type a number is diferent from what I just typed. i.e: if I type 13.56 there is no problem but if i type 6.56 the number is transformed to 20607.00. I have tryed a lot of methods to stop doing that but all of them failed. how to use decimal number format without this problem
View 6 Replies View RelatedAuto Page Number Tabs In A Spreadsheet
Dec 3, 2007I am interested in printing an entire workbook in one shot with multiple tabs, where each tab is one page. I would like to print the tabs such that the first tab has the number '1' in the footer, the second tab has '2' etc. And I don't want to go into each tab and type the number in the footer.
Does anyone know how to easily make this happen?
Create Spreadsheet Where First Page Allows To Input Number Of Teams?
Apr 24, 2014What i'm looking to do is create a spreadsheet where the first page allows me to input the number of teams in the group.The teams cannot play others in the group except the ones they are paired with. eg. "Enter number of team pairs in set" (in this case 4)
Team 1a vs Team 1b
Team 2a vs Team 2b
Team 3a vs Team 3b
Team 4a vs Team 4b
After number of teams in set is established click a start button on the first tab of the spreadsheet.
this auto generates a new tab with all the possible win/loss combinations of 4 team pairs.
New Picture.jpg
if the number of teams on Tab 1 is changed (say to 6) and the start button is clicked, another new tab is generated with all possible win loss combinations of a 6 team set.
remember 1a vs 1b can only be win or lose and the win lose combinations are for the set of 4 matches ( or more matches if specified on the first tab and clicked).
if you put 7 in for the number of team pairs then it would be win loss combos for 7 pairs of teams.
Limit Number Of Rows / Columns In Spreadsheet (beyond Just Hiding Them)?
Nov 27, 2013I would love to be able to limit the number of rows in a specific sheet so that I can quickly carry formulas to the bottom of a worksheet without carrying them to row 1,048,576. I'm aware of options to hide rows and/or to limit the scrolling with the "view code" option on the worksheet tab; however, I'm mainly worried about worksheet performance. When I carry a formula down, I don't want to see a spinning blue circle run for minutes at a time, and I'm hoping to avoid the same spinning circle when I undo my formula. Is there any way to actually limit the number of rows, or is there any other limitation I could put in that would increase worksheet performance?
View 1 Replies View RelatedFilter Out Letters Put In After A Number In A Time Card Spreadsheet
Jan 14, 2009I have the need to filter out letters put in after a number in a time card spreadsheet. I'm not sure that using a select case is the right approach. I need to allow the user to put in a number and a letter signifying what type of time it is. Each cell equals a date on a calendar. For example if the user puts in 8s then the code will add 8 hours to the total sick time, strip out the s and just leave 8 in the cell. The problem is that I need to deal with all of the other letters/symbols that they can enter. From what I know of VBA which isn't much a Select Case seems to be way to go without using a bunch of nested If statements. Here is what I would like to do but this doesn't work. This is a short example of what I have tried as far as Select Case goes.
View 6 Replies View RelatedChart In Spreadsheet That Shows Number Of Claims Per Defect
Dec 31, 2006I have a chart in my spreadsheet that shows number of claims per defect. Is there a way to format this bar chart so that it will only show the top 5 automatically, even when they are constantly changing? There are a total of 13 different catagories.
View 9 Replies View RelatedExcel Spreadsheet - Tally Number Of Votes For Particular Artist Work
Mar 14, 2014We want to create an excel spreadsheet that will tally the number of votes for a particular artist's work. There will be about 150 artist's works that will all be assigned an individual number from 1 to 150 on an excel sheet.
We will have about an hour to complete the counts to determine winners, so a short time frame.
Is there a way of creating a formula/macro in one cell -to just enter the number of the artist's work that was voted for- and have it placed as a tally against the individual piece?
Create A Spreadsheet That Will Automatically Increase The Work Order Number By 1?
Jun 20, 2014I am trying to create a spreadsheet that will automatically increase the work order number by 1. The cell will always be in the same place. The idea is that when the file is opened it populates the number. After being closed and reopened the number will be 1 higher than the previous.
The page will reused by various people to create and print work requests for my mechanics, I want it to assign the W/O automatically.
Export A Group Of Names Then Match And Dump The Corresponding Number Into The Spreadsheet
Jun 23, 2006I have a worksheet that has 2 columns on it 1 is numbers and the others are
names. I want to be able to export a group of names from one of my programs
and then have excel match the name and dump the corresponding number into the
spreadsheet. I beleive I can use vlookup but I am not certain
Tournament Spreadsheet (flexible Number Of Players And Payout Percentages)
May 10, 2014I'm trying to figure out a simple spreadsheet that can spit out tournament payouts based on a flexible number of players (we might have 55, 60, 100) and flexible percentages based on the pot size (if we have 100 players, we would like to pay more players than if we had 50).
Something like [input number of players] [input pot size] [input # of payouts] [input percentage of each payout] it would just save a ton of time as we're trying to calculate payouts on the go.
Separate Spreadsheet Is Sum, By Contract, Both The Number Of Widgets Sold, As Well As The Amounts Due
Jun 16, 2009I have a very long Excel spreadsheet (>2500 rows by approx 20 columns) that consists of >400 customer statements. At the top of each invoice, in colun B, is the text "Service Requestor Contract Number", and 5 rows down is the respective contract number. Further down (and the number of rows differ for each contract as each has its own unique level of detail) , again in column B is the text "Current Month Charges", and on the same row, in columns K and O, the current month number of items sold and $ charges respectfully. The spreadsheet is NOT paginated for each respective statement.
What I need to do on a separate spreadsheet is sum, by contract, both the number of widgets sold, as well as the amounts due.
Formula To Have Spreadsheet 2 Pick Items From Pass / Fail Column On Spreadsheet 1
Jan 23, 2012Workbook 1 has 2 spreadsheets. Spreadsheet 1 contains Item and Pass/Fail Columns. under the item column is the serial number of the item tested. the Pass/fail column has the serial number duplicated if it failed tested. what is the formula is to have spreadsheet 2 pick the items from the pass/fail column on spreadsheet 1?
View 4 Replies View RelatedMulti-Spreadsheet Formula Down To Populate The Other Cells In The Total Spreadsheet
Jan 4, 2010I have attached a document paralleling a document I am working on. The dollar amount in each spreadsheet represent sales. I have entered in values into the candy, soda, and chips spreadsheet. I have also linked values for candy into the total spreadsheet. My question is can I somehow type something or drag the formula down to populate the other cells in the total spreadsheet?
The idea I am thinking but which I don't know how to implement is to list all the items (as in column G) and list all of the relevant cells (e.g. B1 in the Candy spreadsheet) as in columns H and I (Note that all items will have the same cells but the cells will have different values...e.g. all three items have a cell B1 and B2 in their spreadsheet but these cells contain different values). I then try and fail to create a formula in cell B3 of the Total spreadsheet. I am trying to create a formula of the following nature:
='(Spreadsheet Name From Column G)'!(Cell Name From Columns H and I)
The Second half of the formula doesn't really concern me (i.e. the cell name from column H and I). However I am perplexed as to how to achieve the goal in the first parentheses above.
Select Data From Spreadsheet Column If Condition In 2nd Spreadsheet At Same Time Is Met
Aug 11, 2013I have two spreadsheets, one gives me the beginning and end of civil twilight as a measure of day vs. night. The spreadsheet has Date/Time in the first column, and the value 45 in the 2nd column when it is night. The second spreadsheet has also 2 columns with date/time and body temperatures of a squirrel. I want to get basic statistics (mean and standard deviation) of the squirrel's nocturnal body temperature, that is for times when it is night (value 45). The tricky part is that Date/Time of both spreadsheets are different. The procedure has to recognize that the date/time of body temperature lies between the beginning and end of the value 45 blocks of the first spreadsheet.
files: twilight sheet squirrel temperature