I want to take select records from one sheet and sum only the matching records from another sheet, but the ranges are different sizes. As an example, in Sheet1, I want to select the codes with a "Rank" of "1", and total the "Cost" of those matching codes in Sheet2.
I need a non-VBA solution.
in my spreadsheet, there are two pages:
[data] ....
on the 11th row there should be a formula which sums corresponding column, according to how many items and which items were used in this particular set: corresponding prices should found from array "prices" (page a).
i'd like to use array formula (ctrl+shift+enter) if needed (like: {SUM((units=units2)*set2)}) or sumproduct function (like: SUMPRODUCT((units2=units)*set1) ), but can't use it, cause the arrays aren't equal. any ideas (except additional column on sheet B, where reside corresponding prices, then can use sumproduct function)?
I place the below formulas in A1,A2,A3 …A100
A1 = --(D10:D20="A")
A2 = --(E10:E20="F")
……….
These formulas return arrays {1,0,01….) and of course they can not be displayed in single cells (error message).
Is there a way I can use them in a sumproduct formula?
C1 = SUMPRODUCT(A1*A2*A3…*A100)
The reason is that in reality they are much more complicated and I get error messages (length of the sumproduct formula) and secondly this way I can “understand” and use better my formula.
I dont think that i can "break" the sumproduct in smaller pieces and would be great if i can do something like the above.
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
I have a document with about 300 comment boxes, and I needed to enlarge each one to make the information visibly fit in the region. I did this, and was happy.
Then I opened the file on another computer, added a few rows, saved the file, and re-opened it on this one, and all the comment boxes in the original rows (the 300 i had, not the 15 I added later) had been reduced in size and not all the text is visible. Why did this happen? Is there an easy way to re-size all the boxes without doing them one at a time?
I have a macro that checks data in a worksheet and manipulates the data.
the problem that I am finding is that the worksheet data, which is copied from another file, is different sizes - eg: in one file the data may be from a1 to f2890 but another file the data may be from a1 to f5990.
When the file is bigger I have noted that the macro does not check the rows between 2890 to 5990.
How can i get it do check all the cells no matter how big the file is?
A text file is imported to Excel Worksheet. I use this code to Select/Copy a section of that import;
Selection. Offset(4, 0).Resize(15, 9).Select
Selection.Copy
'The number 15 would mean there are 15 rows of items.
But there are not always 15 rows. There might be 18 rows, but because the code is set to 15 it will miss selecting 3 rows preceding, or if the code is set to 12 it will not select the full item list. However, whatever the amount of rows of items, be it 15,2 18 or 100 for example, directly under the item amounts is a "marker" purposely put in the file before the import for other search functions.
Above that marker is the total Item count! So I need a code to search for that marker, then move up 1 cell and whatever that number is, if "15" would then determine the correct row count to Resize
The "marker" is : ^3^
So the full code is:
Macro1 ()........................
I have a macro that contains a line:
Range("BB1011:CX1064").Select
The number of columns selected remains the same, but the number of rows changes. The indicator that tells me how many rows to select is contained in cell F1007 and in this example contains "54".
How do I adjust the macro to change the number of rows to select dependent on the value in F1007?
One of my coworkers is trying to create a document that has 3 columns in one part of the spreadsheet, but further down on the page, he needs to have 4 columns but wants them to use the same amount of space as the three columns. I have looked around and don't see how that is possible.
View 2 Replies View Relatedim trying to find and delete records within a column if they occur twice. this works great right now but I want it to exclude the top 8 rows... i think it might have something to do with the LookAt:=xlPart constraint ...
View 4 Replies View RelatedI'm in the middle of building a code that will look at the value in Sheet: "DATA" column "J", for each row that matches a criteria that I pull from a cell - Sheet: "Test" Cell: "C1".
The number of rows varies. I want to paste columns: "I2, K2:P2, U2:AJ2" when the value in Sheet: "DATA" column "J" matches the cell "C1".
I'm using a Command button to click every time I want to generate the filtered data. And I would prefer the code to always copy data starting at Sheet: "Test" Cell: "K2".
I've copied, below, my test code that I have so far which only copies row 2 from the Sheet: "Data", and copies it into Sheet: "Test", starting at K2.
VB:
Private Sub CommandButton2_Click()
If Sheets("DATA").Range("J2").Value = Sheets("Test").Cells(1, 3) Then
Sheets("DATA").Range("I2,K2:P2,U2:AJ2").Copy
Sheets("Test").Cells(2, 11).PasteSpecial Paste:=xlValues, Operation:=xlNone
End If
End Sub
When I publishing from Excel document to PDF it produces differently sized pages as shown in attached. Is there a way I can achieve a same size page view without having to change the font size and page layout of the source Excel document?
View 4 Replies View RelatedI have a macro that I run repeatedly in a certain type of spreadsheet that does an export on a selection, pulls up a "Save As" message box and saves as a text file. There are 9 sheets in the workbook that contain the same data analysis for a set of samples, the only difference between them being the number of data (e.g. some may have 1500, 1400, 1600, etc.). I only select three columns of data to export (I6:K????) that contain counts (1,2,3,....), x-coordinates and y-coordinates. The counts column (I) uses a formula that only counts if there is an x-coordinate next to it (Col J).
[Code].....
Generally, what I do to make quick work out of selecting the variable ranges is to select Sheets 03-11 (the first nine sheets) and select K6:I6 (starting w/ K6). Then, on each individual sheet, I do CTRL+SHIFT+DOWN to select all the relevant data (if I started my selection with I6, then it would select all cells that contained formulas which may or may not have x,y-coordinates adjacent to them). Once the data is selected (I6:K????), I run the export macro and save the data as text. I would like a macro that can automate the selection for each Sheet 03-11, excluding Sheets "all", "data" and "summary", and run the export macro, first prompting me for a file location and a file prefix. When the text file is saved, it uses the file prefix and Worksheet name to build the filename, i.e. "pathprefix_wkshtname".
I've attached an example workbook : 20120511_Au-cit_pH5_test.xlsx‎
What I’m doing is sorting one page and copying the information to another page in the same workbook. But I keep getting this stupid error.
Run-time error '1004'
This operation requires the merged cells to be identically sized.
I am trying to write a Macro that will insert a Text Box that auto-fits the shape of a cell to hide its content. Once finished, the Macro will need to lock the cell and the text box so the contents of the cell are hidden. The idea is that I want to share a spreadsheet with someone but want to hide individual cells for various reason.
Sheet1A1SAMPLE DATA2sample3sample4HIDDEN5sampleExcel 2007
I tried to record a macro as a starting point but it recorded nothing. I searched around and it seems to be an issue without a solution.
I could obviously change the formatting and the contents of the cell but the idea is to preserve the contents if possible.
I have been trying to solve this problem by breaking it down to it's core elements and it seems to hang up at a certain spot but I can't figure out why.
formula is: {=LARGE(IF(AND(Array1+8<20,6-Array1<0),Array2,0),1)}
where: Array1 is a sequence of numbers, say, 2 5 8 11 14 17
Array2 is a corresponding sequence of numbers 1 2 3 4 5 6
the resulting array should return the numbers 0 0 3 4 0 0
and my answer should be 4
instead my answer is 0
when I break it down and select six cells and use the formula:
{=if(Array1+8<20,Array2,0)}
my result is: 1 2 3 4 0 0
and another six cells and use the formula: {=if(6-Array1<0,Array2,0)}
my result is: 0 0 3 4 5 6
when I select a single cell and use the formula: {=large(if(Array1+8<20,Array2,0),1)}
my result is: 4
when I select a single cell and use the formula: {=large(if(6-Array1<0,Array2,0),1)}
my result is: 6
I've tried this method several different ways, even using "Yes" & "No" as my result and then try to match them up. I've tried doing this not using named array and just selecting the cells themselves for the formula. All with the same results.
How do I deal with arrays in vba? Below is a dummy code just to check the array I specified is working:
View 5 Replies View RelatedI have a worksheet that has 20 columns and 500 rows. I created a VBA macro to loop through the data to hide rows that do not meet certain criteria. After the data is selected I copy and paste those selected rows to another sheet. The macro works well but I would like to use an array to contain the data that I copy and paste to a new sheet. I have been trying to find information on multi deminsion arrays but I have not been able to fully understand how to get the information into the array and then how to get it out again. Most of the examples that I have seen are for two maybe three columns (dimensions). I am hoping that someone could point me in the right direction to get started on this. I also have a few books on Exel VBA but none of them seem to address my question.
View 9 Replies View RelatedI am trying to fill calumn B with the data from an array. There are more rows than entries in my array and I want to loop back to the start of my array when it reaches the end of the array results.
Sub test()
Dim i As Integer, MyArray As Variant, RowCount As Integer, ArrayCount As Integer
MyArray = Array("test 1", "test 2", "test 3", "test 4", "test 5")
RowCount = Cells(Rows.Count, 1).End(xlUp).Row
ArrayCount = UBound(MyArray)
For i = 1 To RowCount
Range("B" & i).Value = MyArray(i - 1 Mod ArrayCount)
Next i
End Sub
The formula is designed to sum a set of data based on 22 variables between a certain date range. In order to keep the formula manageable, I have grouped the variables into arrays.
EX.
Array 1. - 20 potential choices (Service codes)
Array 2. - 2 choices (pkg types)
Array 3. - 2 choices (volume type)
Array 4. - 3 choices (company names)
Each array (listed horizontally on one spreadsheet) calls a specific column of data to match from a different worksheet. I have no problem if only applying one array but multiple arrays return incorrect values.
I have two 2 Dimensional String Arrays with data. I need to find a way to get the difference between these two Arrays. I am new to VBA, I don't know how to deal with these. I certainly feel that there is some efficient function for doing this. or Is the naive two for lop concept is the only way to go?
View 2 Replies View RelatedI have two arrays that output as two columns next to each other. Like this:
ID
Responses
1
4
2
3
3
2
4
5
5
1
I would like to sort the responses column and have it show as this:
ID
Responses
4
5
1
4
2
3
3
2
5
1
I am guessing that I need to some how merge them into one array so I can sort them as one entity rather than sorting two columns separately.
Here is my sample data and the macro I use to pull the data i need for the two arrays. sample.xlsm
Can you declare a const as an array in VBA? For example, let's say I wanted to define an array of ORDINALS:
Const ORDINALS(0 to 9) = ("0th","1st","2nd","3rd","4th","5th","6th","7th","8th","9th")
I have an array that will open specific workbooks. But Now I need to append a Case Number to the beginning of the file, and I don't know how to cycle through two Arrays. I have been setting it up as:
Code:
Dim Report
Dim Reports
Dim WB AS Workbook
Dim WS AS Worksheet
Reports = Array("Case01", "Case02", "Case03", "Case04")
For Each Report in Reports
Then I open the workbooks and print them.
Next Report
Now I want to add in a 2nd Array, that will Append the 1st item in CRN to Reports, then the 2nd item in CRN to 2nd item in Reports. So it would look something like this.
Code:
Dim Report
Dim Reports
Dim CRN
Dim CRNS
Dim WB AS Workbook
Dim WS AS Worksheet
CRNS = Array("0501202201", "0134851081", "9715288103", "1697774009")
Reports = Array("Case01", "Case02", "Case03", "Case04")
For each CRN in CRNS
For Each Report in Reports
Then I open the workbooks and print them.
Next Report
Next CRN
But that cycled through each CRN before moving to the next Report?
I have two arrays of the same size and shape. Question: is there a more straight forward way to add up all the corresponding elements in both arrays without looping through each pair of numbers one at a time?
View 2 Replies View RelatedCurrently I am trying to concatenate two arrays stockotherarray and stockfittingsarray to create stockarraynew
Below is my code, I keep receiving a compile error.
Sub stockarraynew()
stockarraynew() = Split(Join(stockotherarray & Join(stockfittingsarray))
End Function
Does anyone happen to know of a list of formulas in Excel that can be used as Array formulas?
I am trying to get a better grasp of what happens with array formulas in Excel - for instance MATCH seems to be a good candidate to hold an array of values when confirmed with CSE, but INDEX seems like a formula that wouldn't itself hold an array of values (although it might be able to lookup the corresponding cells of two arrays with corresponding rows and columns).
Below is my attempt to understand working with arrays of values.
Test() works fine. Yay! But it is "too easy" just plugging in a hard value for the range address...I don't always have that luxury. Normally I work with ranges that I've determined at runtime. I'd like to assign a range of values to an array but my attempt below -- test2() -- fails. I am not even sure if my effort is a good approach or not, but I know it doesn't work!
Basically, I am looking for how to assign the range of values to an array when I am determining the range in code -- as in:
After
Set Range = ~~
or,
Range(Cells(1,1),Cells(x,y)) ~~................
I need to find the possible combinations for several arrays consisting of binary data. I shall give a simplified example with three arrays and four fields. Lets assume the three starting arrays look as follows:
1 0 0 0
1 0 0 0
1 0 0 0
Each row may contain one field = "1" and all other fields = "0". So the next possible combinations may be:
1 0 0 0
1 0 0 0
0 1 0 0
1 0 0 0
1 0 0 0
0 0 1 0
etc...until:
0 0 0 1
0 0 0 1
0 0 0 1
Assuming k = number of fields and c = number of arrays the possible combinations are k ^ c = 4 ^ 3 = 64. I am looking for vba coding that allows to cycle through all possible combinations and displays them in an excel spreadsheet. Ideally, the code should work for any k and c. Could anyone please give me any pointers as to how to tackle this problem?
p.s. in essence I'm looking for the tabular representation of a tree plan with four branches to commence with, a total of 16 decision nodes, and two possible outcomes (1 and 0)
Well I find myself going round in circles so I thought I'd just ask hopefully solve things quicker.
I'm struggling with arrays in VBA, at some point I could use them but now it seems I've forgotten everything and even reading some tips on-line doesn't seem to be helping at this point.
I'd like to be able to use a dynamic 2-dimensional array to store values. I'm reading these in from a worksheet under certain conditions (depending on a value in another column) and that part works. I know I'll always have 2 columns but I don't know the amount of rows.
I'm calling a Sub that does an if-test and copies the valeus into the array, then I'd like to "reDim" the array 1 row larger for the next pass.
Column1 Column2
Text1 Value1
...
Text2 Value2
...
Text3 Value3
...
To get an array like so:
Arrayindex(i,1) Arrayindex(i,2)
Text1 Value1
Text2 Value2
Text3 Value3
Depending on how I make initial declarations I either end up with an "subscript out of range" or "array already dimensioned" error. So I'm apparently not getting the right syntax in the dim or redim?