Inexplicable Subscript: Find That LBound(sourceData) = 1 And UBound(sourceData) = 11, As Expected

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


ADVERTISEMENT

What Is UBound And LBound

Jan 7, 2013

could not find a satisfactory explanation UBound and LBound functions.

View 2 Replies View Related

Chart SourceData Code

Jun 14, 2007

Creating a vba program that automatically generates charts. Looking for the easiest way to get the range of the data that the chart is using (later in program). Noticed the SetSourceData method but dont see a GetSourceData (that returns a range).

View 3 Replies View Related

Change SourceData For All Pivots In Workbook While Disconnect / Connect Slicers?

Jan 28, 2014

I have 6 Slicers in called "Quantrix" The slicers are called REG, DIRECTOR, AREA MANAGER, AOI, DMA, AG and CLOCK. I have 15 pivots (6 in Quantrix tab and 9 in a tab called "Pivots").

I have vba code to update all pivot cache (showing 1 pivot update below) ...

PHP Code: 

    Max = Sheets("eLink_Raw").Cells(Rows.Count, "A").End(xlUp).Row
    Sheets("Quantrix").PivotTables("Quantrix 1").ChangePivotCache ActiveWorkbook. _
      PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
      Sheets("eLink_Raw").Range("A1:AW" & Max).CurrentRegion _
      , Version:=xlPivotTableVersion14) 

Here is the code to disconnect SLICER, update source data for ALL pivots then reconnects slicer... This works great but its ONLY 1 SLICER

PHP Code: 

 Dim vPivots As Variant
    Dim i As Long
    Max = Sheets("eLink_Raw").Cells(Rows.Count, "A").End(xlUp).Row
    With ActiveWorkbook.SlicerCaches("Slicer_REG").PivotTables

[Code] .....

How can i disconnects ALL Slicers, change source data for ALL Pivots then reconnect ALL slicers?

View 14 Replies View Related

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 View Related

Run Time Error 9: Subscript Out Of Range. Find Method

Sep 8, 2006

when the code runs until

'*This is to the row that contani the target
lRow = Worksheets("sheet3"). Range("A3:A3000"). Find(What:=code, LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Row

it gives me this msg : subscribpt out of range

here is the whole
Private Sub Worksheet_Change(ByVal Target As Range)

Dim code As Variant
Dim lRow As Long
Dim color As Range
Dim discrib As Range
Dim price As Range
Dim myrange As Range

View 9 Replies View Related

Size Of Array - Ubound

Mar 23, 2007

How to calculate the size of an input variant using UBound? Sometimes I need to do the transpose but sometimes I don't. I tried ON ERROR GOTO but it doesn't work in this case. Can you tell me how I could do that?

Public Function Shock(A_E As Variant, Year As Variant) As Double

Dim n As Integer

n = UBound(A_E,1)

Shock = n

End Function

View 9 Replies View Related

UBound Array - Type Mismatch

Jan 27, 2009

This is the first portion of my code. When it reaches x1, it dies... I put in a break... when I highlight that line, it displays:
UBound(myArray1, 1) =

Public Function M_ADD(myArray1, myArray2)

Dim i As Integer, j As Integer
Dim x1 As Long, x2 As Long
Dim y1 As Long, y2 As Long

If VarType(myArray1) >= vbArray Then

x1 = UBound(myArray1, 1) '

View 9 Replies View Related

UBound With Arrays Of User-Defined DataTypes

May 1, 2009

My question is about using UBound with an array of User-Defined Data Type. I don't know how to use UBound to find the total elements in the following arrays called MealItem()

View 3 Replies View Related

UDF Not Updating As Expected

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

Ending Macros (End With And/or End Sub Is Expected)

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

IF Statement Not Working As Expected..?

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

Expected End Of Statement Error

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

External Table Is Not In The Expected Format?

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

DMIN Function Not Working As Expected

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

Sumproduct Returns 0 Instead Of Expected Results

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

Excel 2007 :: How To Get Expected Time Of Closure

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

Can No Longer Get LOOKUP To Work As Expected! (2007)

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

Expected Attendance Per Session Split Into Age Ranges?

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

Adjust Range Within Formula To Get Expected Results?

Dec 13, 2013

I have a system that generates 4 databases with different types of statistical data. Observed defects, Calculated Defect, Capability and Number of points. I have set up a system for telling me what ranges each section is and I am manually changing the ranges to get my expected results.

Is there a formula that I can use that could adjust my ranges, within my formulas?

View 9 Replies View Related

Compile Error Expected Function Or Variable

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

Compile Error: = Expected After Function Call

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

Formula Doesn't Provide Amount As Expected

Mar 26, 2007

My sheet called 'Report' finds a type using a vlookup. I then have a sheet called labor, where I want to find put the total amount for labor using this formula: '=SUMIF(Report!$K$2:$K$65000;LABOUR;Report!$G$2:$G$65000)', but it only returns a '-'. I want the formula to return total amount from column G, if column K is type 'LABOUR'. Is my formula incorrect?

View 2 Replies View Related

Loop Formula To Search Through Range To Get Expected Output?

Feb 14, 2014

Trying to look for formula for searching through Range and search through Data to achieve expected res

Data Output ExpectedRange
Pune 123 is city Pune 123 Mumbai 999
Maharashtra 345 is state Maharashtra 345Pune 123
India 678 is Country India 678 Nagpur 666
Maharashtra 666 is state OthersPune 555

Below is the query i used for manual search however need formula to see through Range and achieve output expected

=IF(ISNUMBER(SEARCH("Pune 123",A5)),C5,
IF(ISNUMBER(SEARCH("Maharashtra 345",A6)),C6,
IF(ISNUMBER(SEARCH("India 678",A7)),C7,"Others")))

View 5 Replies View Related

Compile Error Expected Line Number - End Statement

Apr 18, 2014

I am getting this error and where th If not starts its is in red showing that is where the issue is:

HTML Code: 

Sub RemoveRows()
Dim LR As Long, i As Long
Dim ws As Worksheet
Set ws = Worksheets("100 Airports")
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 10 Step -1

[Code] .....

View 3 Replies View Related

Recorded Macro Doesn't Seem To Provide Expected Result

Sep 21, 2009

On column A, I simply do an A-Z sort that gives the expected result. So I recorded it as a macro. In column G is a code that corresponds with column A. When the marcro is executed Column A is sorted but the corresponding value in G stays where it is. Clicking the A-Z button works. But when that action is recorded as a macro I get the above quirck. Since VBA is not my beef, I don't really know what wrong.

View 4 Replies View Related

Select Case On Iterator Variable Not Working As Expected

Feb 21, 2012

I have had this problem in multiple different macros so I will just provide an example of relevant code:

Code:

For J = 0 To 3

Select Case J
Case J = 0
LikeVar = "a*spk1*E0"
LikeVar2 = "a*spk1*E7"
End Select
Next J

For some reason it skips over the likevar variable assignments even though J = 0 on the first time through the loop. I have tried changing the For line to J = 1 to 3, and the case to J = 1, but it still skips to the end select. Is it not possible to use a select case on an iterator variable?

View 4 Replies View Related

How Can I Get The Sheet To Show The Next Expected Date That The Data May Appear In That Column

Dec 8, 2008

I have some more questions about excel formula. In one column I have data appearing, as either a 1 or 2, at random as I input it. Each column has around 120 rows. In the row at the bottom I created a formula to calculate how often, on average, the data appears in that column. 1st how do I get each row to represent a date. 2nd how can I get the sheet to show the next expected date that the data may appear in that column, taking into account the date it last appeared and the average between appearances?

View 9 Replies View Related

Shell Function Compile Error - Expected Procedure Not Variable

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

Event Triggered When Not Expected When Deleting Protected Cell Data

Oct 31, 2013

I have a relatively complex excel VBA solution which has a top level spreadsheet containing a dashboard of stats (test statuses within projects).

There are various reporting options available which open other spreadsheets to collect data and present it within the top level spreadsheet - these are closed once the data has been copied.

For example, I select an option to show me all the issues relating to tests for a particular team and the solution opens a series of spreadsheets and copies the issues into the summary spreadsheet.

The problem is that the report has the option to double-click a row, which essentially opens the corresponding Excel file and focuses on the relevant row containing the issue.

This works fine but I've added protection to the report to prevent users editing the data here (they should double-click to open the underlying file and edit there).

The report is a protected sheet and locked/unlocked cells can be selected - in fact, they need to be to allow the double-click event.

If I put my cursor in a cell and click Delete then pass over the warning that the sheet is protected then select Enter, Excel raises error "1004: Select method of Range Class failed".I understand that use of worksheet select is not a good idea but what concerns me is that the event which occurred prior to the double-click (the one which collected the data for the report) is running when it shouldn't be so.

Why would the earlier event be triggering and why doesn't it hit my breakpoint placed in the related code block even though that must be running?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved