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.
Dim TotalRange as Range Dim Startrow as Long Dim TruckArray as Variant Dim j as Long Set TotalRange = GetRange(Startrow) 'GetRange is a function that successfully returns a range object based on a starting row For j = 1 TruckArray(j) = TotalTruckRange.Value
I keep getting an error on the last line of the code.
I can't quite seem to figure out the syntax for pulling a Range variable out of an array of type variant. I always seem to receive the error message "Object variable or With block not set", an example of my code as follows:
Dim currentRange As Range For i = 2 To UBound(myArray) currentRange = myArray(i) ' insert code here Next
I have also tried:
Dim currentRange As Range For i = 2 To UBound(myArray) Set currentRange = myArray(i) ' insert code here Next
Which results in the error "Object required". What is the correct syntax?
So I'm creating a grid worksheet for engineering calculations and I have a couple questions about the best way to do it. I've been messing with excel for my calcs for about a decade now, and I every once in a while I try and improve them.
First: I will have several input areas that will either be colored text or shaded background (either works for me). I don't want these 'input required' objects to print as color, just black. But I want my logo at the sheet top to print as color. I've only found ways to not print any color. Can I print the logo as color and the 'input required' stuff as black?
Second: When I do calcs by hand, I write them out on 10x10 grid paper. Each 10x10 grid is one inch. In the past I've created this grid out of the cells, which works. I frequently need to change formulas around though, and each time I do this, I end up needing to mess with the grid cells also. Is there a way to create the grid and have it in the background so it doesn't need to be adjusted each time I change formulas? I wan't the grids to print, and also want to see them on the screen, as I sometimes draw simple objects along with the formulas.
I'm trying to remove gif, jpg, and xls objects from a specific range and I can't seem to figure out how to do it. I can remove all objects from the sheet or only the gif and jpg files from a range. Here is the code I've been playing with:
Sub Clear_Sigs() Dim Sh As Shape With Worksheets("Sheet1") For Each Sh In .Shapes If Not Application.Intersect(Sh.TopLeftCell, .Range("A26:E32")) Is Nothing Then If Sh.Type = msoPicture Then Sh.Delete End If Next Sh End With End Sub
I know this code says to delete only pictures and I've tried tweeking it to do all objects but it doesn't work for just the range I want. Any help is appreciated.
Is there code to write that will delete all objects in a range of cells? I have about 5 buttons at the top of my sheet and I don't want to erase them when I erase objects.
I read and used the Find_Range custom function provided by Aaron Blood. It's a great function, for which I have many uses, but, as I currently have it set up in conjunction with a userform, it doesn't work fully until I use it twice in a row. The first time it's used to return more than one row, it seems to stop short and only display a few of the appropriate rows. Not until it's used twice in a row does it display all the rows containing that product. What do I need to do to have it work fully each time?
I have a large worksheet from which I want to extract only the rows which contain a certain product, selected by a combobox, and paste the rows on another worksheet. I have a userform set up with an oversized listbox which displays the contents of the data worksheet. Below that I have a 'products' combobox, and a button to initiate the Find_Range. Another oversized listbox displays the results.
Here's the function contained in a module:
Function Find_Range(Find_Item As Variant, _ Search_Range As Range, _ Optional LookIn As XlFindLookIn = xlValues, _ Optional LookAt As XlLookAt = xlPart, _ Optional MatchCase As Boolean = False) As Range Dim c As Range, FirstAddress As String With Search_Range Set c = . Find( _ What:=Find_Item, _ LookIn:=LookIn, _ LookAt:=LookAt, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=MatchCase, _ SearchFormat:=False) 'Delete this term for XL2000 and earlier If Not c Is Nothing Then Set Find_Range = c FirstAddress = c.Address................................
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.
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
I want to use VBA to update a drawing object's color, such as a circle.
For example, pretend I am counting sheep and I need to know when I have reached the maximum count by the visual aide of a stoplight. The script in my head reads: If the max. count of the sheep is less than 5, then the stoplight is green. If the max. count of the sheep is equal to 5, then the stoplight is yellow. If the max. count of the sheep is greater than 5, then the stoplight is red.
Is there a way to do such a thing on excel and with VBA?
I created four Ranges called "Range1", "Range2", "Range3" and "Range4", and wrote short program, that fills them:
Dim i As Integer For i = 1 To 4 Range("Range" & i).value = 1 Next i
Is it possible to do similiar thing with for example ComboBox? Let's say that i have comboBoxes named "cb1", "cb2", "cb3" and "cb4" and want to disable them.
