Add Values To 2 Arrays Depending On What Is In Worksheet Table
Jan 4, 2012
I have some arrays to loop through some code which generates some graphs:
Code:
Sub demo()
Dim aServer
Dim aType
Dim i
aServer = Array("a", "b", "c")
aType = Array("1", "2", "3")
[code]....
I am now trying to populate the arrays dynamically and thought I could work that out myself, but this is my first ever time with VBA and it's not as easy as I thought (Or maybe I'm not as clever as I thought!)
I have a table in a worksheet with column headings and row headings which are the values to be added to the array.
Lets say I have the following:
Column headings = "x", "y", "z" (these are the potential aType values)
Row headings= "1", "2", "3" (these are the potential aServer values)
_,x, y, z
1
2
3
The values in the table are either TRUE or FALSE (Or blank which should be interpreted as FALSE). Where there is a TRUE the column heading should be added to the aServer array and the Row heading should be added to the aType array.
In this 9 cell example (Not counting the row and column headings), if the first 2 rows had all the values as TRUE (See example table below) it would therefore result in the following arrays:
_,x, y, z
1,TRUE,TRUE,TRUE
2,TRUE,TRUE,TRUE
3,FALSE,FALSE,FALSE
Code:
aServer = Array("1", "1", "1", "2", "2", "2")
aType = Array("x", "y", "z", "x", "y", "z")
If the table was as follows:
_,x, y, z
1,TRUE,FALSE,FALSE
2,FALSE,FALSE,FALSE
3,FALSE,TRUE,TRUE
It should result in the following arrays:
Code:
aServer = Array("1", "3", "3")
aType = Array("x", "y", "z")
this would work out how many rows and columns there were based on how many column headings and row headings there were (So I wouldn't have to change the hardcoded VBA when the table grows, therefore making it easier to share the spreadsheet with others)
View 3 Replies
ADVERTISEMENT
Mar 26, 2014
I want to write down the code that will populate values in "Sheet1" from the Access table. The column headers shows "Envelope types", "Envelope Size" fields from the Access table and each cell should store sum(volume) for each month in the table.
As I can't upload access table in the attachment so I have exported data into Workbook named "tblmain" as attached. But in actual tblmain is Access table. consider it an access table.
wrting code that will fetch data from access table and store in all the cells of the table in "Sheet1" of Elevate workbook.
View 7 Replies
View Related
May 8, 2013
I 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
View 1 Replies
View Related
Mar 26, 2009
I have 2 sheets recording feedback and summarising the overall percentages for each trainer.
My boss wants me to do this by month!?!?!
I have created a third sheet but I am not sure how to do the following:
Look up the trainer name
Look up the month
identify the percentages for each category
create an overall average of these percentages
So for example if Jon smith trained twice in JAN getting 100% and 50% in cat1, it would display 75% in the cat 1 cell and so on.
View 7 Replies
View Related
Feb 26, 2009
My difficulty is this. I have 2 columns, A and B. A contains only 0's and 1's. B contains any number from 1 to 100. I want to count all the instances of any given number in Col B that are matched with a 1 in the corresponding cell in Col A.
View 6 Replies
View Related
Jul 7, 2009
How can I add an array of values to a listobject, preferably in one big chunk? How can I read a chunk of values from a listobject into an array?
For the latter, I've tried:
View 2 Replies
View Related
May 26, 2006
Given the following data located in Sheet1 of a Workbook -
•I have seven columns (assume header row names are “A, B, C, D, E, F, G”) each containing numeric data in random order.
•All columns are of the same length (equal number of rows), followed by a blank cell, but the number of rows is unknown.
•Columns with header row names A-E will only contain numbers from 0 to 100, while F and G may contain numbers from -127 to +128.
Here is what I would like to do –
I would like to be able to analyze the data in columns A-D to locate rows that contain specific combinations of values (ex. row 41 contains the values 50, 0, 0, 0 and row 239 contains values 100, 0, 0, 0 in columns A – D, respectively, etc.).
For each row that is found to contain one of these combinations (there are many different combinations required), I would like to copy the associated values from columns E, F, & G for that row, and paste them into their respective (E, F, & G) columns located on Sheet2 of the same workbook. However, I would like to paste the E, F, & G values into a specific row order on Sheet2, DETERMINED by the value of the A – D combination identified on Sheet1.
Continuing with the ex. above, let’s say I would like to have the EFG values from row 239 in Sheet1 copied to row 10 in Sheet2, while the EFG values from row 41, Sheet1, are to be copied to row 11 in Sheet2.
In effect, I would like to create a table of this subset of data, in a specific order, from which I could work later.
I can do this manually using “ AutoFilter” to locate the data I need and then write formulas to copy the data from Sheet1 to Sheet2 in the order that I need, but every time the order of the original data on Sheet1 changes I have to start all over.
View 9 Replies
View Related
Jul 11, 2014
I have the following arrays.
A1=Array("A","D","F")
A2 = Array(Array(0, 1, 4, 5), Array(2, 0, 1, 1), Array(2, 3, 11, 3))
B1=Array("D","X","F","J")
B2 = Array(Array(6, 0, 2, 1), Array(1, 1, 6, 1), Array(7, 9, 1, 0),Array(0, 3, 2, 2))
I would like to print in column A all the unique/common values between Array A1 and Array B1. The common values are A,D,F,X,J. And I'd like to print in Columns D:G the A2(i) values and print in columns I:L the values B2(i) in the corresponding row of values of arrays A1 and B1. If some string only appears in array A1 (i.e. "A"), this means that string "A" have values associated in array A2, but not in B2, then print zeros from I:L and if the string only appears in array B1 (i.e. "X" and "J"), this means that strings "X" and "J" have values associated in array B2, but don't have valus in A2, then print zeros from D:G for the same row of X and J.
View 7 Replies
View Related
Jul 14, 2014
Plotting of bar and line graphs given an arrays of x and y values in VBA? I want to plot the rDateRange and rOEERange.
Code:
Dim rInitialDate As Range, rFinalDate As Range, rDateRange As Range, Calculation As Worksheet
With Worksheets("Calculation")
Set rInitialDate = .UsedRange.Find(What:=DateValue(InitialDate), After:=.Cells(1, 4), LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=xlNext, SearchFormat:=False)
[Code] .........
View 2 Replies
View Related
Aug 16, 2008
I have problems with my userform's listboxes. I have two listboxes, and I want second listbox's values to be dependent on first listbox's values.
And even more complicated, I need second listbox's values to be dependent on values on certain matrix.
In that matrix, row headings are listbox1's values and column headings are listbox2's values. How ever there are blanc cells on that matrix aswell. So if there is a blanc cell(s) on a row which (heading) is selected at listbox1, then I don't want that column (heading) which intersects with the blanc cell to be included to my listbox2 values.
Finally I want to insert the selected values from listboxes and the value from the intersection of those listbox values (headings) on that matrix to worksheet.
I included an attachment, where you can see my point better. However, as you can see, now the listbox values are not dependent on that matrix. Otherwise it is working like I want it to work.
View 11 Replies
View Related
Oct 31, 2013
I'm trying to get dates within a column on a table highlighted depending on their value.
I know how to do this using today's date, but I am having issues in using a separate set date as the parameter.
For example;
I want all values in a table in =K7:K72 that are more than 30 days older than a value set in A3 to be highlighted red.
This should be quite easy, but every time I enter anything the whole column changes colour when this is incorrect.
View 1 Replies
View Related
Apr 15, 2014
I am tracking business hours of various business locations. I would like to be able to enter the businesses operating hours on the "overview sheet" and, based on those hours, have a table generated on another sheet that "autofills" based on the date entered on the "overview sheet". For example:
I would enter the operating hours of the business on the "overview sheet" and it would look something like this:
Day
Open
Close
[Code]....
I want a table for each day created. I would then manually enter the data for the "Ranking" column. The issue is that I have numerous location I want to do this for and all with varying hours of operation. It is very tedious to manually create the tables.
View 9 Replies
View Related
Feb 7, 2007
I am trying to populate a table (a7:c27) in the attached sheet with data.
First, I must enter a number 1-16, which uses index function to return either "series" or "base"
If it is base, i want the number in the corresponding table filtered down - i.e. if 1 originally picked then h7 is (40) is entered into each month as base = constant.
If the result is series, then the data in i7:z7 is entered into the the 18 month table.
View 3 Replies
View Related
Jan 7, 2009
I have a list of names in column B and either a 1 or 0 in column A as below:
0 A B
1 1 Bob
2 0 Chan
3 0 Lucy
4 1 Billy
On another worksheet I want to be able to list only the names with a 1 in column A. This must be done in another worksheet so I have something like below.
0 A
1 Bob
2 Billy
3
4
View 3 Replies
View Related
Jul 7, 2009
One of the worksheets contains all of the data and the rest are empty. I need to do is move entire rows of from the main worksheet in to worksheets named the same as the value in column C of the row.
for example one row may have TEST in column C so i want to move it to the worksheet called TEST. The next row might have TESTING in column C so that would go to the worksheet called TESTING. and so on.
View 3 Replies
View Related
Oct 24, 2008
.............................24............30............36
300014999..........9.00%.......11.00%.....12.00%
1500099999........9.50%.......11.50%.....12.50%
100000249999.....9.00%......11.00%.....13.00%
I need an all inclusive formula (all in one cell) that would look at the above table and depending upon the price paid (3000-14999 or 15000-99999 or 100000-249999) and depending upon what monthly term they choose (24, 30, or 36), the appropriate finance charge would be used to calculate a total cost (9-13%). The only way I know to do this is by using IF's and AND's, but there are simply too many arguments and I cannot properly write the formula.
View 4 Replies
View Related
Sep 29, 2009
How can i do sum of the values which are in red color
CODEAMTA1B2C3D4E5F6G7H8
View 9 Replies
View Related
Jul 22, 2014
I am trying to copy images from one worksheet (master worksheet with all data and images) if a cell value matches and place onto a separate worksheet with select rows on it. I've watched this tutorial: [URL], but it only shows how to do it in one cell rather than a whole column.
Essentially if the figure in column A matches in the second worksheet, I want the row data to be duplicated including the image.
For the copied data from the master worksheet to another, I used VLOOKUP and it works great but obviously that won't work with images.
View 3 Replies
View Related
Feb 10, 2014
I have 2 worksheets, 1 with a table sorted like this (in a row):
component | start date | end date | assigned to
Second worksheet with a table like a calendar with dates and people (dates in the columns and people in the rows), every component is assigned to each person by dates.
............ | 01/01 | 02/01 | 03/01
---------|----- --|-------|-------
person 1 | comp1 | comp1 | comp2
-------- |--------|-------|-------
person 2 | comp1 | comp1 | comp3
I want the assignment from worksheet 1 to worksheet 2 to be automatically. i will set the start and end date next to the component in the first table and assign it to a person/s and it will be automatically get filled in the second worksheet (the calendar) under the person/s and under the same dates as set in the first worksheet.
You can see a template here: [URL] ........
View 9 Replies
View Related
Sep 9, 2013
The Room ID values in Column A are associated with the Room values in Column B. I'm trying to move the values in Column A Room ID to Column G Room ID by having excel look up value in Column C Room or Area #, compare it to Room, associate that with Room ID and automatically fill in Column G Room ID. There are 1000s of these so it's not possible to do it by hand.
I attached a picture where i had 2 different workbooks. In reality, I'm working off of 2 worksheets within a workbook.Excel Question.jpg
View 7 Replies
View Related
Mar 23, 2009
I tried both IF and LOOKUP and failed. I'm trying to search for values from one worksheet and identify whether or not those values exist in another worksheet. I attempted the following lookup in field A2:
=LOOKUP(B2,Sheet3!A$2:A$914,Sheet3!C$2:C$914)
B2 (thru B5000 or so) contains values I want to search for; sheet3!A$2:A914 is where I want to look and column C of that same sheet, entered the text "Yes" in an attempt to have the results list "Yes" for hits and N/A for misses. (All fields are text.) I copied the formula all the way down the sheet in column A. The result it is returning is N/A in A2 and Yes in A2 -to the bottom, which is incorrect.
View 2 Replies
View Related
Jan 12, 2010
There is a data range in the E + G columns. The content of the E column can be 5 different items with variable GROSS weights in the G column:
.........E..........G..........R...........S...........T
1.....Size.....Gross....%.........Net......Size
2.....200 g....138......56.52...20........45 g
3.....100 g.....84....................35......100 g
4......45 g......52....................60......200 g
5.....500 g....253...................124.....500 g
6.....Strip......15.......................7........Strip
7.....100 g.....90
8.....500 g....280
9......45 g......62
10........................
View 2 Replies
View Related
Sep 12, 2013
I have created an excel document with If formulas. If nothing gets entered in that particular row, the row simply says "None". Is there a way to get rid of any row that contains that value by any chance?
Something along the lines of if the row says "None" then that particular row gets automatically hidden? It would probably need to be in VBA?
View 9 Replies
View Related
Jun 25, 2014
Example:
Sheet: Users
Column A has "Yes and No"
Column B has "Names"
ex.
A B
Yes Peter Johnson
No Patrick Andersen
Yes John Smith
Now i would like to create a list in sheet [Employee] where one the users with Yes in column A is shown.
In this ex.
Peter Johnson
John Smith
No need to show yes or no.
View 11 Replies
View Related
Oct 23, 2008
I have a workbook with several drop down boxes and formulas already set up and working. I want to improve it though. My question is.... is there a formula that will make the drop down list change based on a cell value....
For example: If B6 equals vegetables then C6 equals list (potato, carrot, pea, etc.) If B6 equals fruit then C6 equals list (apple, banana, grape)
View 6 Replies
View Related
May 28, 2009
So, I have some names and values. Rank function give me order for those values.
Small function gives me ascending order. I want to get first n (let say 5) values back next to each other but can't use VLOOKUP function because sometimes I get duplicates (red numbers).
If there is more same numbersthat small function returns... It need to give me all of them, no matter 5 is limit. how to get back values of rank function that are duplicated. Book1.xls
View 2 Replies
View Related
Jan 3, 2013
see attached document - this is a stock record sheet. what i want it to do is when Bed is seleceted in column B - i want colum C to show the BedSize list which is in the lists tab. and the same with Chair i want it show ChairSize
View 14 Replies
View Related
Feb 25, 2009
I would like to distribute values in cells depending on the input value (excel file attached). This file is a test and basically i've been entering the data manualy. The format is flexible, so it can be reorganisaed.
View 2 Replies
View Related
Jun 17, 2012
I'm looking to get a formula to counting unique values listed in a column depending on a condition also find the attached file for more details
[URL]
View 9 Replies
View Related
Jan 23, 2004
Does anyone know the code to send an email to a set list of six recipients when cells in a certain column in a worksheet reach a certain value greater than another. Let me explain a little - The action to be performed is date-based.
For Example:
When the date cell L2 is 30 days later than the date in K2, send an email with subject containing data from cells B2 and C3.
However, this has to work for columns K and L in their entirety, as both columns contain a list of dates...
View 9 Replies
View Related