I've never had to venture into Arrays as there was no need.
I'm now faced with Arrays.
Generally I'm able to figure things out, in this case I'm stumped.
What I have is a Constant Array, that output the amount of Rows the Array displayed in.
I know the information is there, what I don't know is how to extend the rows.
ie it Outputs 5 Rows right now, with a top row header.
What I want is 10 rows with a header.
Is it possible to use a contstant array in Excel VBA? I have tried several differnt ways to declare it, but the VBA editor keeps yelling at me. The following did not work:
View 2 Replies View RelatedIs it possible to make an named array constant from named constants to use in a lookup?
Eg
I have defined the following constants in Names:
Text1 = "AAAAAAAAAA"
Text2 = "BBBBBBBBBBB"
Num1 = "1234567890"
Num2 = "0987654321"
And would like to make the following array constant in Names:
Arr = {Text1,Num1;Text2,Num2}
However, it does not work.
I am using Excel 2007 by the way and the above values are made up.
I need to convert a cell that has a list of values A1 = 1,2,3. To a range expression {1;2;3}. I want to do this with a formula and not VBA. Example: Cell D1 has the formula = SUMPRODUCT(COUNTIF(C30:C31,A30:A32),B30:B32). I want to put the list range from column C into one cell.
ABCD
110140
2203
3305
450
560
I have the following array function that I am trying to get to work properly:
ActiveCell.FormulaArray = "=SUM(IF(NCR!O2:O100=39326,NCR!Q2:Q100,0))"
39326 is the value of 9/1/2007, and this formula works properly.
I am looking for a way to use this formula but replace 39326 with whatever date is in the first row of the same column as the active cell when it is run.
That is, if the macro was run with cell B8 as the active cell, "39326" would be replaced with whatever value was in cell B1.
I try to understand what the syntax is to create and use an array named constant which would be composed of other constant.
Example :
Constant called AvgZone1 which is =AVERAGE(zone1)
Constant called AvgZone2 which is =AVERAGE(zone2)
and then create a named array constant based on the names : =AvgZone1,AvgZone2
is not working. You cannot for example do a SUM().
I have a userForm (Form1) that contains a persons name that I would like to reference in a separate UserForm (Form2). In the separate UserForm (Form2) I need to reference this persons name many times, so I was wondering if there was a was to declare this name in the separate UserForm (Form2) as a constant. Only thing is that a constant, to the best of my knowledge, must be an expression and not a variable. Mainly, I'm trying to avoid declaring the myName variable in each Sub within Form2, which it will be needed for a ton of Sub's.
Code for Form2: Const myName As String = Form1.txtName.Value
When i press the corner of a cell and drag it (example =D2) it will only go higher in the number (drag it 3 rows down it just goes D2, D3, D4, D5, D6).
Can I drag it down but make it stick to the 2 and instead go E2, F2, G2 etc?
I am editing a fairly sizeable name, address list and I would like the row highlight bar to extend across the page. For instance, when I click on the row number the highlight bar extends across the row, but when I hit return the highlight disappears and I have to click row by row. I would like the highlight bar to extend across a row and then move up or down with the arrow keys.
View 5 Replies View RelatedRight now I have a spreadsheet where I have a person enter in 4 differant values in 4 adjacent cells of a row, then in another 4 adjacent cells of the same row calculations are done with respect to the 4 rows of entered information then displayed. Is there a more effective way in placing and copying these formulas in the last 4 adjacent cells rather then just copying the formula into the first 3000 or so rows of cells?
I say 3000 because each project is differant and will require a differant amount of rows, but by copying the formula, even little IF statements, slows down the program and makes printing a hassle.
Is there a way to make the dropdown list come up longer than standard?
View 4 Replies View RelatedI have a worksheet that was originally saved as a xls (2003) file. I have resaved it as a xlsx (2007) file, but the max rows are still at 65,536. I am trying to combine over 100,000 records into one sheet. The only way I have been able to create a excel document with more than 65,536 rows is to export my records from an Access file.
View 2 Replies View RelatedI am using Excel 2003 SP3. How does one extend the the number of values in a list box and retain the same name to be used in the Data / Validation / Settings / Source entry? The only method I see is to extend the list of allowable values and give the longer list a new Name.
View 2 Replies View RelatedI was trying to set yp a formula that will automaticaly extend a series. I mean will add a sequential number to the one above. If I have in cell A1: CD001. I want to place a formula that will show me: CD002 in cell A2. I tried n function but it only converts it to the number and comes uout as 0. I don't know which one to use if there is any. Answer to the first question I will probably be asked: No I cannot drag it down with a mouse as I will place it in the conditional function afterall.
View 5 Replies View RelatedIf my cursor is in A1 and I want to select all non-empty cells in that column, what is the VBA code that will do that?
(I'm a beginner and have tried recording a macro and inspecting the code, but that always gives me a specific number of cells to select, while I want it to vary by the number of cells that have values. )
I get 40~100 page PDFs of purchase orders every week. Each page has one part number and its open order and forecast information. Some pages have a few line of information, some have many lines.
I convert this file into a txt file.
I then create a macro to import it as space delimited and format it.
What I need to do it get rid of the header/footer information on EVERY page.
When the txt file is created it doesn't have any page marks.
The header has "ABC Widgets" as the customer name and then 10 rows of unnecessary information. If I do a Find ALL for "ABC Widgets" and select all, it will delete that header line at every instance, but the leftover selected cells then start not-lining up (meaning I can't just hit "delete row" 10 times and have it pick the right row--the rows don't line up any more and each instance is -1 row offset.)
Is there any way to extend the selection of non-contiguous rows (starting with ABC Widgets) down by 10 so I can delete them?
i need to extend the analysis but i have no idea what the 2 modules below do.
Sub mmm()
zonecode = Worksheets("sheet1"). Range("a65536").End(xlUp).Row
etypes = Worksheets("sheet1").Range("iv1").End(xlToLeft).Column
nextline = 2
For i = 2 To zonecodes
zcode = Worksheets("sheet1").Cells(i, 1).Value
For j = 2 To etypes
etype = Worksheets("sheet1").Cells(1, j).Value
enbr = Worksheets("sheet1").Cells(i, j).Value
Worksheets("sheet2").Cells(nextline, 1).Value = zcode
Worksheets("sheet2").Cells(nextline, 2).Value = etype
Worksheets("sheet2").Cells(nextline, 3).Value = enbr
nextline = nextline + 1
Next j
Next i
End Sub....................
I have a sheet (Sheet 3) that is pre-populated from another sheet (Sheet 1), and the user is allowed to update the numbers (for forecasting).
I have a column ( Total Hours) that I use to total the new numbers in the row.
If the user inserts a row, the Total Hours formula does not follow. This is the Total Hours formula that I am using SUM Formula
(=IF(SUM($G30:$AP30)=0,"",SUM($G30:$AP30))
How do I (Can I) get the formula to cascade into the new row.
I had asked about automatically naming regions and this is an extension of that post because it's closed. The code we ended up with to name the region is:
Dim sNm As String, sRT As String
If Intersect(Target, Rows(1)) Is Nothing Then Exit Sub '------------------->
If Target.Count > 1 Then Exit Sub '---------------------------------------->
sNm = Replace(Trim(Target), " ", "_")
sRT = "=offset(" _
& Target.Address _
& ", 1, 0, counta(" _
& Cells(2, Target.Column).Resize(Rows.Count - Target.Row).Address & ") )"
ThisWorkbook.Names.Add Name:=sNm, RefersTo:=sRT
My problem now is that I need to have the regions be the length of the longest column. I've tried using a few different ways using the worksheet range but I can't seem to get it to work.
I have two data tables (Table1 and Table2) on two different sheets (Sheet 1 and Sheet 2). In Col1 of Table2, I have "copied" the values of Col1 in Table1 by using a simple equal (=) formula. Secondly, I have an autofilter on Table2 and a macro that automatically updates the filter when the sheet (Sheet 2) is selected.
My problem is that I would like Table2 to be more dyanmic, i.e. I don't want to change the range of the Table2 each time I add, or subtract, a value from Table1.
Could a OFFSET formula be employed in any way?
I have attached an example file.
The macro is:
[Code] .....
ExpandTableExample.xlsm
So I found a great piece of code which calculates relative strength index. However, I've been trying to edit it to make it a bit more dynamic and I'm really struggling to get it to work.
Right now the initial code is:
Code:
Function RSI(MyCells As Range)
Dim up_day, down_day
Dim average_up, average_down
Dim RS, cellcount As Long
Dim cll As Rangeups = 0up_day = 0downs = 0down_day = 0cellcount = 0For Each cll In MyCells
[Code] ..........
Basically I tried to amend this as follows:
Code:
Function RSE(MyCells As Range, Length As Double)Dim up_day, down_day, ups, downs
Dim average_up, average_down
Dim rs, cellcount, rangecount As Long
Dim cll As Range
ups = 0
[Code] ..........
Basically i wanted it to look at the numbers in the range and then error check to see if it was a number... if it was a number great. If not then the length of numbers it should calculate across should increase by 1 as it has to include another number to calc across (hope this makes sense). however, i just cant get it to work at all. My code doesn't seem to make any difference. And it also raises the issue of the fact that the intial value for length also needs to be stored to do the final calc.
I found the following code here.
For data entered into column A, it copies the formulas from columns B:E in the row above to the current row.
It works great except fot the first row (A9) where it copies the header row (B8:E8).
How can I get it to not copy when data is entered into A9?
Private Sub Worksheet_Change(ByVal Target As Excel. Range)
Dim c As Range, i As Long
On Error Resume Next
Set c = Intersect(Target, Columns(1))
If c Is Nothing Then Exit Sub
If IsEmpty(c.Offset(-1, 0)) Or Not IsEmpty(c.Offset(1, 0)) Then Exit Sub
i = c.Row
Application.EnableEvents = False
Range("B" & i - 1 & ":E" & i - 1).Copy Range("B" & i & ":E" & i)
Application.EnableEvents = True
On Error Goto 0
End Sub
I have a pie chart on a pre-defined table with 15 categories where the data will populate over time (presently only 3 are populated) in descending order. As some categories will be blank/zero for a while, I'm trying to find a way to avoid showing the blanks/zeros on the pie chart. As I am trying to display the legend on the pie chart segment, any zeros cause the remaining labels to appear grouped together and rather messy. I would therefore like to hide the zero data from the pie chart so the labels do not appear, or to automatically extend the range to only include all non-zero values.
I found the following formula which, when used as a named range, automatically extends a list for data validation:
[Code] ....
I don't know whether there's a similar formula or whether a few lines of VBA are required to run whenever the chart or data worksheet are selected..?
I have a spreadsheet that requires a formula in column "e". How can I automaticlly extend the formula each time data is entered in column "d" of the next row.
View 7 Replies View RelatedI created the following macro (using the recorder), and now need to modify it so that the formula extends to the end of the data (and no further). I would also like to include a total at the end.
Sub ExcessUsage2()
' ExcessUsage2 Macro
Rows("39:250").Select
With Selection.Font
.Size = 8
.Strikethrough = False
[Code0 ....
I am after an automatic formula or function which calculates values for long lists instead of me dragging down the formula all the way to the end of the document.
Hence, I have a long list of data in columns A, B&C and I want the formula in column D to automatically be calculated all the way down when the list stops.
1st post so hope that title isn't too vague.
Using VBA, I have a macro that will find a column based on a week number and add in a new column.
It will then offset the ActiveCell down one.
I now need the macro to SUM all values in that row to the left of the ActiveCell.
My original thought was to use:
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlToLeft)).Select
Unfortunately, there are gaps in the data field, blank cells that should count as zero value.
How can I highlight all cells to the left, from whichever column the active cell is in, through to column B?
I have a delete button on a protected sheet to allow a user to delete selected rows (unprotects, deletes, protects again...).
I need to add a check to make sure they do not delete any row greater than 152. How can I check if a row greater than 152 is in their range of selected rows?
I am using office 2007 and here is the problem I am facing. I am using a formula and it is based on two columns data. The formula result is at C20:C2400, while the two data columns are at A20:A2400 and B20:B2400. I add one more row of data at A2401 and B2401, I expect the formula result would auto extend to C2401 but it doesn't, nothing happen.
I check that I need to turn on the auto extend check box in option, I check and it is already on.
i have problem with a long code.
So I divided that code in 4 parts.
So I have
module1 -> Sub Macro1()
module2 -> Sub Macro2()
module3 -> Sub Macro3()
module4 -> Sub Macro4()
I have declared
Option Explicit
Dim mdNextTime1 As Double
Dim myEnter As Integer
'Dim myK1 As Integer
'Dim myK2 As Integer
'Dim myK3 As Integer
Dim myK4 As Integer
Const myC As Integer = 82
Dim Lastrow As Integer
Dim myActualRow As Integer
all my sub have the following structure: