Im having slow performance reading a CSV file into 34000 sheet named ranges. Currently it takes about 8 minutes to read 34,000 records. After stepping through the code Ive realized there are two bottle necks.
1)finding which sheet that the range name resides see getRangeAddress() and
2) actually setting the value to the named range i.e. Range(ra) = dprecord(1)
The CSV file looks like this: <range name>,<data value>
Performanace actually seem to slow as the macro runs.
Public Sub readDatapoints()
'This macro will read in the a comma seperated value (CSV) file of datapoints.
Dim sFile As String
Dim currentLine As String
Dim delimit As String
Dim counter As Integer
Dim ra As String
Dim fs As Object
Dim ts As Object
Dim oldStatusBar As Boolean
delimit = ","
I have a relatively small Excel workbook that is using numerous lookup and Match function combinations. I also have a number of dynamic ranges defined and a few UDFs.
Problem is I am encountering some fairly severe performance issues. Changing a single value on a sheet is taking ages.
I suspect it may be due to the workbook dynamic ranges recalculating. Is there a limit to the amount of dynamic ranges in a workbook? And when do they get recalculated? Could it be something to do with sequencing of the calculations?
I'm going to attach a file I'm working with so you can see my issue direction but here is the background:
- file you see is an excel export from another software. This is as clean as it gets.
- what I am trying to accomplish is take the data for each person listed and movie it to it's own sheet via a macro. Creating the sheet name based on the name of the person. I've completed that task before with some help here, but never with data formatted like this.
- does anyone have any options they can think of? I've tried working with the two cells that make up the page number and basicly the move off that but can't seem to get that to work.
- would anyone suggest any formatting changes (while keeping all data integrity) before trying a move?
In creating a chart that calculates home prices I need to be able to just enter "Good", "Fixer", "Remodeled", etc...and have that automatically use one of the numbers that I have set and add it to my total. I have created a box with variables for "Good", "Fixer", "Remodeled", etc...How do I make it so I can put any of those variables into the condition of the house row and make it add the dollar amount specified into the totals box? This is the very simple code that I am using to find the totals so far: =SUM(C5:J5)
Here's what I need:
Also, the plug in values in the first picture WILL CHANGE and I don't want to have to change the formula. So I need something to point to those cells.
How do you set the range with whats below? Right now its looking at 4,2 only and if extend to look all the way to 19,2 it will just copy the whole screen. I need it to look at 4,2 and then move to 5,2 and then so on once it has copied all that information to the sheet ...
This should be easy ... can't figure it out. If I want a formula that wants to perform an action (NPV, but I don't think that matters) on the cell directly above and the next "X" (say 20 cells/years of cash flow) to the right of it, how can I set it up so that I can copy the formula across as well as change "X".
(Obviously, it's easy to select a 20 cell range and copy it across ... but what if I want the length of the range to by dynamic (ie I want to switch to a 40 year NPV instead of a 20 year)?)
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?
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
I want the first two questions to be input into Column B & C, but then the third question to be input into column I.
Im trying to set a range using a named range and dont know the best way to do this. so far ive got
Set rDataRange = Range(Offset("datastart",1, 0) & ":" & Offset("datastart",LastRow, 0))
datastart is the named range in the sheet and im trying to make a new range that starts 1 row below where datastart is and end where lastrow is. not sure where i messed up on this but i get errors when i try to run this
I'm having trouble setting the Range "UtilizationRange" using a variable, "CurrentRange" to store that range. I get the error "Run-Time error 1004: Method 'Range' of object'_Global' failed".The rest of my code works when I set the utilization range using the actual range for my purposes, but the problem is the range will change each month and I don't want to change it manually each time. There's a cell in the excel sheet this refers to (AG3) that holds the range value, which in this case is [N7:N75], and I would just like to return that value to the variable "CurrentRange" and set UtilizationRange equal to that.
The value is returned to CurrentRange, but it's the last line that's giving me the problem.
Sub RangeTest()Dim UtilizationRange As Range, Cell As RangeDim CurrentRange As String CurrentRange = Range("AG3").Value MsgBox (CurrentRange) 'This returns the value [N7:N75], which is what I want Set UtilizationRange = Range("CurrentRange")End Sub
I have a certain number of accounts for opening and close dates. What I wish to acheive is to fill a Range of date from the opening to the closing (or up to 200606 YYYY-MM) in another spreadsheet with the account number corresponding to that date next to it.
The VBA I have right now manages to create the list of accounts but up till 2006. It isnt taking into consideration the closing dates of accounts.
So For example What I want is to set up inside of the loop that to continue to do the loop until it reaches the closing date by YYYY-MM so if the clossing date is 200504 I will have data filled up to that date, and if the closing date is "" then fill the dates up til 200606. Ive tried out a number of ways of inserting the if statement but apparently the string create a problem.
Here is my current VBA and a Sample Sheet of what it looks like....
i have compiled a multipage using some borrowed code and some code i have written myself. most of it works, but i have a problem populating listbox2.the error is in Private subCmbFindAllJobNo_Click(). i have put h1 tags around the line of code which shows the error when i debug. this code works ok as a stand alone, so i suspect i have done something wrong in the userform initialise.
Option Explicit Dim rng As Range Const FirstRow As Long = 2 Dim r As Long Dim ans As Variant Dim MyArray(100, 4)
I've got two sheet's in a workbook. The first contains data and is setup with dates in column A, day of the week in column B, and values going across by day in column's C through H. As an example it looks as follows:
I'm trying to do a simple loop which creates charts based on an ID number. I recorded a macro and has tried to modify it but am having trouble defining the correct reange when settign the data source. Here is my