Obtain Workbook Name
May 31, 2007I would like to obtain the name of a workbook after activating it and then paste it into an input box.
How could I do this?
ADVERTISEMENT
Obtain An Interpolate Value For Z, Given A Value For X And A Value For Y
Mar 29, 2009I just have a quick question with regards to two-way interpolation of data.
I have a data table which has x-values, y-values and z-values.
I need to obtain an interpolate value for z, given a value for x and a value for y.
The x value specified is 1.569 and the y value specified is 1.66.
I know how to use the FORECAST formula for a simple x, y table, but I have no idea what to do here when I need to find a z value, given the x and y values.
Obtain A Value From Another Cell
Feb 27, 2009I'm trying to find a way to enter the value of a cell from another worksheet into my currently active cell. The (obviously incorrect) code I'm working on looks something like this:
View 2 Replies View RelatedObtain Details From Table
May 25, 2006I am currently running XC running races and am wanting to know how i can have a quicker way of sorting out the best of sex, best of age group and best overall. The age groups are Under 18, under 30 and over 30. What can i do with v- lookup and other formulas to make it easier to record when they come in with out a long wait, i just want to be able to type in thier number and get all thier details so i can add the time straight in, then from there work out the winners instantly
View 2 Replies View RelatedObtain/Return FilePath/Name In VBA
Sep 4, 2006Does anyone know the code for obtaining the filepath of the current workbook? Tried a search and can't find anything on this.
View 9 Replies View RelatedObtain Cell Formatting
Nov 23, 2006how to obtain what the formatting of a cell is, be it a date, number, custom, currency, etc? I want to acquire the format of the cell in vBA code, in order to display the value exactly as it was formatted in the cell when sending that value to a label on a UserForm or Field on a Microsoft Word form.
Example:
Cell A1 has the value 12/25/2006 formatted with the Date format: "mmmm-dd-yyyy" appearing as December 25, 2006
Cell A2 has the value 9.247 formatted with the Number format: "#.##" appearing as 9.25 to show only two-decimals
Cell A3 has the value 899, formatted with the Custom format: "00000" appearing as 00899 to represent a five-digit US zip code
Cell A4 has the value 1234.56 formatted with the Currency format: I think -"$#,##0.00_);($#,##0.00_)" appearing as $1,234.56
For rownumber = 1 To 4
Entry = Cells(rownumber,1).Value 'gets whatever in cell
Formatting = Cells(rownumber,1).Format 'makes no sense in vBa.
msgbox Entry & " formatted as " & Formatting
Next
Obtain The Array Values Of A Function
Jan 25, 2013How can I obtain the array values from a function?
For example: Those steps were made by cytop to split a text (btw it works perfecet!!), I just added Function splitText(strSplit As Variant) As Variant ... End Function
VB:
Function splitText(strSplit As Variant) As Variant
Dim datosColumnaIncio() As Variant
Dim iTemp As Integer
Redim datosColumnaIncio(Len(strSplit) - 1)
For iTemp = 1 To Len(strSplit)
datosColumnaIncio(iTemp - 1) = Mid$(strSplit, iTemp, 1)
Next
End Function
So I want to know if its correct like that:
VB:
Function splitText(strSplit As Variant) As Variant
Dim datosColumnaIncio() As Variant
Dim iTemp As Integer
[Code] .....
So when I call the function:
VB : MsgBox splitText("F4")
It throws an error message :s what is wrong or how can I get the array values of a function ???
Sort And Obtain The Lowest Value In A Range
May 1, 2014Excelforum.jpg
What I want to do is in column 5 row 1, I want it to show a value only if all 4 columns, row 1 are filled in. For example in the picture I want to show a blank in the first 8 rows but to start showing a value on the rest of the rows, in this example IPE 240 to be the first value. When it shows this value from this row I want it to obtain the value with the highest percentege inside. For example in this picture the correct value first value to be shown in column 5 will be IPE (84%)
Ive done this code so far: =IF(AND(B256<>"",D256<>"",F256<>"",H256<>""),Here I want it to show the value on the row that has the highest percentege,"")
How To Obtain 6 Numbers In A Cell With No Duplicates
Jan 29, 2014At any rate, for a statistics experiment, I need a way to obtain precisely 6 random, numeric values in a cell, separated by commas, with no duplicates.
For example, if cell A1 contains the following: 7, 12, 14, 7, 14, 3, then I need to remove the duplicate(s) -- in this case, the extra 7 and the extra 14. After the two duplicates are removed, I then would have to replace with two more values (non-duplicates, of course).
Here are the restrictions, in addition to having no duplicate values appear:
* The first 5 values need to be randomly drawn from values between 1 and 75, inclusive.
* The 6th value needs to be randomly drawn from values between 1 and 15 (inclusive).
* It would be nice - but not necessary - to have the first 5 values sorted in ascending order. For example: 7, 12, 14, 29, 70, 3.
How Do I Obtain The Name Of The Form Calling Macro
Nov 21, 2007I've assigned a single macro to numerous Form Checkboxes (not ActiveX). I'd like the macro to be able to identify the Checkbox calling it, so that if for example, if it were fired by clicking "Check Box 41", the macro should know that.
Is this possible, or must I use an ActiveX control?
SUMPRODUCT :: Obtain Subtotals On A Report
Jan 6, 2009I have been using the Formula 1 (see below) to obtain subtotals on a report, which works fine. Cell A4 has 1st July 2008 and cell A5 has 1st Aug 2008 entered as dates.
I wanted to confine the formula to a single row by using the MONTH() function using formula 2. I have spent a considerable amount of time trying to get this working with no success.
Is someone able to have a quick look at this and tell me what I am doing wrong.
Formula 1
=SUMPRODUCT((INDEX(Consolidated,0,16)="SI")*(INDEX(Consolidated,0,14)>=$A4)*(INDEX(Consolidated,0,14)
Formula - To Obtain The Last Two Numbers Before The Letter B
Jan 25, 2009how it would be possible to obtain the last two numbers before the letter B, if "Fri Aug 01 16:40:49 BST 2008" is placed in A1 so "49" would be placed in B1,
View 9 Replies View RelatedApplication.FileSearch To Obtain Last Modified Date
Mar 4, 2010I've been trying to get this to function correctly. The following code does pull out a list of all filepaths, but what I want to to is access the properties of the file.
Basically I select a customer from a combo box, and from this the code does a vlookup to determine what folder the reports are stored in. The list is then entered onto the worksheet 'FilesInReportFolder'. There is some commented out code as I've ben trying to come up with a simple way to remove the 'xls' from the end of the string, but din't worry about that.
I've found a website that suggests adding .name to the lineto get the name instead of the path, but what I really want is be able to pull out other details about the file.
Here's my
Private Sub ComboBox1_Change()
Combobox1value = ComboBox1.Value
Dim rng As Range
Dim RowNo As Integer
Dim NoOfFiles As Integer
Dim foundfilepath As Integer
Dim FilePath As String
Obtain Column/Row & Address Of Cell/Range
Sep 7, 2006Let's say Cell is a cell.
How do we select this cell's column? I tried :
Cell.Column
but it doesn't work. I have an error message.
Browse Button Code To Obtain Single File Name
May 29, 2009i've looked on the threads and there are some exaples of this but not specfic enough.
i need code that allows me to browse my PC for Excel Files then select one file and put it's name into the activecell?
Formula To Obtain Current Week Monday Date
Mar 17, 2013The portfolio team uses data that is refreshed every Monday. Write a formula to obtain the current week's Monday's date.
View 3 Replies View RelatedHedging Simulation, Solver To Obtain A Maximum Profit
Apr 18, 2007Im setting up a fixed hedging simulation using excel and want to use solver to obtain a maximum profit. I have taken historical stock prices and then predicted future prices using the random walk process. To create the hedge I am using european calls and puts in both long and short positions. The simulation has been set up so that the options are being brought/sold when the historical data ends and then sold/payed when the predicted data finishes.
I am letting solver change the values of the strike of each of the options and also the amount purchased/sold for each of the options too! My constraints are that all the strike prices have to be >= 0, all the amounts of each particular option has to be >= 0 and that profit has to be >= 0. It is worth noting that as well as the values already mentioned, d1 and d2 values are also being calculated on the sheet which are then used to calculate the value of the options being used (using Black-Scholes model), this value is then given a realistic spread value and then 3% is added on as the brokers fee. All of the values mentioned are being taken into consideration when caluclating profit.
Excel 2007 :: MATCH Or INDEX To Obtain Last Immediate Value On Changing Column
May 23, 2014I have one worksheet. I am needing to match up column G cells (TELCEL/MULTI) with value on E3 and with value E4 and give consecutive numbers on column F depending if the matched column G with value on E3/E4 is on same date of column L and if column Q (Tipo de movimiento) appers "Ventas"; I have plenty blankcells and other values on column G.
column E
# DE EVENTO
COMPANIA
INICIO
MES
Clave del producto
Descripcion
Fecha del movimiento
Clave de la tienda
Clave de la caja
Clave del usuario
Clave del vendedor
Tipo de movimiento
row3
TELCEL
[Code] ........
I have to put 1 to first and then sum one if condition is true
1
TELCEL
TAT
may
TAT31
Telcel, Telcel tiempo aire $31
01/05/2014
01
01
3
Ventas
How To Obtain Similar Text Values From Data Range In Next Column Heading
Jan 8, 2013I have attached the file, Problem.xlsx
How I will populate data of Weekday through the cells.
Is there a formula or function to populate data.
Using Month Function To Obtain Month And Copy It To Next Empty Column
Apr 4, 2013I am working in a spreadsheet that contains a bunch of data, it is not limited, and varies. It has some fiels such as: name, date of birth, address, and others. I am interested in creating a column with only the month digit of the date of birth for each row.
I have been working with some code, I am not quite sure how to continue. The following table would be a example that I have of the data, it has only to entry in the column of Date of Birth, but my data range will always vary. I want to get the month and past it in the next cell that is available in this case would be column c or 3... The worksheet name is REP.
VB:
Dim Cell As Range
' 1st cell with the posting date
Set Cell = Range("A2")
Do While Not IsEmpty(Cell)
If Cell = "Date" Then
[Code] .....
VBA - Use Command Button To Obtain Save As Screen And Save As PDF
Jun 22, 2014I have a UserForm in which i have inserted a CommandButton. I also have a Table on a Sheet in Excel that i would like to save as PDF.
When i click the CommandButton on the UserForm i would like the SaveAs screen to come up and have the Table (or Used Range in the Excel Sheet) as the selection to be Saved as .PDF format.
Obtain Cell Reference/address Base On Cell Value
Mar 10, 2008I have this certain value "Y" in all the sheets. In the summary sheet, I would like to get the cell address for this value in a given sheet. I know that you can get cell address using CELL worksheet function. However, for that you need to know the reference to the cell, which is what I am interested in.
I tried Hlookup to get column #. But couldn't figure out how to convert the number to letter so that I can use MATCH to get row # and then finally, index or address function to what I am looking for.
If someone knows how to convert column # to column letter OR knows a better/easier way to obtain cell reference/address based on cell value,
Obtain The "average" Of The Data In E3 Through E24
Oct 20, 2007I need to obtain the "average" of the data in E3 through E24. If I filter this column I still need the average for all of that data and not just the average of the filtered data. This formula only provides the data of what ever is visible at the time and does not meet what I need to do: =SUBTOTAL(1,E3:E24)
View 5 Replies View RelatedCopy Data From Workbook / Open Existing Workbook - Select Range And Paste
Mar 26, 2012Copy data from workbook, open existing workbook, select range and paste. But my copied data is lost.
Sub Select_Copy_Paste()
'
'
Windows("ElektroFunctiesDatabase.xlsm").Activate
Sheets("PowerSupply's").Select
Range("A2:I6").Select
Selection.Copy
[Code] .........
' Here i need to do something to paste data into r.address?
Copy A Range Of Cells In Another Workbook Opened In A Separate Instance Of Workbook
Feb 11, 2010I m not able to use the standard Excel Paste Special function when I copied a range of cells in another Excel workbook opened in a separate instance of Excel. Instead, Paste Special thinks that I have copied some non-Excel objects and gave me the Paste As options. This is not the case if I open both workbook within the same instance of Excel. Could you share with us if there is a trick to trigger the normal Paste Special options in such situations (without having to invoke Macro procedures)?
View 2 Replies View RelatedCreating New Workbook / Copying Sheets And Saving Workbook - Subscript Error
May 30, 2014Trying to create a new workbook from another open workbook, then copying all the sheets that aren't called "Summary" to that new open workbook and then saving it. I get a subscript error on this line:
[Code]....
Copying Data From Recently Opened Workbook To Workbook Where Form Is Present
Jun 16, 2014All i did was i just created a form to open a workbook from the directories.
Code to copy the data from that recently opened workbook to my workbook where I have my forms. I need to copy that data as in the case that i don't know the workbook and the sheet name i'am going to open as i may open any of the files!!
Refresh All Queries On Workbook Open When Finished Print To PDF Then Close Workbook
Jan 23, 2014I have data that daily needs to be refreshed and printed to pdf.
I figure the simplest way to do this would be to task schedule the workbook to open daily. Then on open it will refresh the data, print it after all data has been refreshed and close the workbook.
I set it up originally without the need to print so I have all the queries set to refresh when opening the file, however when I now try and put the code to print to pdf on the workbook open event it runs before the queries are finished running. (Query notes: queries were created through Microsoft query, and are accessing a MySQL database queries set to refresh when opening the file queries set to enable background refresh).
Pull Data From One Closed Workbook Into Active Workbook With Command Button
Feb 27, 2014I've been using the following code to bring in individual cell values from one closed workbook to an active one. I would like to modify this is possible to bring in multiple cells at once and also pull them into a different worksheet in the active workbook. Basically, my command button is on Sheet1 but I'd like the data to pull into a cell on Sheet2.
Private Sub CommandButton1_Click()
With Range("Q9")
.Formula = "='C:Users[Workbook Name.xlsm]Worksheet Name'! N27"
.Value = .Value
End With
SaveAs - Copy Sheet Into New Workbook And Save It In Same Folder As Original Workbook
Jun 17, 2014I have been trying to edit a code which previously saved a copy in a new workbook to a specific folder/path. (Additionally it copies and clears some figures, but this is working as it should.)
However I would like the copy to be saved at the same location as the original workbook, regardless of the path the original workbook is saved.
I.e if I need to move the workbook containing the code to a new folder/location, when using the macro, the new copy should be stored in the same folder/location as the original one.
For now it is only saving the copy into "My Documents"
Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 16-02-2009 by ceng
'
Sheets("Bunker ROB").Select
Sheets("Bunker ROB").Copy
ActiveWorkbook.SaveAs Filename:= _
ActiveWorkbook.Path & Range("D3"), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
[Code] ........