I have a array of 10 values(integers) that have been sorted using the Selection Sort method. The array has been named SelectionSort and I need to call the first and last index position of the sorted array in order to preform a binary search on it. What is the code for calling these two index positions?
View 5 Repliesi am trying to produce a simple work sheet that will sort the positions via one column automaticly with out having to do it manually.
View 2 Replies View RelatedI have a global dataset where column A represents longitude, B represents latitude, C is a spinup value which can be ignored (even deleted) and columns D to DH are runoff values for the years 1901 to 2002. The rows extend down to 6510 (including the column headers, 6509 without). I've been trying to create a reformatted table based on this data, where longitude values are listed along row 1 and latitude values are listed in column A, with the corresponding runoff values for each year correctly repositioned into the spreadsheet according to this new 'format'; there will be 102 grids created (one for each year). The longitude values are currently not ordered and will need to be sorted (low to high) into position along row 1, without causing the runoff data to become unassigned to its associated longitude and latitude coordinates.
The second part is to then perform a linear interpolation of the reformatted data for each year's table so that the data is again reformatted' from 1.5-degrees to a 5-minute spatial resolution. This involves creating tables for each runoff year as before, but this time creating new values via application of a linear interpolation between each adjacent pair of existing values (in both the x and y direction - as the data are to be imagined as being on a geographical grid with coordinates). I've currently been trying to achieve this using Fortran, but have been getting nowhere as I'm completely new to this programming language, so I thought I'd try using Excel as I'm more familiar with this, although not sufficiently so to solve this alone!
I'm analysing some data sets with variable content, and need to insert blank rows every so often to provide space to do some analysis. The problem I have is that the position of the blank rows change according to the data set, I have been able to calculate how often the row needs to be inserted using VBA as part of my import routine and saved this value to a cell, but I cant figure out how to write the VBA which will insert rows using this value and stop at the end of the data set.
In the attached example I need to insert a row each time the data in column 2 (name) changes, this is the value I have calculated which in this example is 7. So in this attached file I need to insert a row in row 2, row 9 and so on etc. But this is what changes depending upon the "Product" held in colum 1.
I have a complicated one here (at least for me)...
I have a very simple sample spreadsheet showing exactly how I'd like it to work but couldn't find out where to upload it? I can upload or send to you privately, if need be? May make it much easier to understand the desired end result.
I want to populate as follows:
Column A: "IN TIME"
Column B: "NAME"
Column C: "POSITION"
I have a date in cell D1 - today().
I need to search for that date in a column between range AA2 and BN2. When it finds the date (let's say it finds a MATCHing date in AA2), it will then search that entire column cells AA30:AA1920 and look for "server". When it finds "server" it will then grab the in time which will always be 4 cells directly above where it found the position ("server"), and then the name of the person, which will always be in column X, 6 cells above the position.
There will be multiple instances of "server", and I want to populate the columns A, B, C with all the servers it finds first for that day with their name, in time and position. then a blank row.
Then, I want it to find "Bar" positions using the same formula and method, and populate that right below the server data, then find "line", etc.
Ultimately, I'm trying to make a daily staffing plan, where all the people that are working on that date, it will show their in time, name, and position in columns, A, B, C as far down as necessary.
However, I'd like them auto grouped together by position, so I'd like the formula to somehow populate all the servers first, then all the bartenders, then hosts, then line, etc.
I am trying to set up a spreadsheet to calculate margin on stock positions.
There are 3 tiers, based on number of shares. My example:
400 shares in held in total.
1st 200 shs margin at 10%.
next 100 shs margin at 20%.
everything above 300 shs (ie 100) margin at 30%.
The 400 shs may be made up in any number of ways, (ie lots of smaller amounts) and i need a formula to give me the margin amount on each position, flexible enough for when positions are added / removed.
I've been looking at SUMPRODUCT but can only seem to make it work for the total, not each individual position.
What I am trying to do is to be able to paste 2 telephone numbers into one cell and subtract the last 4 digits of each from the other. for instance entering 4063219225-4063219297 into one cell subtracting the last 4 digits of each number to come up with 72 (9297-9225).
View 9 Replies View RelatedI need to list all freeze pane position from every sheets in my workbook in order to reverse a "unfreeze all" function. I don't know if i need to be more specific ?
Situation : I have a workbook with many sheets. Every worksheet has a different freeze pane position. I hate freeze panes, so while i'm working in a workbook, i remove them all. My boss likes those ones so i want to give him back my work with the same layout.
The main thing i don't know here if how you ask Excel to fetch freeze pane info. Ideally the information will be reported in a new sheet, columns : Sheets, HorizontalFreezePanePosition, VerticalFPP...
Once i will have this list, i will create a new macro and set back freeze pane individually.
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
View 4 Replies View Relatedi have 5040 rows in a sheet. in columns A,B,C,D,E,F, i have some input. and based on that i have some formula on column G.
lets say, min value is at row 4094 by looking at G column... i want to call entire row to the, lets say K L M N O P ....i need a formula for that....
random numbers are included in the formula. So ever time i make any changes on the sheet, min value changes even thought i dont play with the formula and input... so i want my new min value shown at row 1 of K L M N O P, every time i do that
how can i do that?
I am creating a set of lists, each on a different sheet. I want to have the first sheet as a sort of "Title Page" with basic information from the others. Is it possible to create a link of some types so that I could click on "Magazines" for example and go to the sheet called Magazines, or "Books" and go to the sheet called books. The latter would be very handy as this workbook has around 30 sheets in it, which is a pain to look through to find the right one.
View 2 Replies View RelatedI have created an XLA using a blank workbook which has a macro (called CompareMan) I have written within it.
I have written a utility (called INSTALLMAN) to install this add in.
When I run the install it creates and activates the add in OK.
The install also creates a toolbar with a button to trigger the macro.
Problem is, when I click the newly created button, no matter which workbook I am currently in I get a message ...
"The macro INSTALLMAN.xls!CompareMan cannot be found".
So my question is, how do I get my button to trigger the macro in my add in?
I have to do the same procedure for a number of profit centers. The only differences are the names of these profit centers. Therefore I want to use the same code, with changing references. How can I use this array so that it calls/uses 1 profit center at the time? If I use the following code they all are used at once (P1100P1200P1300P1500P1600P1900)
View 2 Replies View RelatedIs there a way to call a sub with out actually saying its name? In other words can I refer to a sub by a value or a cell that holds its name? for example, in my CNC programs I often use something like this;
View 14 Replies View RelatedOkay, I know you can reference worksheet functions with code like:
application.search()
But how do you use a UDF function within VBA???
I tried, thisworkbook.UDF() (UDF being the name of any UDF) but it didn't work.
I have a add in for excel (self made) and I recently hit a wall when trying to add functionality to it.
I have a form "UserForm1" created within the .Xlam file of the add in.
I want this form to be useable for any excel file that might be open, so putting code in the excel file that needs to use the form is undesirable.
The functions carried out by my add in are activated trough ribbon buttons.
my goal is to have a button on the ribbon that loads the form.
As the UserForm1.show code doesn't work, I will need a alternative.
I did find the code for making the sheet show if there would be a button in the workbook, however I want all code to be in the add in.
I am using the following code to find the text "Employment" in any row in colum H. If found it copies it to another worksheet.
Sub CopyRowsSht8()Sheet8.SelectSet Rng = Sheet8.Range(Sheet8.Range("H1"), Sheet8.Range("H" & Rows.Count).End(xlUp))For Each cell In RngIf cell.Value = "Employment" Thennum = cell.RowSheet8.Range("A" & num & ":" & "L" & num).Copy Destination:=Worksheets("Employment").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)End IfNextCall CopyRowsSht9End Sub
1. This sub calls CopyRowsSht8 and then this calls CopyRowsSht9 and so on. I have 15 sheets so how would I code this into some kind of loop to include sheets 8 to 22 instead of calling sub then sub then sub etc.
2. It takes a while to do just two sheets so 15 would be very slow - can this be made faster. The maximun number of rows containing data is only about 100 on each sheet -could that make a difference?
Im trying to call another subroutine within the same workbook but it doesnt work,
---------------------
Sub Macro1()
Dim mac1 As Variant
Dim mysub As Variant
Select Case Cells(1, 3)
Case mac1
Call mysub
End Select
End Sub
---------------------
The below thing has been driving me up a wall for 2 days now. I know the fix has to do with calling the variables, but I can't figure out the exact code.
Sub Parsing_Logic()
Dim IBG_URL As String
Dim A As String
A = Mid(IBG_URL, Application.WorksheetFunction.Find("/200", IBGURL), 8)
Dim B As String
B = Left(IBG_URL, Application.WorksheetFunction.Find("/200", IBGURL))
Dim C As String
C = Right(IBG_URL, Len(IBG_URL) - (Len(A) + Len(B)))
Dim D As String
D = Left(C, Application.WorksheetFunction.Find(".200", C))
End Sub
Function IBGLink(IBG_URL As String, Formatted_Date As String)
If Application.WorksheetFunction.IsErr(A) Then
IBGLink = (D & Formatted_Date)
Else: IBGLink = (B & Formatted_Date & D & Formatted_Date)
End If
I am trying to call another macro, but instead of using the name of the macro, I have it in a variable. I am getting an error that says "Compile Error: Expected sub, Function, or Property." Can you call a macro that is in a variable? I basically have a bunch of macros created. I also have a list of the name of those macros on a worksheet in Excel. I want excel to start at the top of the list in excel and run the macro, then move down and run the next macro, and so on. Below is my code.
Sub Commercial_2005()
Dim Macroname As String
Workbooks("bleeg.xls").Activate
Worksheets("CommercialList").Select
Cells.Range("a1").Select
While ActiveCell.Value <> ""
Macroname = ActiveCell.Value
Workbooks("copy of recast_Report_v2.xls").Activate
Call Macroname
Workbooks("bleeg.xls").Activate
Worksheets("CommercialList").Select
ActiveCell.Offset(1, 0).Select
Wend
End Sub
I guess I just don't understand the basics of calling a function. I have the following function in a standard module:
Function CleanUp(ws As Worksheets)
ws. Range("A2:P100").ClearContents
End Function
I have this code in a worksheet module:
Sub Trying_to_call_a_function()
CleanUp (ThisWorkbook.Worksheets("Month End"))
End Sub
But when I run the routine that calls the "CleanUp" function, I get the message: "Object doesn't support this property or method." I have tried changing the Function to this: CleanUp (ws as Worksheet) [instead of (ws as WorksheetS)]. Yet, this does not change anything.
Is calling a subroutine within another different than running it from the play button in the VB editor? I am getting different results.
I have the following code that I want to call from another subroutine that creates a series of command buttons.
Sub DynamicButtons()
Dim ButtonCount As Integer
Dim ctl As OLEObject
' Create the Button objects
ButtonCount = 0
For Each ctl In Sheets("Sheet1").OLEObjects .............
I'm not sure whether this should be a general question or it should go in the programming section... but here here goes anyways
I need to create a spreadsheet where the contents of Col A & B in Sheet1 are copied into both Col A & B in Sheet2 & Sheet3. Sheet1 Col A&B will be continuously updated (rows will be added) and as these columns are updated, Col A & B in Sheet2 & Sheet3 should be updated automatically and keep the format that is set in Col A & B in Sheet1.
Now, Col C&D in Sheet2 & 3 will be updated by a different user. As the user updates the cells in Col C&D in Sheet2 & 3, Col C&D in Sheet1 should automatically be updated with the data in the cells of Col C&D in Sheet2 (and keep the format) and Col E&F in Sheet1 should automatically be updated with the data in the cells of Col C&D in Sheet3 (and keep the format).
Currently I'm trying to set up a table that referneces sheets that don't yet exist. These sheets will be added by the user or by macro, either one. I want my table to automatically take these pages into account when they're created without having to rewrite my formulas. The problem is that when I make the call to a sheet that's not there, it opens a file open dialogue box and I don't want it to do that. Is there something I can do here about that?
I don't think it's really going to shed any light on the subject, but this is my call (from memory, not copy and paste)
IF(OR(ISERROR('P01'!N2),'P01'!N2 = ""),"",'P01'!N2)
Also, when I enter this formula and the sheet doesn't exist, it automatically reverts to a text field and I have to manually switch it back to general in which case it gives a #REF error (which is just fine).
Also, I'm writing it in Excel 2003, but it completely crashes Excel '97 if I try to open it there, how can I fix this?
I've got three workbooks that end users will be using for data entry. The VBA code for the three workbooks is identical.
I'm trying to encapsulate the code into an add-in so I only have one copy of the code that I have to debug/maintain.
Furthermore, I've also encapsulated the code in the add-in into a class. The class isn't really a "usual" object modelling some data; rather, it's a utility class processing Worksheet_Change and Worksheet_Activate events. Hopefully this isn't a really bad idea, rather than keeping the code in standard modules in the add-in.
The application is called "Midas". Here are some code excerpts:
Midas.xlam:
Standard Module:
[Code] ......
Class Module:
[Code] .....
And in the end user workbook:
[Code] .....
The Wbk_SheetChange event handler is triggering, but it's looking for the code in the end user workbook, instead of the class. Is there a way I can change
[Code] .....
to call the private routine "Cases_Changed" (where "Cases" is the sheetname) residing in the class instead of looking for it in the workbook?
Hi, i'm having a terrible problem (at least for me).
I want to use a var from a UserForm to use it in calculation method in a Module.
How can i refer to that variable in the Userform in the Module code?
I'm having some trouble getting to grips with using/calling variables and other sub routines.
View 9 Replies View RelatedI would like to know if it is possible to display a form using a variable containing the form name rather than the form name itself, I have tried everything I can think of and cannot make it work. I know I can use CASE SELECT but I have a large number of forms involved and would like to use a variable name if possible.
View 4 Replies View RelatedI need to extract the filename “My Excel File” from Worksheets(“Sheet1”).Range(“A1”), whose value = C:Documents and Settingsuser1DesktopMy Excel File
I found on Chip Pearson’s site a Function TrimToChar which, using SearchFromRight and a TrimChar of “” will trim OFF the filename “My Excel File”, leaving the Path.
But, I think I could use his function to tell me the number of characters in the Path and then use that to extract the remaining characters from the total character length (Mid Function)
Problem is, I don’t have a clue how to call a Function in VBA to work on Worksheets(“Sheet1”).Range(“A1”). How do you set the InputText , TrimChar, & SearchFromRight?????
(eventually, this will be in a loop, where I extract the filename from a Dynamic Named Range (list) in Column A)
I currently have a macro that runs every 10 seconds. This macro calls 5 sub-routines.
Code:
Public Sub Copy_Data()
*****code****
*****code****
*****code****
*****code****
Call SortData
Call Create_OutOfStock_File
Call Create_NearEmpty_File
Call Create_InStock_File
Call Create_Other_File
Application.OnTime Now + TimeValue("00:00:10"), "Copy_Data"
End Sub
I need to keep the current code to run Copy_Data every 10 seconds, however, i would like:
Code:
Call Create_OutOfStock_File
Call Create_NearEmpty_File
Call Create_InStock_File
Call Create_Other_File
To be called/run every odd minute, for example, 00:01:00, 00:03:00, 00:05:00