arrays

I have a project where I need to group by first letter of last name. The groups can be dynamic and would range from letters in the alphabet (ie. A-E, F-J, K-P, Q,T,W-Z) The start and end of each of these ranges is in a cell so I can read the cells and create the groups in variables if...

I need to write data to a text file as a data steam I have written preliminary code to capute data in a spreadsheet but the ultimate goal is to write the data direct to a text file without using a spreadsheet. currently the spreadsheet is just to verify that the data is being captured correctly...

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

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

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

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

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

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

{=SUM(INDIRECT("'sheet1'!"&SUBSTITUTE(ADDRESS(1,XMATCH(C$2,INDIRECT("'sheet1'!A1:"&SUBSTITUTE(ADDRESS(1,O1+16,4),"1","")&ROWS($M$1:$M$1)),0),4),"1","")&XMATCH($B3,INDIRECT("'sheet1'!$m$1:$m"&M1),0)):INDIRECT("'sheet1'!"&SUBSTITUTE(ADDRESS(1,XMATCH(C$2,INDIRECT("'sheet1'!A1:"&SUBSTITUTE(ADDRESS(1...

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

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

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

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

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

ABCDEFGHIJ1Trial Balance (in $MMs)2Currency: USD34hhEntity TypeCorporationPartnershipCorporationPartnershipCorporationCorporationCorporation5hhJurisdictionUSUSCACASZNLNL6hhaCompanyCompany ACompany BCompany CCompany DCompany ECompany FCompany G7MappingGL AccountGL...

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

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

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

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

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