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
Jan 7, 2013
could not find a satisfactory explanation UBound and LBound functions.
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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