Defining A Variable As New Collection
Dec 7, 2008
I have a piece of code I am using to important data from a text file. The code works fine but I am having a hard time understanding how defining a Variable "As New Collection" works. I have tried to research on numerous websites dedicated to excel but had no luck finding any further information.
I have executed the code line-by-line over-and-over and believe what the "new collection" does is it creates some type of external database that is hidden within excel (that's just my guess). I have attached a sample file and a text file with dummy data if needed to see how the code is working.
View 4 Replies
ADVERTISEMENT
May 7, 2014
In my VBA macro, it copies data from this.workbook, and paste to another "destination" workbook. That destination workbook must be opened first before the macro is run.
Within the Macro code, I had to specify the exact name of the workbook which the data is pasted to.
The problem is that the exact name of that workbook which it paste to changes every week.
Is there any method where my macro can be changed and updated with the name of the destination workbook each week without manually opening and editing the code. The user of this.workbook is not technical enough to edit and maintain the macro each week
View 8 Replies
View Related
Oct 5, 2006
I want the below code to run from R1C10 (i.e. cell J1) to the end of the data in the row, which will vary in position, rather than having to define where the end of the row will be beforehand (i.e. R1C21).
Is this possible?
View 9 Replies
View Related
Jul 28, 2014
I sum number of articles that are coming in on one sheet and articles going out on another. The total of In - Out is a separate value. All three values are placed on a third sheet.
The following code I use does the job, but I have to use a set range in the SumIf function. I want to be able to use a variable for the lastrow of a table colum.
VB:
Private Sub ArttotKnop_Click()
Dim Klanttel As Integer
Dim Rij, vLastRow, iLastRow As Long
Dim varResult, varMin, varTotal As Variant
iLastRow = Sheets("Ingebracht").ListObjects("Ingebracht").ListRows.Count
[Code] ....
Since my table grows larger every day I want to use variables.
Where I wrote A2:A500, B2:B500, B2:B550 and D2:D550 I want to be able to replace A500 and B500 with A and B + the variable iLastRow. The same for B550 and D550 with the vLastRow variable
Tried using:
.Range("A2:A" & iLastRow)
.Range("A2", Cells(iLastRow, 1))
.Range(Cells(2, 1), Cells(iLastRow, 1))
They don't work.
View 2 Replies
View Related
Feb 17, 2014
I'm trying to make a chart viewer on the first sheet of my spreadsheet and am using this website as a guideline, [URL] However instead of the 3 charts in the example, I have 12 which makes my formula for defining a name substantially longer.
By moving my charts to the same sheet and getting rid of the INDIRECT formulas, I shortened my formula to 291 characters which is too long as the limit is 255.
=IF($G$9="Sodium",$N$34,IF($G$9="Manganese",$N$35,IF($G$9="Iron",$N$36,IF($G$9="Nitrate Nitrite",$N$37,IF($G$9="Nitrite",$N$38,IF($G$9="Nitrate",$N$39,IF($G$9="PH",$N$40,
IF($G$9="Conductivity",$N$41,IF($G$9="Turbidity",$N$42,IF($G$9="Fluoride",$N$43,IF($G$9="Selenium",$N$44,$N$45)))))))))))
Is there any way to make my formula shorter? Or to reference a cell where I place the formula?
View 5 Replies
View Related
Oct 7, 2008
If Average GMV (Cell A1) is less than 75% of Average BP (Cell B1), use Average GMV, however the Average GMV or Average BP can't be less than $600 million. Multiply the result by .01*1/3. Below are two equations I've created, however each of them only solves one part of the problem.
=IF(A1<0.75*B1,A1*0.01*1/3,B1*0.01*1/3)
=IF(B1<600000000,(600000000)*0.01*1/3,B1*0.01*1/3)
Essentially Average BP would be used, unless Average BP was something like $900,000,000 and Average GMV was around $650,000,000. At this point Average GMV is 72% of Average BP and above the $600,000,000 minimum.
View 2 Replies
View Related
Jun 16, 2009
Knowing Excel adapts its formulas upon inserting a row (i.e. a sum formula will automatically include a new row inserted in the middle of its range). Is it possible to do this with a named range. I have a pivot table that serves as a table for a vlookup, but the table sometimes changes month to month with extra accounts. I need to be able to keep the formula even though the table changes, so I figured a named range would help me out but it still doesn't accomplish what I need it to do. So basically, is there a way to make the Named Range, say AGroup (A1:C5) extend to (A1:C7) if 2 new rows are added in?
View 12 Replies
View Related
Jan 6, 2010
I trying to code a SeriesCollection in vba, below is the code I have:
' ActiveChart.Name = "Chart1"
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Name = "='Chart1'!$C$1"
ActiveChart.SeriesCollection(2).Name = "='Chart1'!$D$1"
ActiveChart.SeriesCollection(3).Name = "='Chart1'!$E$1"
ActiveChart.SeriesCollection(4).Name = "='Chart1'!$F$1"
ActiveChart.SeriesCollection(5).Name = "='Chart1'!$G$1"
ActiveChart.SeriesCollection(6).Name = "='Chart1'!$H$1"
ActiveChart.SeriesCollection(7).Name = "='Chart1'!$I$1"
ActiveChart.SeriesCollection(8).Name = "='Chart1'!$J$1"
ActiveChart.SeriesCollection(9).Name = "='Chart1'!$K$1"
ActiveChart.SeriesCollection(10).Name = "='Chart1'!$L$1"
ActiveChart.SeriesCollection(11).Name = "='Chart1'!$M$1"
ActiveChart.SeriesCollection(12).Name = "='Chart1'!$N$1"
However it it giving the error "Object denfined or Appication denfined"
View 9 Replies
View Related
Jun 20, 2006
I am using the following code to determine whether a given range is the linked cell for a Combo Box by looping through the shapes collection.
Function LocateFormControl(OverRange As Range) As Shape
Dim objTemp As Shape
For Each objTemp In OverRange.Parent.Shapes
If Left(objTemp.name, 6) = "Drop D" Then
If WorksheetFunction.Substitute(objTemp.ControlFormat.linkedcell, "$", "") = WorksheetFunction.Substitute(OverRange.Address, "$", "") Then
Set LocateFormControl = objTemp
Exit Function
End If
End If
Next
Set LocateFormControl = Nothing
End Function
However, when I use this code on big worksheets with many other shape objects (such as Comments), the program runs very slowly. Is there a ComboBoxes Collection that I could use to avoid looping through all shapes on the worksheet, or is there a different, faster way to run this code?
View 2 Replies
View Related
Apr 23, 2009
I have a sheet containing several charts. I have a script which I found on this site that helps me create dynamic min and max values for the axis.
Since I have several charts it seems that I need to define which chart that I want to change the axis.
This causes an error.
View 9 Replies
View Related
Aug 7, 2009
I have a form in excel that people are going to populate, then a button which runs a macro which copies all the cells out into a new line in a different worksheet inside the spreadsheet. im going to use that sheet to formulate graphs etc, the macro works however it overwrites previous data, im assuming i need some function to calculate which is the next empty cell then use this to to copy the data to
View 12 Replies
View Related
Jan 22, 2010
I would appreciate knowing how to fix this formula to do more than less than/more than answers. How do I have the following answers return for these birth year ranges? I have attached a sample file, with the formula in place. Thank you so much for any help!
1974-1991 = amateur 18-35
1900-1973 = amateur 36 and up
1992-1994 = junior 15-17
1995-2005 = junior 14 and under
blanks or zeros = unknown
View 8 Replies
View Related
May 21, 2014
I am starting to write a macro and first thing i Need to define is all the columns that I need in my report. I started out by defining an array and this is what I have so far.
I need that every time I run this macro it should vlookup the data from cleantxt files that are saved in my c:autofile ReportsFC Banded Summary and essentially create separate excel files for different buyer names and save those sheets to my C:Banded Summaries with the name as Bandedsummary(todays Date)_BuyerName
This is going to be the bones of it then I have to figure out how to vlookup data for all the columns and do sumifs and format it.
This is what I have so far:
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
[Code]....
View 4 Replies
View Related
Nov 6, 2008
Defining Cell Name when Dynamic?. Here is whare I am.
View 3 Replies
View Related
Jan 20, 2009
I tried to run this code and encountered an error telling me that N should be an object.
N has to be a variable number representing the amount of sheets in the workbook -2.
Does anyone know how this is written?
View 6 Replies
View Related
Dec 5, 2009
I've got a spreadsheet that could have 1 of 3 possible print ranges:
PR1 = B2:K67
PR2 = B2:K132
PR3 = B2:K197
If there is a value greater than "0" in Cell B68, the print area would be PR2, but if there is a value greater than "0" in Cell B133, the print area would be PR3, otherwise it would be PR1.
These print ranges are such that the page break would would be below B67 and B132, but I'm concerned that I may need to set these page breaks in the Macro.
I'm not sure how to use the IF, THEN, ELSE, in the Macro to test the value of the cell and then set the print area.
View 3 Replies
View Related
Oct 6, 2008
I'm trying to make ranges in a SumProduct formula dynamic, but getting errors #N/A. I think this is because the top two rows are headers, throwing off the range count.
Q. How do I adjust the range definition to compensate., e.g.
Range =Offset(Sheet1!$D$3,0,0,COUNT(Sheet1!$D:$D),1) where column D is numeric, and
Range = Offset(Sheet1!$T$3,0,0,Match("*",Sheet1!$T:$T,-1),1) where column T is text?
I had assumed that the offset value 3 would do this, but I suspect the functions count and match are not doing so.
View 8 Replies
View Related
Aug 23, 2007
once bTest is declared as a Boolean, it becomes equal to FALSE (I checked this after much head scratching). How does one assign a value to a boolean variable only after you make your test.
Sub foo()
Dim bTest As Boolean
If Range("a20").Value = False Then bTest = False '
View 9 Replies
View Related
Sep 15, 2008
I'm trying to develop a work spreadsheet template that is to be summarised every fortnight. Our fortnights here go Thurs-Thurs. So I figured I would start with a base date (thurs - Sept 4th) and then have the spreadsheet display the true fortnight ending upon loading. Ultimately this fortnight ending will be used to copy and paste the template information into a summary spreadsheet but for now I am trying to get this intial part working. It's linked to a command button and my attempt at code is below:
Private Sub CommandButton1_Click()
Dim Base As Date
Dim Now As Date
Dim Delta As Integer
Dim No_of_Fortnights!
Dim Rounded As Integer
Dim Fract!
Dim Fort_week_end As Date
(Note the following 8 lines are not the problem, they just open the main sheet. The problem is the eights lines starting with the word 'set')
Sheets("Technical Inquiry Form").Activate
Sheets("Technical Inquiry Form").Range("b3").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Sheets("Technical Inquiry Form").Range("b3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Technical Inquiry Form").Activate
View 9 Replies
View Related
Nov 14, 2008
If I have a column of data with a defined name is there an easy way to update this as data is added? Can this be done when you open the workbook and without? I'd assume an open event could do it but I'm having problems finding a simle solution.
View 9 Replies
View Related
Jun 11, 2009
I'm having an issue trying to locate a certain value in a cell.
If it is a number, say 50, then my code works, if its a word though
then it doesnt work. How can I get this to search with a list of words rather then numbers?
Sub alpha()
Dim cell As Range
For Each cell In Range("A2", ["N27"])
If cell.Value = Beff Then cell.Interior.ColorIndex = 6 'doesnt work
If cell.Value = 50 Then cell.Interior.ColorIndex = 6 'does work
Next cell
Range("A2", ["N27"]).Font.Bold = True
Range("A2").CurrentRegion.Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End Sub
View 9 Replies
View Related
Nov 22, 2009
I have to enter a 2D array by hand into code because I am doing it in PowerPoint VBA, but I can't find out how to enter a giant array manually.
I know I could do it like this:
MyArray(0,0) = 1
MyArray(1,0) = 2
MyArray(2,0) = 3
...
MyArray(99,99) = 10000
But obviously that would be a giant mess.
Couldn't I just define it like:
{0,1,2,3,4,5,6,7,8,9,...,99,100;
101,102,103,...,199,200;
...10000}
View 9 Replies
View Related
May 25, 2006
What the code does is that it runs in a Master workbook, and searches for XLS files in a defined folder. It opens a file, count, closes the workbook and takes the tallied number of sheets and places it in the master file, column A being File Names and column B as Number of sheets. What I need is for the code to take the file name from column A, run it and then place sheet counts in Column B and then move down the columns until all the counting is done. Currently I am only able to define one row using the range function and i'm at a loss.
'Get file names and enter into Master
Public Sub ShowFolderList()
Dim wb As Workbook
Dim ws As Worksheet
ChDrive "C"
ChDir "C:Documents and SettingsTest Folder"
outrow = 2
Filess = Dir("*.xls")
While Not Filess = ""
Cells(outrow, 1).Value = Filess
outrow = outrow + 1
Filess = Dir()
Wend.......................
View 2 Replies
View Related
Feb 13, 2007
I have created some code of which this is an extract
Dim i As Variant
i = Array(37, 38, 41, 42)
For Each i In i
'Some Code
Next i
the routine works fine when the i variable is hard coded, but once the above is included it fails (Error 10 This array is fixed or temporarily locked).
View 3 Replies
View Related
May 26, 2009
Is there a way to remove a value from a collection after I've added it.
I have simplified the code I'm using to highlight what I'm trying to do.
Basically I'm adding 10 keys to a collection and want to remove the last one. There is a reason for this.
View 3 Replies
View Related
Oct 5, 2006
Im workin with a spreadsheet.. At the end of this script you see how i am putting "a" in R11C33, well I want to put the contents of my workbook C3 instead. I just dont know how to do it. After it does that I need it to move on to cell C4 and put it also in r11c33...
View 4 Replies
View Related
Oct 27, 2006
I have a large table (20,000 plus entries) and I need a way to pull out data that matches criteria in buckets.
For example:
April data (column B) that is from 0 to 30 (column C)
April data (column B) that is from 31 to 45 (column C)
... 46 to 60
... 61 to 75
... 75 to infiniti
Then do it again for May, June, July, etc. I have used DCOUNTs to pull this type of data, but it's a bit cumbersome. Any other ways to pull this type of data from the table?
View 3 Replies
View Related
Jan 28, 2007
I am attempting to create a double-entry accounting system and am having trouble with my Transaction class (clsTransaction). As each transaction will consist of at least two entries, I have included a Collection in clsTransaction that will accept Entry objects (clsEntry) as items. The Add method appears to go ahead without a hitch, but when I output the values from the Entries I get the same values for all items in the collection. Here is the code I've been working on.
The Transaction Class
clsTransaction - (Class Module)
Option Explicit
Dim mcolEntries As Collection
Dim msDescription As String
Private Sub Class_Initialize()
Set mcolEntries = New Collection
End Sub
Public Sub Add(eItem As clsEntry)
mcolEntries.Add eItem
End Sub........................
View 3 Replies
View Related
May 2, 2007
I have a userform with three checkboxes on it. In the manuals I have read (and websites I am learning from) I have encountered articles that give me the impression that it is possible to create a collection of almost any object, and then quickly return properties about the collection as a whole. So, I am wondering, is it possible (or even appropriate) to group the checkboxes on this userform into a collection, and then, with a few lines of code, see if any of them are checked? If this is not possible, or efficient, is there another way to do it? I'll admit that three IF statements are not all that hard to enter, but I know the day is coming when I will make userforms with many more checkboxes in them.
View 4 Replies
View Related
May 31, 2007
I have a routine that creates a collection of textboxes to simplify adjusting their positions: ...
View 9 Replies
View Related
