array

  1. S

    Copy contiguous columns, paste to non-contiguous columns

    Hi there, I have a workbook with integers stored in 6 contiguous columns and 3 rows. ("source WB") For example: A1:F3. However, this range changes from week to week, so the selection of the source range needs to be dynamic. I have another workbook ("destination WB") where I want to paste...
  2. H

    convert Array Formula to Non-Array

    Hello All just trying to get the following array formula to a non-array {=IFERROR(INDEX(TRADES!$D$2:$D$2000,MATCH(1,(TRADES!$A$2:$A$2000=B3)*(TRADES!$C$2:$C$2000=DATE($F$1,$N$1,$E$1)),0)),"")} Thanks
  3. G

    Filter Everything, Except for a Single Criteria

    ActiveSheet.Range("$A$8:$AH$1894").AutoFilter Field:=1, Criteria1:=Array( _ "NOT STARTED", "ON-HOLD", "STARTED"), Operator:=xlFilterValues ActiveSheet.Range("$A$8:$AH$1894").AutoFilter Field:=2, Criteria1:="NCR" Hello All, The Code above is working to filter only "NCR" (after Field...
  4. O

    MS Scripting for directory tree parsing

    Go easy on me. I'm more of a decent hacker using other's code not a programmer. 1st post here. Tried using recursive code I found based on MS scripting (which I barely understand) to parse directory tree to fill an array with all paths. It seems to be parsing fine as I can tell HOW MANY dirs it...
  5. leopardhawk

    Need to add multiplication to an array formula

    Hello forum friends, I am trying to add a multiplier to an existing array formula but it is giving me, not an error, but the wrong result. The formula as it exists is returning a result of 24. I want to multiply this result by 0.621371 but when I add *0.621371 to the end of the formula and do...
  6. M

    Split string into numbers in Excel VBA

    The string can be like "abc123(456 k789)". It is to be splitted in array like array[0]=123, array[1]=456, array[2]= 789. So the problem is to split array based on numbers.
  7. N

    VBA - Executing array formulas

    Hello, The code below was written to update formulas in F5:N and array formulas in H5:AA, and replace the formulas in F6:A6 and below with the output as values. I am using VBA code to avoid a slow workbook, forcing the formulas to run only after I have updated other data in the workbook. The...
  8. A

    Fill down an array formula across multiple worksheets

    In short, what I need to be able to do is fill an array formula down in multiple worksheets. Within each sheet the formula needs to fill down a different number of times. I will give some background info so that this can be understood in context and then paste the code I currently have below...
  9. C

    sum column of numbers based on id # and other possible criteria

    I am trying to do the following but cannot seem to wrap my head around it. I have a workbook with multiple sheets. For what I am doing I have to pull information from one and compare it to another. It looks something like this: Sheet 1 <tbody> A B C D E F 1 Game # Cards Home ID Home...
  10. A

    Array not erasing

    Hi, I cobbled together some code from multiple sources that will process text files. Since we have multiple files in a folder that need to be read, I built the process to read all of the filenames in a folder into an array (DirectoryListArray()) that I use as a master list of files, then...
  11. S

    Userform Picture and text selection

    Good Morning, I was wondering two things, both interrelated: 1. I have a drop-down box that selects a bunch of info from an array using vlookup formulas and vba coding. Is it possible to have a collection of pictures saved on a sheet in the workbook and then the background of a userform as...
  12. J

    VLOOKUP vs. INDEX/MATCH on variant array

    I have a multidimensional array with 'x' rows and 3 columns of data, and I'd like to perform a VLOOKUP on the array information stored in memory rather than calling the worksheet each time. I'm just not sure how to write the code to perform the VLOOKUP using INDEX/MATCH, and repeat that process...
  13. G

    Array Formula Question - Condition An Array by Itself

    Hello! I'm trying to solve the following issue using an array formula but I'm struggling. I'm fairly versed with arrays but can't figure this out. Thank you in advance for the help! Using the example below, I'd like to pull the max number associated with each individual text in the first...
  14. Y

    Alternative to Array Formula

    I am using this formula : =IFERROR(MEDIAN(IF((rngProgramme=rngEruCourse)*(rngOwnerType="LA")*(rngStartYearList=rngStartYear)*(rngCreatedDate1>=rngLeadsFrm)*(rngCreatedDate1<=rngLeadsTo)*(rngRating="Null")*(rngDisposition=""),rngAging)),"-") THen Ctrl + Shift + Enter and I have a lot of cells...
  15. M

    Formula to Sort a Subset of Conesecutive Positioned Values of an Array with Constant Values?

    I am looking to figure out in Excel 2016 how to write a formula that will sort a subset of values in consecutive position within a larger array with constant values (zeros), but keep those zeros in their same positions thus keeping the number of values in the array the same? Moreover, an...
  16. W

    Store row numbers in an array

    I've got 2 types of data and thought it might be a good idea to use an array to store the row number in for subsequent processing; is this a better solution than ifs & loops ? Both sets of data are in blocks of approx 4 to 7 rows but each block in its raw form is separated by a blank row so...
  17. 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...
  18. 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...
  19. 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...
  20. 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...

Some videos you may like

This Week's Hot Topics

Top