1. S

    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...

    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...
  3. B

    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...
  4. D

    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,, test3,, desired output sheet...
  5. A

    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...
  6. K

    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...
  7. K

    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...
  8. A

    Reading all items from a Listbox into an array

    Hi guys. This problem is driving me bananas. I have two list boxes. The first is populated with training subjects that are outstanding for an employee. Using buttons, some subjects are moved across to the second list box. Now, when I click on the OK button, I want all items in the second list...
  9. N

    Loop Matching Arrays (sum values) - VBA Please help

    I have to match both columns below. To get a match you need to sum/combine some values in TC column and combine some values in AE column. (e.g-1 sum of Items 1 & 2 of TC Column (20,378) will match to the sum of items 2 & 3 of AE Column (20,378) e.-2 sum of items 15,18,19 of TC Column will...
  10. M

    Re-Initialize Conditional Formatting Array Formulas (50+ Rules/Columns)

    Hello all! I have a workbook with multiple tabs (such as "MF1 - Employee Data") that acts as a template for clients to place their employee data into. Also in this workbook is another tab (Validation Data) that contains all of the configuration/build details of the client's environment. The...
  11. 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
  12. Z

    arrays verses worksheet for math

    I have a workbook which currently uses a worksheet to do a lot of math for a user form. Speed, accuracy and overall performance is very stable so far. My question is, I've always wanted to do the math in memory with arrays just because I guess and learn arrays. So short of that is there a more...
  13. Z

    arrays count unique items?

    Hello folks, I have an array of say (s,e,se,sw,s,n,ne,ne,s,e,se) and would like the count of the unique items in that array using VBA. 7 in this example I believe. Is this possible and how would it be done?
  14. A

    Pivot Table Challenge - Arrays?

    Excel friends, The Pivot Below has more than 10,000 Codes. I need to find a formula or a way to get a list that looks like the summary on top. The Description is the one corresponding to the maximum on the Total Column. I understand it can be done with Arrays. It will be a very elegant...
  15. A

    Sorting an Array "Subscript out of Range"

    Can anyone help me with what I am not understanding regarding this bubble sort. This is just the start of my learning curve and I'm falling at the first hurdle. This is a one dimensional array with 762 rows in a single column. Am I getting mixed up with the operation of rows and columns here...
  16. G

    Using Arrays to populated Matrices and other data

    I have a spreadsheet where i create 4-5 fairly large matrices in worksheet cells and then perform some further calculation using these. I'd like to update the spreadsheet to not use worksheet cells and to hopefully run faster. I've started to do this using 2D Arrays to store my data in...
  17. C

    Which one cost more memory to run?

    Dear forum users, I'm sure there's answer to this on Google but I can't seem to find it. I'm used to storing data in VBA arrays and/or collections. But that can sometimes be hard to visualise when I have so many of them. The other way is just to store it on the worksheet! It is easier to...
  18. L

    Using a variable inside code

    Hello guys, I'm trying to minimize lines that i have in my code and i ran into a small problem I'm not sure how to approach I have 3 arrays that i fill up depending on a variable: If fullArray(i, 2) >= 1 And fullArray(i, 2) < 3 Then arrayInQuestion = "sheet2" If fullArray(i, 2) >= 3 And...
  19. M

    VBA Arrays

    Hello, I am using VBA arrays to select certain options in the list. For Example the Code below Selects five people in the table. What I want to do with the array is to select everything excluding the options already selected. So instead of selecting these five people, select everything else in...
  20. L

    Keeping array values after macro run

    Hey guys, I have a project that I'm making and it contains 3 arrays. Once i run the macro, all the actions are done, but the array is not erased, if i re-run the macro the values inside keep doubling(keeps old values and adds them again). I am using erase at the end of the last sub now to make...

Watch MrExcel Video

This Week's Hot Topics

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
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
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