# arrays

1. ### Sumproduct with 3 tables?

Dear users, I am trying to do the following but have been unsuccessful therefore I seek your help; I have vendors who buy TVs from certain Towns. The top left table displays the quantities of TVs bought by each vendor who has a record of purchase while the bottom left table displays the...
2. ### Convert If(Or string to Arrays

I have a formula that lists several cells to look for the letter X. If X is in any single cell a text is displayed "Package Price" in K25. Need formula to be converted from strings of cells to arrays. Current formula: If(OR(E41=”X”,E42=”X”,E43=”X”,),”Package Price”,””) Need new formula to make...
3. ### HOW TO MAKE IF STATMENTS INTO ARRAYS

hey there, im trying to change a co-workers if statements into an array if possible im trying to validate that If Colum AA is 1 or greater then to check if Colum AQ is between 7.3 and 8.3 and if its anything else to check Colum y and if there is a date then colum AQ should be zero, if there isnt...
4. ### Find matching cells in array with VBA + opening sheet when found

I have 2 arrays in 2 columns in the same sheet. The first array displays the sheet name for all clients. The second array displays all client names. When a client's name has not been inputted into their sheet, the second array defaults to match the name of the client sheet. i.e Array 1...
5. ### Using the results of a formula as part of a new formula

Is there a way to turn C4 into a formula that actually performs a sum range of the result produced by the formula that's already in the cell? Referencing C4 in a sum range from another cell works (for example, [SUM(INDIRECT(C4))] in C5 produces the correct value of "0"). But is there a way to...
6. ### How to Access Array Made With Arrays?

Hello, I am a novice to VBA, but I'm getting there. I am trying to build a program, and one of its aspect is to use two-dimensional arrays. One of the functions get directories, where it fetches an array of folder directories, an array of spreadsheet directories, and then stores both of those...

8. ### Excel VBA Replacing Value When Text Entered is NOT in Array List

Hi there: I've been looking at some other posts on replacing text entered with values from two arrays. Works when the values exist in the array; however, if the user enters a different value, then I'd like replace it with "". Please see what I have so far below, and let me know if you have any...
9. ### Creating an array from a range and keeping the 0 row/column

This may not be expressed well, but... I want to create an array from a large range (changes depending on the data but let's say A1:Y7000). This is easy enough with something like dim rg as range set rg = range("A1:Y7000") Dim arr() as variant arr = rg However, I want column and row 0 and...
10. ### Excel VBA - Arrays instead of Index Match formula

Hi all, I have 2 workbooks and would like to lookup row values using the common columns of Workbook 1's Column B and Workbook 2's Column F, match corresponding row values in Workbook 2's Column I, which is empty, to Workbook 1's Column Q. The Index Match formula in...
11. ### Option Base 1 But LBound Is 0

Hi all, Using Excel 365. I have Option Base 1 at the top of my Modules However my code is returning Subscript Out of Range error And when I hover over the array variable for LBound it shows as 0. thanks, -w Error here: Debug.Print vCriteria(i), vColumns(i) Full code: Option Explicit...
12. ### Error in VBA Find Function

Hi all, Using Excel 365. My Function is returning an error: Public Function FindColumnHeader(rng As Range, _ SearchTerm As String) As Long FindColumnHeader = rng.Find(what:=SearchTerm, _ LookIn:=xlValues, _...
13. ### SUMIFS with multiple arrays

ABCDEFGHIJ1Trial Balance (in \$MMs)2Currency: USD34hhEntity TypeCorporationPartnershipCorporationPartnershipCorporationCorporationCorporation5hhJurisdictionUSUSCACASZNLNL6hhaCompanyCompany ACompany BCompany CCompany DCompany ECompany FCompany G7MappingGL AccountGL...
14. ### Conditional COUNTIF() on a whole row

Greetings, I have a worksheet with 737 columns; A1:ABI64 - below is a small representation of it. In cells B11:B18 I want to add a formula to reference the person in column A and count the 0's for that person, in the range A1:ABI64. What is the best way of doing this please? I have tried...
15. ### What type of array am I working with when I place specific columns into an array and try pasting them to a new sheet

Greetings I'm fairly new to VBA and finally require some assistance, as I can't seem to find this question on any of the forums I have a sheet with multiple columns, but only need a select few. I recently came across a method to paste an entire column in an array with a line of code that...
16. ### Rank (Spearman) Correlation of Non-Adjacent Values in Excel in Single Formula

Rank (Spearman) Correlation of Non-Adjacent Values in Excel in Single Formula 1. In a single formula, I am trying to get the Rank (Spearman) correlation between two arrays. One of the arrays contains non-adjacent values. I can get the Pearson correlation with this formula...
17. ### List IP's in a list of ranges

Does anyone know how to go about generating a list of ip's in list of ranges or seen it done using a macro? Thanks input sheet test2 10.10.3.0-10.10.3.9, 10.10.3.11, 10.10.3.15-10.10.3.16 test3 199.10.3.0-199.10.3.9, 199.10.3.11, 199.10.3.15-199.10.3.16 desired output sheet...
18. ### Creating a Function to Compute the Dot Product in VBA

I have been trying for hours to create a function that will find the dot product of two independent columns. I have very limited knowledge in VBA and my professor is no help. This is what I have right now, which is heavily based on what I learned in class. Does anybody have suggestions of what...
19. ### Comparing two arrays and go to if-condition if match was found

Hello, I have two arrays Arr1 and Arr2 that both include a list of words. I need a code that compares every element of Arr1 with every element of Arr2. If at least one match was found I wan't to add more code. So it would look like this: If Arr1=Arr2 Then ... The comparing part is not working...
20. ### Filtering based on input in listbox

Hello all, I need help with the following macro please. I am trying to use a userform to allow a selection of criteria. Based on the selection a column in the excel sheet should be filtered. So far this is my code: Dim Ary2 As Variant Dim i2 As Long, j2 As Long With Me.ListBox2...

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back