In A1 I have the value 60 I want it in 1:00:00 format
What I do is, I divide that by A1/24*60 and the copy that and paste-special-value on A1
Is there any way to convert the number 60 into 1:00:00 format without having to do calculation in temporary columns?
Im wondering if this can be done in excel..
2 columns
if the value in column O=$0.00 then I need to divide the adjacent cell in column L by 0.3 or multiply by 30%
this is the way I was trying to do this...
=IF((O2:O6722)=0,DIVIDE(L2:L6722).3)
I have tried it several different ways and cannot get it to work - is it my formula or can this even be done because I can't reference just one cell?
I may not even be calling it the right thing. but let me explain what i'm looking to do. i've got three columns on a spreadsheet and i need to be able to add two columns together and then divide the sum by another column. so something like this:
4+4/636 = 79.5
d2+f2 (divided by) h2 = 79.5
I'm trying to divide the value in column A by the data in B:AO using VBA, however I need to repeat this for many rows (the values differ for each row).
There could also be a different amount of rows with data in each time, and there is also a single blank row after every 18 rows with data.
I am trying to loop through Column Q2:Q2000, and divide the adjacent Row R2:HS2 by the value in the corresponding Q column.
ie. cells R2: HS2 should all be divided by Q2
cells R3:HS3 should all be divided by Q3
etc until R2000:HS2000 are divided by Q2000
I presume that i would require an initial loop to scroll through the column and a seperate loop to scroll through the row values, but i cannot get my head around how this is done.
I have attached a sheet as an example, as i am trying to Divide a cell by the number of populated cells in that column e.g column (A) has a table of 16 but only has 13 populated cells in that column, the sum would be something like this =sum(A18/13. Sometimes the column will have less and at times more populated.
test sum skip blank cells.xlsx
When opening 1 of my files (made to be used by others) a lot of .tmp and .emf files are made. When I close the file most, but not all, are deleted and new ones are added. Simply deleting all .tmp/.emf files doesn't work because some are still in use (by other files).
There for I have made a bit of code that checks if direcory "C:...Temp|Temp_PandL" exists. If not, then it will be created:
The form is completed by the project leader, then they hit a Command Button and it's sent to the Senior Manager. After the Senior Manager looks at it and approves it, he then sends it on to Admin (me!) so the project can get set up on all of our systems.
We recently upgraded to Office 2007 (yes, we are a bit late!) and now the Macro that sends the form to Admin no longer works. An example of the error is as follows:
Cannot run the Macro "FY06-10 PROJECTS 19-Jan-10 08-32'EmailtoAdmin.EmailtoAdmin'. The macro may not be available in this workbook or all Macros may be disabled.
Here is the code that I use to send it to the Senior Manager:
Sub MailtoSM()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set Sourcewb = ActiveWorkbook
ActiveSheet.Copy
Set Destwb = ActiveWorkbook
With Destwb
If Val(Application.Version) < 12 Then
FileExtStr = ".xls": FileFormatNum = -4143
Else.........
Right now the security settings on my server are set so that new files can be added by anyone but only a few people can delete them. Everytime Excel opens a file on the server it creates a temp file that it cannot delete due to the security settings. This means that I have to go in to each subdirectory and manually select then delete each of these temp files.
way to identify an excel tempfile, or a method for deleting these files with VBA?
I am trying to export a chart using the code below, the loops change the data that is used to produce the chart:
Dim strFilename As String
Dim strExt As String
Dim lngProfile As Long
Dim lngOption As Long
For lngOption = 1 To 3
Range("nmOption") = Choose(lngOption, "Existing", "Option 4", "Option 5")
For lngProfile = 1 To 4
Range("nmProfile") = "Profile " & lngProfile
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveChart.ChartArea.Select
strExt = "gif"
strFilename = ThisWorkbook.Path & "Images" & _
Range("nmOption") & "_" & Range("nmProfile") & "." & strExt
ActiveChart.Export strFilename, strExt
Next lngProfile
Next lngOption
Each export command creates a file with the correct name but no data (0kb in size) and a temporary file that is the correct file type (I have tried both jpg and gif).
I could try and copy the temporary file, but why does Excel not finish the job? I have tried to put a loop in and also added DoEvents after the export but no joy.
Using Excel 2003 and cannot copy a worksheet and am getting this error:
File not found: CDocumentTempVB4B.tmp
Code to copy the sheet that get selected by if-then-elseif statement and paste in a new workbook, save the workbook as temp, email it and delete it,
sub sending_mail
Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim DataWB As Worksheet
[Code] ......
I have a workbook with 4 worksheet that store different type of data. It also has a userform that load at start of the application which is to search the data in the workbook. The userform has a combobox where the names of the sheets are stored. when the user selects say Sheet2 in the combobox, it enables the relevant textboxes on the userform and activates the worksheet at the change event. The userform has a search button that searches all the worksheets based on the text entered in a textbox.
The problem: how to search based on 1 textbox. What I want is: say for e.g the end-user selects sheet2 from the combobox, this intern enables 4 textboxes (Name, DOB, Nationality, ID #) on the userform. The end-user should have the liberty to enter data in 1 and/or any of the textboxes. The search should be performed, that if data is only in 1 of any of textboxes then give all rows that fit that criteria and display in a temp worksheet. if say the name and dob is filled by the user than what matches both should be displayed in a temp worksheet. if say dob, name and ID# given so the search button should narrow down to fit all 3 criteria and then display result in temp worksheet. As if mentioned data can be entered in either just 1 or any or all textboxes.
E.g. the worksheet is (Columns are Name, Nationality, DOB, ID#)
row 1 = name: Steven Martin, DOB: 27-may-1993, Nationality: Trinidad & Tobago, ID #: 1234567
row 2 = name: Gary Richards, DOB: 2-FEB-1993, Nationality: British, ID #: 456789
row 3 = name: David Cohen, DOB: 27-May 1993, Nationality: American, ID #: 98765
row 4 = name: Roberto McDonalds, DOB 21-Jul-1962, Nationality: British, ID # 654321
row 5= name: Gary Richards, DOB: 01-Dec-1978, Nationality: Australian, ID # 1234567
Now if the user enters only name as "Gary Richards" and search then row 2 and 5 should be displayed in a temp worksheet. if user enters name Roberto McDonald and ID# 1234567 then it should not display anything. if user enters DOB 27-may-1993 and nationality British and ID # 1234567 then as well shouldn't display anything and should a msgbox "no data found".
I have a spreadsheet where I want to require certain fields to be completed then I want to have that file auto emailed. I have learned that I do need to have the file saved before sending otherwise the data will not appear in the email, so with this I want to have the file temporarily saved emailed then the temp file deleted.
Here is the code I have so far but it errors on the blue text, I did change the TempFileName from = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") to = [C16] & "_" & [B6] & "_" & [D6]
Private Sub CommandButton1_Click()
If Range("B6").Value = "" Or _
Range("d6").Value = "" Or _
Range("f6").Value = "" Or _
Range("E9").Value = "" Or _
[Code] ......
Dropbox - Final.xlsm
Here is the above link. Am looking at a button which saves whatever is the temp worksheet row in the customers worksheet. The temp worksheet basically takes the data from the Quotations worksheet and places it in a row.
I have conditional formatting on a cell based on a formula, when I entered the solution of "if then 0 " but the cell doesn't see the result as a 0, if I manually enter the 0 the conditional format works.
I even tried putting the formula in another cell and then referencing to that cell, still didn't work...
The "divide by zero error won't trigger the conditional either......
[Code] ....
If I have a value of lets say 53, I need it broken down into equal and whole numbers across the spread sheet with the last one being the remaining value.
Currently for example in cell F5 I have a value of 41 that I need to break down so
I have done the following:
In Cell P5 =IF(F5>=25,F5-21,F5)
Then in Cell Z5 =IF(P5>=25,P5-21,P5) and this goes on until I have zero value from the original value in the last cell can i not do this all in one formula and have the results populate in other cells.
I am creating a 'weekly average spreadsheet' (Excel 2007) for a teacher. It has a column for each student's name and his test grade for each day (M-F). The last column gives his weekly average.
She also needs an average for all the students' for the week (vertically). My formula is correct, i.e., =AVERAGE(H2:H11). However, I'm getting a divide by zero error because I have some blank rows above my formula (so she can add new students' names as they come.
How can I get rid of the divide by zero error so she can see the running averages as she inputs grades (but still leaving empty rows for new names)?
I have just started to try and learn VBA for excel. the resource I have been using are books and the WEB. Some how, not sure how there was/is some code where all enteries in all worksheets are divided by 100, i.e if I enter 2222 after clicking the enter button the result in the cell is 22.22. I have deleted all macros and code however the problem still exist.
View 3 Replies View Related
If CheckBox1.Value = True Then
Range("e25") = ComboBox6.Value
ElseIf CheckBox2.Value = True Then
Range("e25") = ComboBox6.Value / 2
End If
Im getting an error on the second to last row, Ive never tried to divide using VBA before and I have no idea how to!
Also the amount im trying to divide will always be in the format hh:mm
I have a database that requires me to find the average of some data over several columns, however if one of the values I have to divide by is a zero I get a 'Can't Divide By Zero Error' is there any way to override this ? So that if a Divide by Zero Occurs, instead of returning a 'Can't Divide By Zero Error' it just replaces it with a Zero instead ?
View 6 Replies View RelatedIs it possible for a macro to rename a macro? I have a Temp macro that I want to rename to Auto_Open (or maybe auto_close).
I have minutes of meeting and have to divide them into different sheets against completed, and different departments and dept HOD responsible for it. How do I do it without "Copy/Pasting" the code? Also I have to remove the ones that are done. Any macro or something that can be written for these.
format of sheet
Sl No. | Date | Remarks | Dept | Dept Head 1 | Dept Head 2 | Status
I have to get data into different sheets against Dept, Dept Head 1, Dept Head 2 and remove the "Done" status to different sheets.
How do I do it without manual labor?
I have 10 years from 2003 to 2012 and need to divide this into 2 categories pre-cisis and post-crisis. How can I do that?
View 3 Replies View RelatedI am working on my thesis empirical part now I have 10 years from 2003 to 2012 and need to divide this into 2 categories pre-cisis and post-crisis. How can I do that?
View 10 Replies View RelatedExcel has a QUARTILE function to divide a population into quartiles. I'm trying to do something similar but divide a population into deciles (i.e., every 10%). Is there any easy way to divide a large population into deciles and to assign a decile to each value in the population (i.e., top 10%, 11-20%, 21-30%, . . . bottom 10%)?
View 2 Replies View RelatedI have a data extract that gets generated at the end of each month that I post into the 'Full Extract' tab on my example spreadsheet.
writing a macro that will divide the various items in the 'Extract' based on the Reference, (some items may need to go into multiple sheets)
then draw down the formulas that need to be applied to the item.
I'm lost on VBA, but I have been successful at recording a few extremely simple macros. I have a number of pdf statements that I have run through OCR, and the OCR often misses the decimal point so that 43.85 comes out as 4385. (this is in dollars, so everything has 2 decimals).
These cells are usually pretty easy to spot, so I want to select that cell, press a keystroke or 2, and divide the value in that cell by 100. I assumed that by selecting "relative cell references", and recording a macro with the keystrokes "<F2>/100<HOME>=" (without the quotes, of course), I would accomplish that. But instead, it places a constant in that cell, with the value obtained in the cell where I recorded the macro.
=1/25%
is it that you want a UDF
Function MyFun(a,b)
MyFun = a / b
End Function
Usage:
=MyFun(1, 25%)
Mangesh
"Gonecoastal1" <Gonecoastal1@discussions.microsoft.com> wrote in message
news:B257DADD-0042-4CF8-88DF-1D17AEAFF627@microsoft.com...
I really struggle with the following scenario:
A1=5 B1=4 C1=0 D1=0 E1=5
What I want to do is to divide the sum of those cells with the number of cells with a value > 0 (but all cells must be controlled to check if the value > 0).
In real life it's easy to see that I want to do 14/3 (A1>0, B1>0 and E1>0 equals three cells), but how to write this formula in Excel?