What would the formula be to complete all three of these functions in one cell?
64
-3
61
When positive & negative then G1+T1=X
-8
-8
0
When negative & negative then G2-T2=X
3
2
1
When positive & positive then G3-T3=X
Whats the best way to get a complete list of the properties of a given cell?
Possibly this is documented somewhere if it's always the same, or maybe it varies and there's some sort of collection you can iterate through.
I am trying hard to use excel macro to round up cell value to next complete figure when it reaches to 0.6, however it should show the first five "0.1, 0.2, 0.3, 0.4, 0.5 exactly. I am giving increment of 0.1 digit to A1 Cell, it is going well. I am unable to get the next complete figure when 0.6 comes. Is it possible in macro.
View 3 Replies View RelatedI have a formula that calculates the number of rows in a spreadsheet. I would like the results of that formula to complete a range description. For instance, if the range would be expressed as A1:A20 (for a file with 20 rows), and the result of the formula (the cell enty) for a different file is 35, I would want the range to be expressed as A1:Ax, where x is equivalent to the result of the formula (effectively A1:A35, but expressed with a reference to the cell with "35" in it, not the 35 itself).
I have a spreadsheet with date and time values of the format
"dd/mm/yyyy hh:mm" in column A followed by some other data in cells of that row.
What I'd like to do is have a macro that will delete a complete row if the time value in column A lies between two times that I can specify in the macro (the dates are irrelvant)
I found this code for an auto-complete function from this website: [url]
It works well, but see the web example before using it. You have to name a range MyList with your potential values for it to work.
Currently, you have to click the drop-down arrows to see the potential entries. I want to make it so that after the user enters the first 3 characters it will automatically "drop-down" and show the potential entries. This will save a trip to the mouse and let the user see whether their entry is in the system.
Option Explicit
Dim i As Long
Sub KeyEventOn()
For i = 65 To 90
Application.OnKey "{" & i & "}", "'MyValidation """ & i & """'"
Next
End Sub
I would like to have excel automatically fill an excel cell with a predefined list. For example, if you put the letter l it will automatically include the word large. This is like you do when you fill out a form online. See attachment
View 2 Replies View RelatedCan I retrieve, from VBA, the exact value of a cell?
If in A1 I write 0.12348 with general, number or text format, from vba with MsgBox ActiveSheet.Cells(1, 1) I obtain 0.12348.
If in A1 write 0.12348 with currency or accounting formats, from vba with MsgBox ActiveSheet.Cells(1, 1) I obtain 0.1235.
Naturally, I need retrieve complete values from cells with format accounting?
My Excel 2010!
is there any way to remove/deactivate the cell's auto complete year function?
eg: input the value "03-jan" and the cell will automatically convert the value of the cell to 03/01/2008
I am trying to combine the following two function into one cell. The second column contains a "space" before the number. The third column is the result. =CONCATENATE(Q2,",",+R2) and =SUBSTITUTE(K2," ","")
Here is a sample table with showing the data and result
4
5L
4,5L
5
6R
5,6R
5
7L
5,7L
6
10R
6,10R
6
3L
6,3L
I've been struggling with the following problem for hours now and getting nowhere fast.
I've got '=(100/X6)*L6)' in cell M6 which gives my percentage fine but I need to round to the nearest integer with '=ROUND(M6,0)' Question is how do I combine both functions to provide the nearest whole number in M6 ??.
I made a drop down list in cell H11 for Marital status (married,defacto,single,widow). Those are the 4 options in the drop down list.
Now im trying to make in another cell IF (H11="Married" OR "Defacto,950,false)
but wont work, i know im wrong but i learn by trial and error or last resort MREXCEL and m ytrial and error didnt work.
I'm looking for a formula that can do 2 different functions in one cell. I have a subtotal in A1 and my total in B2. Now, I want B2 to say if A1 is lessequal to $100,000 then multiply by1.5% plus A1 and if greater than $100,000 muliply by 2.5% plus A1.
View 9 Replies View RelatedWhen using watch window to watch a formula that has multiple formula's in it, is there a way to see the result of 1 formula within the big formula?
E.g. say you have:
= SUMIF(A1:A11,"207",B1:B11)/COUNTIF(A1:A11,"207")
And you put a watch on that, but in watch window it shows the result of the whole formula, say if you want to watch only the SUMIF part?
Im using Excel 2003 by the way.
I'm putting together a table which shows monthly costs versus Total costs. Is there a way I could use if two choose between two option dependent on the value of cell A1.
Example
A1 = "Totals", multiply Subtotal in A13 by 12.
A1 = anyother value multiply by 1.
Something like:-
=if(a1="totals",(a13*12), =if(a1<>""sum(a13*1)
i have wrote the following code snipped:
Sheets(2).Activate
Sheets(2).Range("D8").Value = Application.WorksheetFunction.Subtotal(109, Sheet(4).Range("BJ3:BJ" & b)) + Application.WorksheetFunction.Subtotal(109, Sheet(3).Range("BJ4:BJ" & a))
The debugger said: That "Sheet" is not allowed in the subtotal-function. because i would like to calculate two subtotals in two sheets an write the sum in another sheet
I have six complex functions, which I would like to place in [A2] depending on what is placed in [A1], The functions themselves are quite extensive and I don't want to use any active functions (like INDIRECT) as this will cause slow calculations. [A1] will just be a given name to each of these functions.
View 3 Replies View Relatedreferencing constant cells in custom functions. This is a sample of my code:
Code:
Select Case Name
Case Is = "Ball"
Valve_Days = [V4] + Cushion
Case Is = "Check"
Valve_Days = [V5] + Cushion
Now, the problem is when I open the workbook up, or someone downloads the file from the server, it resets all fields to div/0 based on what sheet the last user 'Saved' the document on. I think this is occurring because it doesn't know which sheet to pull the value V5 from? The problem never started to happen until I added another sheet in the workbook. Is there a way to tell VB that it isn't just cell V5, but it is cell V5 on Sheet 'X'?
I am aware of the following topic in the VBA Help file:
"Using Microsoft Excel Worksheet Functions in Visual Basic
You can use most Microsoft Excel worksheet functions in your Visual Basic statements. To see a list of the worksheet functions you can use, see List of Worksheet Functions Available to Visual Basic.
Note Some worksheet functions aren’t useful in Visual Basic. For example, the Concatenate function isn’t needed because in Visual Basic you can use the & operator to join multiple text values."
And I'm aware of how to call Excel funcitons from within VBA; e.g., answer = Application.WorksheetFunction.Min(myRange)
However, not only are some Excel functions not useful; the fact is they cannot be used because VBA has a native function that does exactly the same thing and you have to use that native VBA function to achieve your goal. It is these overlapping functions that I am especially interested in. I want to know what I should use directly in VBA and what I need to go to Excel for.
I have an issue new client is asking for me to present my reports with a percent complete. We do survey work and all our date is in Ft. The way my file is set up right now i have H5-H100 as incomplete footage, Column I5-I100 has my completed footage and to keep it simple Row 13 A-k show total amount of footage. He wants to see everyday when i submit the % complete based of footage incomplete and footage completed.
Anyone able to help me out with a formula for this? I tried messing with it a little, wasn't really able to get it down and my Excel skills definitely aren't past beginner level.
Excel cannot complete this task with available resources. Choose less data or close other applications. My file is only about 3mb in size, made up of 17 worksheets. These sheets are calculated by referencing to another file that contains all of the background data. The data file is also about 3mb, made up of 13 worksheets.
There are probably about 2 - 3 thousand formulas in the file in total, ranging including vlookups, sumifs, sumproducts, etc. When the data was contained within the file there was no problem. I moved each data sheet into a new workbook to trim the size of my file and also stop the incessant calculation and this is when the problem started. Now, when I open the file and am prompted to update, it will update to about half way and then throw up the error message!
I have desinged an excel invoice, but I think I might want it to do the impossible.
I have my companies database as a .csv file, is there any function where I can enter in the company account number and it will automatically fill in the company name, address, contact etc like a mail merge type thing and for it to work vice versa enter the company name and it fills in the account number and address all automatically taken from the information on the .csv file?
I want to enter a name in a cell from a list of 70 names I have on another work sheet. By just typing the first few letters of the name into the cell I need it to complete the entry. I do not want to use a drop down box it takes too long to find the name. I need a formula to do this for my spread sheet.
View 6 Replies View RelatedI have a file that i need to use for analysis but it is currently a text file, how can i use VBA to open it with excel and then complete text to columns, using a delimiter of a semi colon.
I have attached a sample of before & After.
I have a form I'm building that I want to put on the server to allow different people to complete as needed.
As they complete this form I want to take all the info they entered and have it go into a data base once there I want to clear the form for the next person.
Is there a way to add auto complete to this Macro so that when a user starts typing in a cell in column A it auto completes.
[Code]....
The idea is that a button on the worksheet 'Sample Info & Results' runs the macro which copies the worksheet to a new file with the name specified in the cell B3 and saves it as a csv format with ";" as the separator. For the most part it is working with the exception that the export stops at row 6 for some reason I dont understand. The code is not mine (credit to T.Thielmans on stackoverflow). code for 1) referencing a folder location within the original file to use as a standard location to save the csv file and/or 2) removing hidden columns.
Code:
Public Sub ExportToCSVFile(FName As String, Sep As String, SelectionOnly As Boolean, AppendData As Boolean)
Dim WholeLine As String
[Code]....
I am attempting to use VBA to open a webpage and then login. Below is the code I have so far:
Code:
Sub Test()
Dim IE As Object
Set IE = New InternetExplorerMedium
With IE
.Visible = True
.navigate ("url")
[Code] ........
Here is the HTML Code of the website:
<form target="_top" name="Logi<wbr>n" method="POST" action="/login.fcc"></form>
<label for="firstname">User Name</label>
<input class="textbox2" onkeypress="javascript:processkey(window.event)" name="USERNAME" type="TEXT">
[Code] ........
An error occurs within the second With, stating that the object variable or With block variable not set.
I have a worksheet named, "Database2". There are hundreds of names in colum B starting in row 3 of this worksheet.
In the same Workbook is a worksheet named, "Records". I want to create a userform with a combo box, an "Enter" and "Cancel" command button. When typing begins in the combo box I need it to recognize the name from the "Database2" list in colum B and executes auto complete. When the "Enter" button is clicked I need it to enter the name in the first available row in colum B starting in row 3 in the Worksheet named "Records".
I have some Worksheets in which cell A1 contains a textual heading for each Worksheet.
On each Worksheet, cells in the range A2 to O5 containg headings; the data goes in rows 6 onwards.
In VBA script, I'd like to autofit columns A to O, but starting at row 2 so the text in A1 is not included. For each sheet, I can determine the last row, to get a "range" to autofit (e.g. "A2:P15").
I've run a search on Autofit; the only examples I can find autofit the whole column or set of columns.
Is it possible to autofit starting at a specific row?