i have a 2D array of numbers (7 columns x 30 rows which doesn't change size nor place in the book).
I have the address of the "starting" and "ending" cells of the area I need to sum (often in more than one row) squeezed between other tables which must not be part of the calculation.
i have this formula, and i think it will work if i find a way to correct the blue areas. they are trying to define the match array. i tried using the address and cell functions with no luck. i just want the array to return something like B2:B15 (from the AXS sheet) so the match function works. in the current sheet, C8 and C9 have values that refer to rows in the AXS sheet
I'm trying to draft together a simple VBA script or even =HYPERLINK string to utilize a range of cells in Excel for the To: field in an Outlook email.
I have 7 columns each with categorized for different individuals in different departments.
Instead of dealing with contact groups, I have created a spreadsheet that breaks down the departments and supplies the email address for everyone in the department.
So, what I want to do is make a link or button for each group that when it is clicked on will open a new email via outlook with all of the addresses in the appropriate range in the To: field.
Example:
Cell: A1 is the heading Cell: A2 is the link/button you would click on Range: A3:A255 would contain the addresses.
One other thing, I'm not positive if this is an issue or not...The script might need to add the customary semicolon and space after each address added to the To: field...As needed when using multiple addresses in Outlook.
Website has a URL where the last element is a number within a range (e.g. 1000 - 4000).
But the numbers are not sequential (e.g there are 100 pages, first URL ends "1001", last one ends "6000".
As shown below I set the code to cycle through every possible number:
VB:
For i = 1001 To 6000 With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://(whatever)" & i _ , Destination:="(Whichever cell the output starts in)"
Not the most constructive use of the national grid.
Is there a VBA that can check whether the URL "number" is genuine before doing anything else - and move straight to the next "i" if it doesn't exist?
Is there an easy way to identify range or cell addresses in a formula. I want to find the alphanumeric part of the address.
For example in the SUM Formula =Sum(A4:A230), I wish to separate out A4:A230 part. Or at least I want to remove the number part in the address and just want to get Sum(A:A).
Is there a function that can do this? I don't want to do this using usual string/text operations as it becomes a laborious process and has to consider all possible cases of range addresses.
I am using Excel 2010. I have been given a task at work that can save my team a lot of time if I can solve the problem. Every month, we have a spreadsheet with about 5000 rows that we have to email. In each row, there is a range that we have to email to a specific email. For example, I would have to copy and paste Range A2-R2 in the body of the email, and then email it to whatever email is in cell S2. I would then continue this for the next 5000 lines, making it a possibility that i will be sending 5000 emails manually.
I have been trying to come up with a solution through VBA that would automatically send these emails. My goal is to automatically send the Range A2-R2 to outlook email, then cell S2 into the "To" email address box, and then automatically send it. So far, i have successfully been able to send one row, but cannot figure out how to loop it for the remainder of the rows.
A couple other key points are that I have column headings as well (Range A1-R1). If possible, I want to be able to include the column headings in the email body as well. Example - first email would be range A1-R2. second email would be range A1-R1 and A3-R3, and so forth. The body of the email would also contain a standard script, such as "Please review the information below."
The goal here is to save everyone from having to send 5000+ manual emails. This would be a big boost for my team.
I have data in a spreadsheet that I would like to send to another sheet as follows:
Use the values in the range $D$2:$D$12 and $F$2:$F$12 to reference the column and row of a cell and the values in the range $G$2:$G$12 to reference a specific group of cells on another sheet into which would be inserted the value found in the range $C$2:$C$12. The best I have come up with is to place a formula in each of the cells in the second sheet that would state:
IF a value in the range $D$2:$D$12 matches the column # of the current cell AND IF the corresponding value in the range $F$2:$F$12 matches the row # of the current cell AND IF the corresponding value in the range $G$2:$G$12 matches the region of the current cell THEN the value of the current cell EQUALS the value of the corresponding cell in the range $C$2:$C$12.
I think this will work but I don’t have enough knowledge of Excel to write this formula.
Hi all, starting this as a new problem because it's so far different from what I was originally talking about; but this does relate in part to my previous thread.
Anyway. I'm trying to set an array to set Range objects so that I can define each one as a seperate With block. Here's what I "know" when starting out.
I developed the following. I keep thinking it should be multidimensional but my sleep deprived brain came up with this instead. The problem is, it's throwing a 1004 Method "Range of object '_Worksheet'" failed at the With statement.
I am trying sort a bunch of different ranges. So I am trying to use the same sort code but run an array of ranges through. This code is for only two ranges CA3:CD200 and CF3:CI200. I want to pass the ranges as variables through the sort code but I cannot get it to work.
Through VBA is it possible to give a range of cells that should be converted to array formulas? As part of my macro I am copying a sheet from one book to another. This sheet contains a lot of formulas and so as not to keep the reference of the old book I find and replace "=" with "#" and then swap them back after the move. This works fine for all normal formulas but not the Array formulas.
If someone could tell me how to give and range and convert them all to arrays or can think of a better method of moving a sheet from one book to another without the formulas keeping the references to old book.
I have created several functions in VBA that operate on arrays created by other functions in VBA, however they don't recognize cell ranges as arrays. For example, the UBound function doesn't work with a range argument within my function.
How can I get my functions to recognize cell ranges as arrays, as well as arrays that I have created directly in VBA?
I am trying to set an Array to prompt user with a series of questions and input answers into a specific row. However, I want to skip over certain columns and leave those blank. How would I modify my code to do this?
Code:
Private Sub CommandButton2_Click() Dim ArrQues() As Variant Dim ArrInput(2) As Variant Dim lngLstRow As Long Dim strPNfromSales As String ArrQues = Array("Enter the Part Number.", _ "Quantity Being Returned?", _ "What is the Reason for Rejection?") For b = LBound(ArrQues) To UBound
[Code]...
I want the first two questions to be input into Column B & C, but then the third question to be input into column I.
I have a quick question in relation to filling a range with an array.
When I use the following code:
Code: Sub justdoit() s = Array(1, 2, 3) Range("A1:C1") = s End Sub
It works fine. but when I try to populate the range in rows instead of colums, the array value stays as the first value.
Code: Sub justdoit() s = Array(1, 2, 3) Range("A1:A3") = s End Sub
I'm experimenting with arrays and would like to get better at them, but this has me stumped for the moment. How to get the values to work going down, like they do going across.
The above code is a small sample of data, I am wating to replicate with using over 80K lines and cell by cell is too slow.
Code: myRange = Range(Cells(5, 18), Cells(dRow, 30)) For Each cell In myRange If IsError(cell) Then cell = "" Else cell = Round(cell, 3) End If Next cell Range(Cells(5, 18), Cells(dRow, 30))= myRange
The problem is that the range does not change after I paste modified array back to it.
In a worksheet is a table: a b c d e f g h i j h l m n o p q r s t
I'm looking for a procedure to (with the help of an array) reproduce this table exactly under the original one with one row between them but in the folowing form:
q m i e a r n j f b s o h g c t p l h d
the procedure should be applicable on any haphazard regardless of its place on a worksheet
I would like to fullfil a range a cell with different strings. As the cells are jointed to each others, I would like to pass the string array to the range and not to pass each string to each cell, to spend time.
I mean, with the function
Dim stTxt(3) As String range(Cells(1,1),Cells(3,1)).value=stTxt()
But I have some difficulties with this function: -if the range is a column, the function works, but if the range is a row, then only the first value of the array is passed, and to all the cells of the range -I didn't manage to use this function when using
Range(Cells(x,y),Cells(z,t))
and when the range is in another worksheet. I need to use
Cells(x,y).resize(z-x,t-y)
(I'm using Excel 2000).
how I can pass correctly my string array to a row of cells? (cf 1. point).
Is there a limit to the size of an array that can be transposed in Excel '97? The following works with an array size of 1000 by 4 but not 2000 by 4
Dim arrDetail() As Variant Dim i As Long, Dim counter As Long counter = 1 For i = 1 To 2000 If a certain condition Then Redim Preserve arrDetail(1 To 4, 1 To counter) arrDetail(1, i) = "string" & Rnd(i) arrDetail(2, i) = i arrDetail(3, i) = Cells(i, 1).Address arrDetail(4, i) = IIf(i Mod 2 = 0, True, False) counter = counter + 1 End If Next i Range(Cells(1,1), Cells(counter,4)) = Application.Transpose(arrDetail)...........
I have a list of values in a column that I want to enter into an array. The values are linked from a database and as such the number of values that are in the column can change. At the moment I am populating the array using the code below. I have made the array much larger than it needs to be to accommodate for the possiblity of more data being entered into the database and my loop also accounts for this by adding extra values into the array. Just wondering if there was a more efficient way to
a) Dimension the array and b) Populate the array so that only the data I need is entered into the array
Dim ClassArray(40) As String 'Save Classes as an array For j = 0 To 39 ClassArray(j) = SourceWB. Sheets("HMI Class"). Cells(j - 2, 1).Value
I have a form that finds an loads a record in textfields and combo boxes fine. When there are more than 1 record found, the user is presented with the FindAll option. Upon clicking find all, the list box gest populated with the records found. But instead, I am getting Run-Time error:'9' Subscript out of range in the following code block. It is occurring in the block starting with myArray: - MyArray(i, 0) = fndA:
Private Sub cmbFindAll_Click() Dim MyArray(6, 12) Dim FirstAddress As String Dim strFind As String 'what to find Dim rSearch As Range 'range to search Dim fndA, fndB, fndC, fndD, fndE, fndF, fndG, fndH, fndI, fndJ, fndK, fndL As String Dim head1, head2, head3, head4, head5, head6, head7, head8, head9, head10, head11, head12 As String ' heading s for list Dim i As Integer i = 1 Set rSearch = Sheet1.Range("A6", Range("A65536").End(xlUp)) strFind = Me.txtWorkOrderNo.Value With rSearch Set c = .Find(strFind, LookIn:=xlValues) If Not c Is Nothing Then 'found it c.Select 'load the headings
