arr

I'm trying to cycle thru some columns, but can't get this array correctly implemented. It would be easy to use a For loop with a step of say 4, but unfortunately the last column is 5 columns to the right. Ultimately this is column BL, BP, and BU. Sub AverageCells() Dim x As Long Dim c...

Sub TestIndex() Dim arr, List$, i% List = "10,12,14,16,18" arr = Split(List, ",") For i = 10 To 18 Step 2 Select Case i Case LBound(arr) To UBound(arr) MsgBox arr 'Here, I want msgbox show say 0 to 4 End Select Next i End Sub So when...

I'm trying to use an array instead of a range so I can loop through it more efficiently. I thought I understood the various documentation I researched but can't get it to work. Here's my set up/code: Please help explain why I'm not getting a result. Thanks Dim Arr() As Variant lastrow = 0...

Hi, I have the following Excel Module that creates a new formula called Findword, Currently I have to press Alt + F11 and add it as a new module everytime I open up a new workbook, is there a way to save it in the background so it can automatically be used in every workbook <code class="vb...

I am using this code which works fine: Set copyrng = lo_b1.AutoFilter.Range.SpecialCells(xlCellTypeVisible) copyrng.Copy Destination:=strng arr = strng.CurrentRegion.Offset(1, 0) aRws = Evaluate("Row(1:" & UBound(arr) & ")") arr = Application.Index(arr, aRws, Array(14, 1, 6, 2, 13, 12, 18, 16...

HelloWorld, I have this challenge here with me and I need some help. I want to look compare the day and month of the date in cell A2 with the date ranges below. Then when I find a match , I use that match to select at random one item from one of the arrays. So if the match is seen in...

I need a formula that will show the "Header" that correlates with the first time a value drops by at least 90%. In the example below, in the Yellow column, it should say "Q4 2015". Please help! This data package is huge. I need a formula to apply or I may spend the next 10 years doing it...

Hello friends. Macro gives an error: Run-time error 1004. Method Run of object _Application failed. How to fix this error ? Sub Macro1() Dim arr As Collection, x, t, i Set arr = New Collection For Each x In Range(Cells(6, 17), Cells(Rows.Count, 17).End(xlUp)) arr.Add x.Value arr.Add...

I am receiving an error on the line below: M = Application.Average(Arr(2, 8), Arr(N, 8)) Arr() is an array variable, N is an integer, Arr() is currently (1 to 2978, 1 to 9) How can I use the average function to find the average of the values in the 8th column? Or is there another way I can...

Dear All, I have about 21 files likes 1,2,3,4 as attached - in these workbooks, have many sheets but will have one sheet named "ABC" which is same to all 21 files. I have another Macro workbooks with many worksheets. I wish to consolidate the above data at "ABC" into only one worksheet named...

Hi Guys, I am trying to perform a text to rows for comma delimited column entries. I have included the screenshot of the raw data and the clean data. I am also including my code and a screenshot of it in the excel vba window. If you could please guide me with what I am doing wrong I would...

Hi, I am having a problem with multiple if statements, If a word is found in a cell from the array then if the offset cell equals a value then replace the offset cell with a new value, However depending on what has been found from the array needs to step through a different if statement to...

As the title would suggest i want to remove any columns that do not match an array. I tried the following code but receive an error, does anybody have a possible solution? Dim arr As Variant arr = Array("Title", "Forename", "Surname", "Address 1", "Address 2", "Address 3", "Address 4"...

I'm trying to figure out a way to pull specific text from a long mesh of test in a cell (R). For example, <tbody> "SF123811#ARR676#Unbilled AR Trueup" this is an example of what could be in cell R </tbody> and this formula works when I'm trying to pull the ARR number...

Dear Gents, I have data like following table, and write codes to try get weekly data from daily (because of the format, I can't use pivot table in this case) I'm not sure how to determine how many monthweek in a month. Would you please help me figure out how to fix this out? (D =0 to 4 means...

Hi, I have a column of data and every cell may or may not contain specific words, what I am trying to do is once the first instance of a word (from an array) is found then insert a column before the data column then exit completely out of the loop without looking in any other cells in the data...

Hi Everybody, 1. Just wondering if anyone had a good way to check if there was any blanks in an array? It has to cover numbers and words. I would like to check it first and then 'do something', if possible. 2. Does anyone know why the following COUNTBLANK is erroring when the AVERAGE is...

Hi, I'm struggling to convert the date into ISO format which is giving me wrong results while I compare two different sheet. My inputs are <tbody> 4/9/1984 8/1/2007 F 21006.87 Regular School District 1 Average 34 54 57 57 57 1 4 33.4 18245.93 259616.66...

Hi, Following code errors on highlighted line with Error 13, type mismatch: Sub Test1() Dim x As Long Dim arr() As Variant With Sheets("Sheet1") x = .Cells(.Rows.Count, 1).End(xlUp).Row arr = .Cells(25, 1).Resize(x - 24, 2).Value End With For...

Can I place specific value into an array and then use the array with a loop to delete row if value in array is in cell. i.e. Place West, North, South in Array then use the following code. dim arr as Variant For i = lastRow To 2 Step -1 If Ws.Cells(i, "H").Value = arr Then Ws.Cells(i...