1. O

    VBA Array not working - cannot write to array and read is too slow

    Hello I am writing a code to write this code to populate an array based on an If statement and then unload that array into a range. Here is a sample of my code Sub UpdateFromPCT() Dim wkb As Workbook Dim ws As Worksheet Dim pct As Workbook Dim pctST As Worksheet Dim i As Long Dim holdAry()...
  2. G

    Filter Function Formula

    I have the following formula, I'm trying to get it to use the entire array range if the criteria in the if statement is met. The if statement is true but I can't get the 'include' part of the filter to work so it does the full range. Any suggestions...
  3. R

    Looking to kick rows of data in Sheet A based off what cells in Sheet C say

    Hi, i've got a code that works - kind of. It does run, and it runs at a good speed. Only problem is that it's not kicking the right rows... High level explanation: I've got sheet A with all my data, it goes to sheet B to see what qualifier to use and then finds it in sheet C which ones have...
  4. F

    lookup value with two criteria with array

    hi can anyone help me in following lookup value with multiple criteria, but i want with array plus, which is faster. array formula or vlookup? date code Rate 25-May-21 613974 0.376 24-Jun-21 613974 0.376 15-Sep-21 613974 0.376 20-Sep-21 613974 0.376 29-Oct-21 613974 0.39 21-Dec-21...
  5. J

    How to merge multiple ranges into one array.

    Hy everyone, The below code works when the ranges are A5:J39, A44:J65 and A70:J89 Sub RangesToArray() Dim vRws As Variant, vData As Variant vRws = Application.Transpose(Split( _ Join(Application.Transpose(Evaluate("row(5:39)"))) & " " & _...
  6. V

    Converting a list of date ranges into weekly distribution array

    Hi, I have a list of data with employees and their holiday data ranges, which I want to transform into an array with weeks (showing week ending date - Friday) to show days of absence in that week Employee Start End Week Ending --> 03-May 10-May 17-May E01 30-Apr 15-May 4 5 3 E02 08-May...
  7. L

    VBA CountIfs - Transpose returning 0 (Boolean) values

    Hello, I have some code that I'm struggling with that works fine as simple formulas but doesn't want to work at all as code. The code is simply a countifs from a range counting if values match 2 different given conditions. But for some reason the countifs doesnt actually return any values. Both...
  8. D

    Array - search for value, returning neighbouring cell values with no gaps

    Hi, I needed a similar array formula in the past and was helped hugely by this forum. But I can't replicate for a different context. I'm looking for an array formula that will allow me to search for a value (in a table in a different sheet), and then return all the neighbouring cell values to...
  9. L

    VBA Does declaring Array range with Cell make it a 2-Dimensional array?

    Might be a dumb question, but I have used the following to create an array in VBA - Dim CurrMonth As String Dim PosMatch As Variant Dim MonthDayCount As Variant Dim MonthDays As Variant CurrMonth = Format$(Date, "MMMM") With Worksheets("Tracker")...
  10. L

    VBA Find element given its index

    Hopefully something that isn't too hard but if I have dimensioned an array, something like my_Array = Array("1","2","3") and I want to return the 2nd value of said array. How do I go about requesting specifically the 2nd value of said array?
  11. K

    Adding cell contents to an array reference in macro

    After years of using base excel, I am just now learning how to use VBA to make my work process more efficient. I run weekly reports showing last weeks data, the financial weeks run Monday to Sunday. The data I pull contains data from the day I pull it, I don't want to include this data. I want...
  12. G

    Remove curly brackets from a formula with VBA?

    Some background... I wrote a python program that queries a database, exports the data into an xlsx spreadsheet and then adds that data into a template with existing formulas. The issue I'm having is after the data is added to the template and the file saved; when I access the template most of...
  13. C

    Array adjusting location by a variable

    I'm trying to get a formula that will check an array of 8 cells for "Y", and if true sum the adjacent (-1) cells, and move the arrays X*7 columns over. A static formula that works is SUMIF( X2:X9, "=Y", W2:W9) = 15 where the X is 4 (7*4-5). The problem with this formula it doesn't adjust when a...
  14. N

    4 questions in one post

    1. I have noticed that your website doesn't always load. I have tried power cycling all devices as I have tried accessing it on phone, table, laptop and iMac with no success. Leave it 18 - 24 hours I can get back in? Any thoughts 2. I am using excel Excel 365 on mac and store the files in my...
  15. C

    Substitute for Arrays in Excel Online (specifically Index/Match function)?

    Hi, I have created an Excel Online workbook that I am using with Power Automate in order to pull in Survey123 results (results go into a "RAW DATA" tab). In my "SUMMARY" tab, I am presenting an overview of the Sump data by date. In order to pull specific data from the "RAW DATA" tab and match...
  16. airforceone

    Sheet Name as Array Value

    this code is from @Fluff VBA Run macros on multiple sheets, not all sheets Sub Button1_Click() Dim ws As Worksheet '** SET The Sheet Names - MUST Reflect Each Sheet Name Exactly! WkSheets = Array("ATC1", "BEC1", "DKC1") For Each ws In Sheets(Array("ATC1", "BEC1", "DKC1"))...
  17. B

    VBA code to adding additional filter with array

    I have below code which i use for copying the filtered data to another sheet(ws2). Sub VBA() ws1.Activate lr = ws1.Cells(Rows.Count, 16).End(xlUp).Row For Each c In ws1.Range("P2:P" & lr) temp2 = Left(c, 3) temp = Right(c, 3) temp3 = c.Offset(0, -5)...
  18. M

    Improve Holiday Planner by using named ranges in an array?

    I have a holiday planner to work with - not mine originally but the job do not want to make any significant changes (all "used to this one") In the range I have pasted below there are "Months" defined by merged cells. In the second xl2bb paste is the section that shows the formulas being used...
  19. Q

    Looping through a row in a 2-D Array

    I have 2-dimensional array and I would like to inspect each element in a specific row with If-Then statements and assign assign values to the next row depending on the outcome of the If-Then statements? What is the correct syntax for looping through the elements of a row in a 2-d array?
  20. D

    Is there a way to make this array code more concise?

    Sub Dispatch() rng = shtWF.cells(1, 1).CurrentRegion rprw1 = sht1500.cells(Rows.Count, "A").End(xlUp).Row + 1 rprw2 = sht3480.cells(Rows.Count, "A").End(xlUp).Row + 1 rprw3 = sht2958.cells(Rows.Count, "A").End(xlUp).Row + 1 rprw4 = sht2966.cells(Rows.Count, "A").End(xlUp).Row + 1 rprw5 =...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

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