Cells.find Function Slower Than Expected Across Several Worksheets
Jan 24, 2007
I wrote a custom find function to search for a list of numbers across a bunch of worksheets (6 in total). A cell reference is sent to the function and it basically returns the worksheet name and address for the first occurence, or nothing if not found.
Now I realise that the number of cells that need to be checked is fairly severe across 6 worksheets (finding 65 numbers * 65536 * 256), but this still takes about 1 minute to execute... is that about right? Code was hacked together quickly and is nothing special...
Public Function My_Find(Optional my_range As Range) As String
Dim ws As Worksheet
Dim my_cell As Variant
Dim result As Variant
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
View 3 Replies
ADVERTISEMENT
Mar 11, 2013
I wrote the following:
for i = 1 to 10000
if cells(1,1).value=" " then
row(i ":" & i).select
selection.entirerow.delete
end if
next i
At first it runs very fast but getting slower and slower. What could be the cause and how to fix it?
View 5 Replies
View Related
Jan 7, 2008
I'm new to the DMIN function and I'm having a problem.
Cell A2: Date (rest of column is date format) (heading for the column)
Cell A2: Credit (rest of column is acctg. format) (heading for the column)
Cell A2: Debit (rest of column is acctg. format) (heading for the column)
Cell A2: Balance (rest of column is, equals balance field from previous row + credit - debit for current row, acctg. format) (heading for the column)
Column E: Notes (rest of column is generic text) (heading for the column)
Cell K1: Date (the text "Date")
Cell K2: > TODAY() (the text "> TODAY()") -- maybe this should be ="> TODAY()"?
Cell H2: =DMIN(A2:D1000,"Date",K1:K2)
I'm trying to get the minimum balance for the Balance column where the date is greater than today (lowest balance that I have in the future, which helps me see if I will be overdrafting).
I'm getting a value of 0 in H2. I have also tried =DMIN(A2:D1000,1,K1:K2) and get the same result.
View 9 Replies
View Related
Jul 27, 2006
Need the right direction to stop the error 'Compile Error Expected Function or Variable' appearing. I have both of the following codes in a module. The AddNewTenancy works perfectly but the EditTenancy comes up with the following error.
Both the Userforms exist so I know it's not that.
Sub EditTenancy()
EditTenancy.Show
End Sub
Sub AddNewTenancy()
NewTenancy.Show
End Sub
View 2 Replies
View Related
Dec 18, 2006
i'm trying to call a function from another one, i'm getting this error 'Compiler error: = expected' but i don't know the reason, the functions simply take some values an store them in an here is the
Dim productos(19, 3) As String
Sub agregarProducto(ByVal descripcion As String, ByVal modelo As String, _
ByVal precio As String, ByVal unidad As String)
Dim r As Integer
For r = 0 To 19
If productos(r, 0) = "" Then
productos(r, 0) = descripcion
productos(r, 1) = modelo
productos(r, 2) = precio
productos(r, 3) = unidad
End If
Next
End Sub
Sub agregarProductoTelas()
Dim descripcion, modelo, precio, unidad As String
If Selection.Column = 1 Then
descripcion = Selection. Offset(0, 0).Value
modelo = Selection.Offset(0, 0).Value
precio = Selection.Offset(0, 3).Value
unidad = Selection.Offset(0, 2).Value
agregarProducto(descripcion, modelo, precio, unidad) 'error happens right here
MsgBox (descripcion)...
View 3 Replies
View Related
Aug 8, 2014
I have a VBA shell script that worked just fine on another computer, but now it won't run.
The code that won't execute is:
[Code] ........
View 12 Replies
View Related
Apr 9, 2007
Most excel/vba books say not to use the sumif function.
I have a very large workbook (9meg) and cant use VBA code as it slows down the interation calculations required.
I have used the sumif function to find and collate unqiue data on different worksheets.
Is there another function that i should be using?
View 9 Replies
View Related
Feb 7, 2007
I am trying to run create a simple macro that copies and paste special values - something I have done 100's of times but for some reason I keep getting an error message - even though I recorded the macro and didnt write it by hand - see below:
Sub Macro6()
Cells.Select
selection.Copy
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
For which I get 'Compile Error - Expected Function or Variable'
View 6 Replies
View Related
Apr 20, 2007
I have set up a worksheet to demonstrate how arrays work--and find that I need to learn more about the topic myself! The code below reads data from Column A into an array, sorts it, and then writes the sorted data to Column B. In the example I have created there are 11 data values in Column A. When I break at the indicated line of code, I find that LBound(sourceData) = 1 and UBound(sourceData) = 11, as expected. However, at the indicated line of of code, when i = 1, the reference to sourceData(i) results in a subscript error.
Public Sub AddSortedColumn()
Dim sourceData() As Variant
sourceData = Range("A1:A" & Range("A65536").End(xlUp).Row)
Dim swap As Boolean
Dim i As Integer
Dim save As Variant
swap = True
Do While swap
swap = False
For i = LBound(sourceData) To UBound(sourceData) - 1..................
View 5 Replies
View Related
Oct 31, 2011
I am using the find function to establish the row number in a spreadsheet for an edit process.
Code:
wks.Range("B:B").find("something").Row
the cells in Range("B:B") are linked to another worksheet. If I copy paste values on the cells in Range("B:B") the code above works a treat.
View 4 Replies
View Related
Apr 2, 2014
When I run macros usually they're fast, but occasionally will run very slow. An example macro I have is pasted below. It's just a simple macro that pastes months. If I am pasting on top of a cell that contains a month it pastes the rest of the months that follow.
Most of the time this macro runs very fast, but occasionally it takes about 1-2 seconds to run, and it is a very simple macro! It is not macro specific, as all my macros will be slow. I''m using Windows 7 with Excel 2010. I'm only using 23% of my CPU and 4.5GB of my 16GB of RAM, so I don't think it's a hardware thing.
Is there some kind of condition that occurs that will make excel run macros slower?
[Code] .....
View 4 Replies
View Related
Nov 28, 2011
I have a simple function that pulls in a single cell value, one per row, stores it in a property of a custom type, and then does a little more processing based on the value.
The custom type is GR, the property is CGID.
Code:
With Worksheets("Sheet1")
GR.CGID = UCase$(Trim$(.Range("CGID")(rw).Text))
End With
It runs fine with no noticeable performance changes up to around 10,000 records, but beyond that, it gets progressively slower and slower and slower until, at about the 20,000 record mark, it's processing at about 1/10 the speed it started at! (100 recs/sec instead of 1000+)
For testing purposes, all the cell values are the same, and are a small string value.
I know that hitting the sheet from VBA is time-intensive itself, so of course it makes sense that if I had two calls to the sheet, it would take twice as long to run the function as if I have just one. But this issue where the processing speed decreases as time goes on leads me to think there's some kind of garbage collection or memory allocation problem that I hope can be solved.
View 9 Replies
View Related
Dec 14, 2006
I want to find a content of ActiveCell only in column "C", beginning from cell C4.What must be in Look in, and Look at?How to accomplish to match whole number,ex. "1" in cell with "1",no to find "1" in "123"
This function searches in whole sheet,but i need only in C column!
Cells.Find(What:=ActiveCell, After:=[c4], LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
View 9 Replies
View Related
Oct 27, 2009
I'm surprised and confused as to why =Match("",A1:A300,0) doesn't find blank cells in the "who does what" worksheet of the attached file.
View 2 Replies
View Related
Feb 18, 2014
I have data from (row 1, column 1) to (row 53, column 5) on 283 consecutive worksheets in a singular excel file that I would like to be presented on a singular worksheet starting from the data on worksheet 1 and descending to the data on worksheet 283.
I am looking for a copy and paste loop solution that will copy the data from each page and sequentially paste the results on a singular output page in descending order (worksheet 1 data, worksheet 2 data... etc) so that I can sort the data.
View 1 Replies
View Related
Jan 6, 2014
I am trying to code a macro that will search through a selected range of cells for key letters, for instance this cell may contain any combination of B, C, Te, Tc, RH, or LH. I would preferably like to search with capitalization being a factor but it is not a deal breaker. Below is a sample of what i have if the cell has a B, C it works for B but ignores the C i need it t o recognize both.
Code:
If InStr(1, ActiveCell.Text, "B") Then Range("O" + CStr(ActiveCell.Row)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
[Code] ........
View 9 Replies
View Related
Nov 21, 2006
i m given 12 max value of my 292 cells. now im asked to find number of cells that falls within each of these max values?? im asked t use histogram. how will i do it?
View 3 Replies
View Related
May 20, 2014
I'm working on a workbook that will track staffing patterns.
The workbook has three worksheets: Sheet1 "RCS", Sheet2 "HCT' and Sheet3 "Hidden". I've attached the workbook to this thread. The password for the form is "j".
On Sheet3 "Hidden" I have two tables that are set up to collect the SUM of columns on Sheets1 "RCS" and Sheet2 "HCT". I'm finding the SUM of each range by way of the background color. I've set up the following formulas and when the "data collection tables" are in the same worksheets as the original information, the formula's work perfectly:
The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"
[Code] ........
The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"
[Code] .......
I have two more functions that aren't working due to the fact that the source values are percentages and NOT plain numbers. The above functions work great for SUM but not for percentages. EXAMPLE--Let's say, 3 sub percentages it gives me the SUM of the 3 percentages (i.e. 85% + 100% + 100% = 285% instead of giving me 95%.
[Code] ........
How might I use the following functions to find the average of the source fields instead of the SUM?
View 7 Replies
View Related
Mar 31, 2009
I have a workbook with several worksheets in the same format. I would like to have a function to output the sheets that have rows in which collumn A = X AND collumn B = Y.
I've been working on this one for a couple days now and I'm not making much progress.
View 9 Replies
View Related
Jul 7, 2014
Let's say I have a workbook with 7 worksheets named, for example, "Instruction", "Begin", "Worksheet 1", "Worksheet 2", "Worksheet 3", "End", and "Data". (in that order)
What I want to do is run a macro to go to whatever worksheet that is in between "Begin" and "End" and copy, for example, cells $C$1:$D$10; then paste as formula into worksheet "Data" starting from cell C1 and then down a list (i.e., copied cells from "Worksheet 1" get pasted as formula into "Data" cells C1:D10; then copied cells from "Worksheet 2" get pasted as formula into "Data" cells C11:D20, and so on and so forth).
But if I were to add more worksheets (e.g., "Recipe" and "ToDo") positioned in between "Begin" and "End" and run the macro again, it'll either 1) re-copy all the formulas from the included worksheets back into "Data" including the formulas from the newly added/placed worksheets or 2) it'll add the formulas from the newly added/placed worksheets and paste into "Data" at the end of the list.
Can create the macro to run based on the position of worksheet, and not based on the name of worksheet, since ultimately there will probably be over 10 worksheets between "Begin" and "End".
View 4 Replies
View Related
Mar 26, 2009
I have a range of cells, for this example I will use 2.
Cell E17 = 77/170
Cell E18 = 8/9
Using the following formula: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)))
This bring back an #VALUE! Error as the second part of the formula keeps picking up "/9" however the first part works fine, displaying "170"
Now if I use:
=SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)-1))
It all works. The problem is that I need this to be automatic using the above way means having to add a "-1" to every formula for a cell with only 1 char to be added.
Using the formula:
=SUM(RIGHT(E17,FIND("/",E17)-1))+SUM(RIGHT(E18,FIND("/",E18)-1)).....
View 2 Replies
View Related
Jul 31, 2007
I have a written the function below, but when ever I use it, and for example drag it across lots of cells, they all come up with the same value, and I have to manual click on each one and pres enter to get it to show the right value. I have tried searching but without much luck as I am not sure what I should be searching for. Using application.volatile doesn't.
Option Explicit
Public Function FirstLinePickUp(inputrow As Variant) As Variant
Dim n As Integer
Dim testcell As Variant
n = 0
testcell = ""
Do Until testcell <> "" Or ActiveCell.Column - n <= 0
testcell = Cells(inputrow.row, ActiveCell.Column - n)
n = n + 1
Loop
FirstLinePickUp = testcell
n = 0
testcell = ""
End Function
View 4 Replies
View Related
Nov 12, 2008
I have a macro that continues to tell me that an End With and/or End Sub is expected. I continue to add these statements in various ways, but the macro will not complete properly.
Here is the
View 5 Replies
View Related
Jul 30, 2009
I have two worksheets named "Data" and "Product". I want to use the following macro to sort the records depending which sheet is active. If I am in the "Data" sheet, it does the sort in the "Product" sheet? I was expecting it to ignore the second IF statement when I am in the "Data" sheet?
View 3 Replies
View Related
Jan 19, 2010
worksheets("Demorecords").Range("k2").formula = _
"=IF(AND(DAYS360(N2,Current_Date)>" & _
Combobox1.Text "!$B$6,DAYS360(N2,Current_Date)" & Combobox1.Text "!$B$7,"Pending Delete","Persistent"))"
A error box says compile error: expected end statement and then highlights this part (red)
View 9 Replies
View Related
Feb 2, 2010
I am receiving an error from the Microsoft Query wizard when I try and create a query of an xlsx or xlsm file. The error is "External table is not in the expected format."
Why this error is coming up and how I can correct the file so that I can write a query to pull info from a Named Range in the file.
I have written some code to duplicate the Microsoft query using ADODB but get the same error message when the Open connection line runs.
View 3 Replies
View Related
Sep 22, 2006
My SUMPRODUCT formula is returning 0 instead of expected results
=IF(ISERROR(SUMPRODUCT((WOR_Data!$A$2:$A$429=$A7)*(WOR_Data!$E$2:$E$429=$L7)*(WOR_Data!$K$2:$K$42))),0,SUMPRODUCT((WOR_Data!$A$2:$A$429=$A7)*(WOR_Data!$E$2:$E$429=$L7)*(WOR_Data!$K$2:$K$42)))
I have verified that all criteria match criterion for spelling, case, length(trailing spaces). All fine. First comparison is unit, second comparison is account description, 3rd argument of course column to be summed.
If I take out the error handler, I receive #VALUE! error
View 9 Replies
View Related
Feb 19, 2014
How to get the "Expected date and time of closure" in MS Excel 2007
Call No. ABCD
Login Date 2/15/2014
Login Tme 9:30 AM
Agreed SLA (Service Level Agreement) 1hr
Accessibility Type 24hrs
Site Accessibility 24hrs
Accessibility Days All Days
ETC Date ?
ETC Time ?
[Code] ......
View 1 Replies
View Related
Jun 17, 2009
I have used the LOOKUP formula several times with no problem.
Now -- even in the simplest example -- I can't get it to work as I expect.
Rather than explain, attached is a very simple spreadsheet that shows how the LOOKUP formula is returning a different value than I would expect.
As mentioned on the spreadsheet, the value I would expect to be returned is "euro", not "other sam".
View 6 Replies
View Related
Mar 26, 2013
I am trying to create a 'simple' spreadsheet which will allow me to check how many children are going to be attending my nursery and split them into the different age bands.The columns I am using at the moment are
Name
D.o.B
Start date
Mon AM
Mon PM
Tues AM
Tues PM
I need to be able to split this data into three age groups: Babies (under 2 years), Tweenies (2-3 years) and Pre-school (3+).I would also like the formula to take account of the start date so to remove children from the list who will not have started by this data.
The idea is that I can use this as a quick look to see if I can accept another child / give me an idea of staffing needs. I know there is software out there that can do all this and more, and used to use them on previous nurseries, however this is a new start nursery which does not have the cash to pay for the software at the moment so I am looking to save myself a few hours of checking it all myself until the nursery is up and running and can afford the software which we would hopefully buy in year 2.
View 3 Replies
View Related