array

  1. S

    Sum specific word in area

    Hello, I have 5 columns, all of them can contain specific word 6th column is numbers. Is it possible to take all 5 columns, find if cells contain specific word and sum the number if it does. For example here I want to sum numbers of red only : <tbody> red blue yellow 5 blue red yellow...
  2. A

    Variable Range for Array

    Hi All, Hoping you can help please. I have the below code, which use an array of data to autofilter another range of data: Sub CodeSample() Worksheets("Pending Raw").Select Dim range1 As Range Dim var1 As Variant Dim sArray() As String Dim i As Long Set...
  3. S

    [VBA] Custom two-option user Popup

    Hi all, Hope you're having a good day. I want to create a popup for a user to choose between three options Select a file, Select a folder, Cancel I made a userform to handle this. If they choose to select the first option, I want to use Application.FileDialog(msoFileDialogFilePicker) to...
  4. J

    Array question

    I have a array with 500 records and another array with 25 numbers. These 25 numbers are between 1-500. I want too clear all the records in 500 record array which are not in the 25 records array. Array1 10, 20, 31, 32, 45, 50 Array2 20, 45 After matching the result for array1 is 0, 20, 0, 0...
  5. R

    Array compare?

    I have a project (proprietary, can't go into details, sorry) where I read in data from a server and convert it to a user friendly graphical form. It get's repeated over time and sometimes the source data will slightly change from what was previoulsy imported. My users have asked for me to do a...
  6. R

    Declaring variable types within an array

    I have an array with a mix of data types (strings, dates, integers, etc...). When I use the watch window for debugging, some date values show up as dates, but others as a real number (which isn't very helpful). Not sure how that happens, but that question is for another time. In the...
  7. T

    What is wrong with this as an array formula?

    To start with, an array formula that works as expected. {=COUNT(ABS(Alg.Oth!$AO$602:$AO$4592))} Of course it gives result same as a non-array formula without ABS. =COUNT(Alg.Oth!$AO$602:$AO$4592) So, i tried to modify it to COUNTIF and Excel won't let me enter it, gives the "there is an error...
  8. H

    Comparing Arrays

    Hi, I have two arrays that contain a date, amount and description. I need to compare the two arrays and then process the differences. Possibly place the contents in another array so they can be processed or output them into a sheet. Thanks
  9. S

    hlookup automatically change 1st line in the table array

    Hi My formula is =HLOOKUP($D48,Directorate!D$52:D$350,3,FALSE) As you are aware for this HLOOKUP formula to work the infofrom Cell D48 must be on the line D52 (in the table array source), to bring back the 3rdrow values. However I would like to drag this formula this down (as Ihave 600...
  10. J

    Vlookup with 2 conditions

    Beginning in Column H3, based on the value in column G3, I want to return the starting goalie for the game. So for game 1 I want to return "Pekka Rinne", Game 2 "Jusse Saros" from the array $A$3:$D$12 The linked picture of the example sheet, the eventual array will be 2000 rows...
  11. S

    SUM i to n without Using Cell References

    In excel, SUM(number 1, [number 2], ...) is used to sum an array of cells, however, I'm trying to go back to the mathematical usage of summation: n Σ f(n) = f(1) + f(2) + ... + f(n-1) + f(n) i = 1 I'm trying to use this to plug in values to a function such as PV(i%, n,, F) to sum present...
  12. T

    How to create selection dependant comboboxs and define data source

    Hello, A forum contributer helped me create a dynamic array for 2 comboboxs which works great. Now I would like to understand how to either create a third combobox or set of third comboboxs based on the selection in box 2? Presently the first row of all columns is the title with the data below...
  13. Alphacsulb

    Get array from filtered results

    I'm attempting to capture an array from filtered results to then use later in a filter. I tried following this logic but cant figure out how to change it to my needs since I'm only using one sheet. https://contexturesblog.com/archives/2010/12/15/excel-autofilter-with-criteria-in-a-range/...
  14. FracinDean

    Specifying All Members of an Array

    Good day Mr. Excel users, Searching in the forum and online help have not helped me figure out how to use worksheet function Rank but using members of a VBA array as arguments. I made a simple example to work with. Here is what I have so far: Sub ranker() Dim c As Variant, allVals() As...
  15. Johnny Thunder

    Excel Formula Help - SUMPRODUCT with Array of ID's

    Hello All! Not sure if this is possible but can I create a SUMPRODUCT formula that contains an array of ID's? Below is the mock up of ID's that referece a Named Range "Cdata". This formula works if you duplicate and enter in a single ID (1256, 1261 ect.). Any help is appreciated...
  16. R

    Searching an array for a match

    I have a array that looks like this: UPC, Date,Price UPC, Date,Price UPC, Date,Price ... UPC, Date,Price UPC, Date,Price UPC, Date,Price UPC can have duplicates (tracking prices over time) The list could be quite long (a few thousand lines?), I'm looking for the fastest way to search the array...
  17. C

    Cumulative sum with data in unequal increments

    Dear All, I would like to calculate the cumulative volume of liquid from a pipe over the period of 22 years. Data has been given on a daily basis, however in some instances this covers multiple days. The format of the native data is as follows: <tbody> DAY LIQUID RATE, M3/D CUMULATIVE...
  18. L

    Shape Array TopLeftCell.Address

    Hi I working with a series of shapes on a worksheet where I am holding the shape data in a array and then writing each shapes TopLeftCell.Address into another worksheet to do some calculations. The problem is the For loop is slow due to the number of iterations it has to go through. The...
  19. A

    Vertical and Horizontal Condition to Sum Array

    Hi Folks. I cannot seem to figure out how to approach this one. I am trying to sum integer values in array 'INSERT Tabbing Data-GIS'!$A$2:$BZ$5000 Where column header matches certain criteria, i.e. if 'INSERT Tabbing Data-GIS'!$A$1:$BZ$1 are all available columns, needs to match value in $C7...
  20. W

    VBA to fill array with delimited data from text file

    I'm trying to use VBA to extract data from a series of text files. So far I've found the code below at The VBA Guide To Interacting With Text (.Txt) Files — The Spreadsheet Guru Sub DelimitedTextFileToArray() 'PURPOSE: Load an Array variable with data from a delimited text file 'SOURCE...

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top