Using RoundDown Function In A Macro

Jul 27, 2009

I have formula as follows that I am trying to use in a Macro. It is working fine as a function by itself.

View 5 Replies


ADVERTISEMENT

ROUNDDOWN Function

Jul 27, 2007

I have a cell that is called "Number of containers", this cell affects my "Freight costs" and several other cells. I want this cell to never contain decimals, I have used the format menu and changed the number of decimals shown but that does not affect the number itself. It still uses decimals when calculated. I want this cell to use no decimals and also to always be rounded off downwards (since there can never be more than a certain number of items in a container). Can I use the ROUNDDOWN function?

View 9 Replies View Related

Convert Data With ROUNDUP And ROUNDDOWN Functionality Using VBA

Jan 8, 2014

I've got a data dump that must be edited/converted prior to pushing it into a different main frame system. There can be NO decimals in the end result. Here's the challenge:

______Col A____Col B___Col C___Col D____Col E
Row1__TOT_____1Q_______2Q_____3Q______4Q_____
Row2__20.00____5.00_____5.00____5.00____5.00___
Row3__26.00____6.50_____6.50____6.50____6.50___
Row4___0.00____0.00_____0.00____0.00____0.00___
Row5_139.00___34.75____34.75___34.75___34.75___
Row6__13.00____3.25_____3.25____3.25____3.25___

Starting on Row 2, there's a break down of 4 quarters of data divisible by 4 using the whole number TOTAL of Parts in Col A.

The analyst must first break it down into 4 parts (as done in Col B through E). Then, round up and down appropriately to ensure that NO DECIMAL data is entered into another main frame system.

Can't copy one formula downward because if it ends in .50, the up/down sequence is diff from how it would be rounded if it ended with .75.

Need vba to be smart enough to evaluate "IF" and apply "THEN" according to what it sees in Col B's data and convert B, C, D and E appropriately. (I will tie this to a button within a toolbar to run the macro/vba when the conversion of B thru E is needed.

EXAMPLE:
IF B's data ends in: xx.00 or xx.50, then convert those 4 quarter's data accordingly:
ROUNDUP, ROUNDdown, ROUNDUP, ROUNDdown, (this one doesnt really matter but works)

IF B's data ends: xx.25, then convert those 4 quarter's data accordingly:
ROUNDUP, ROUNDdown, ROUNDdown, ROUNDdown,

IF B's data ends: xx.75, then convert those 4 quarter's data accordingly:
ROUNDUP, ROUNDUP, ROUNDUP, ROUNDdown,

Using ROW 6 as an example for how to handle xx.25's:
B6 would convert up to 4
C6 would convert down to 3
D6 would convert down to 3
E6 would convert down to 3
TOTAL after conversion = 13

View 9 Replies View Related

Insert The Rounddown Formula Into This Formula

Jan 29, 2008

I use this formula for converting date of birth to a number based on today’s date. I have tried to have the number.. 6yrs 6 months to print only 6 not 7 years old. How do I insert the rounddown formula into this formula

=IF((AD37=""),"",YEAR(AQ36)-YEAR(AD37))

View 9 Replies View Related

Macro And IF Function Is True Then Macro 1 (hide) Is Used

May 20, 2006

I have 2 macros one hides a selection of rows and the other unhides them. What i want to be able to do is when an IF function is true then macro 1 (hide) is used. If the function is false then macro 2 (unhide) works. There is a code in VBA

View 3 Replies View Related

Run Macro From If(function)

Nov 7, 2002

if its possible to run a macro from an if statement as in
=if(weekday(A1)=5,Executemacro,"")

Or else how to program the result of a macro to a cell when a certain condition exists?

I already have a macro that takes data from an active cell with a running number:

Range("B5").Select
Selection.Copy
Range("E26").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Now I need to run this Macro when its friday (example)

Can't find it in the VB help files.

View 9 Replies View Related

Add Sum Function After Macro

Oct 6, 2008

I have found this code to spred my data from master worksheet to clients worksheets!

Is there a code to add the Sum function in the last row on each of the new worksheets in specific columns where is needed?

Sub ExtractReps()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("MAIN")
Set rng = Range("A:AG")

View 9 Replies View Related

Macro To Run A Function

Jan 31, 2009

What I would like to do is create a macro (use a shorcut key to activate ie crtl+q)or if there is a barcode I could scan to do that when the function is called for it would move down one cell then left 4 cells. I am looking to to this when walking around doing some scanning I could do that function rather than pressing the keys all the time.

View 9 Replies View Related

Macro For A Sumif Function?

Aug 19, 2013

I'm having a little problem with the function Sumif. I have data like this:

Date
0
0

Total
0
78[code]...

I want to some all the Totals that appear before the "End Notification" text. I have more totals after that text and the number of rows are variable.

In my data, I have always 4 totals before that text, so I thought perhaps that the way to do this would be to impose to the sumif function to stop summing after counting 4 totals. I didn't find a way to do that.

I thought a little more on the problem and I find out that the easy way would be to record a macro with the sum if function, where, in the range part, I would do a Find for "End Notification"; and select all the cells to the top of the sheet. The problem is that the macro dont record that part, only the row number.I think I need to turn the range a variable, where the variable is equal to the find selection.

View 9 Replies View Related

Custom Function To Run A Macro

Aug 11, 2004

I am wanting to create a custom function that i can enter into a cell to run a macro (MyMacro). I do not know how to write a function, but so far i have:

Public Function Run(MacroName As String)
Application.Run MyMacro
End Function

View 6 Replies View Related

Discount Function In Macro

Mar 30, 2007

I have a quote system which takes data from tabbed sheets if there value is greater than 0, then does a simply formula which is then times by a discount which found by searching for the sheet name and then attaching the appropirate discount. In short this means that the whole forumale doesn't work and is not displayed

View 11 Replies View Related

Writing VBA Macro Or Function

Nov 5, 2007

I have Asset Class in Column I and if it is 364A-EL or 365A-EL or 368A-EL or 368B-EL or 369A-EL or 371A-EL or 371B-EL or 373A-EL or 397C-EL then 'POLES' assigned in to Owner Class (Column J).

If Asset Class is 366A-EL or 367A-EL or 367B-EL or 368C-EL or 369B-EL or 371C-EL or 373B-EL then assign UGCBL to Owner Class.

Should we write the macro or can it be accomplished by any Excel Function?

View 11 Replies View Related

How To Use The Count Function In A Macro

Feb 21, 2008

What i am trying to do is count the number of rows that contain data.

It will always start in A7 and when creating the macro i hit control-shift-down arrow.

There will be a differnt number of rows for each data set. When i ran the macro it kept the result of the first data set that i used to create the macro.

here is the

View 9 Replies View Related

Add An Ontime Function To A Macro

Oct 17, 2008

I'm using this to unhide rows one at a time:

View 4 Replies View Related

Using The Function ISNA In A Macro

Nov 4, 2008

It then places all the data on a new sheet. My problem is that some of the formula that the macro places on the new sheet come back with the result #N/A - which is fine, as they are the result of a lookup.

What I want to try and do, is at the end of the macro, get it to delete the row if the activecell is showing as #N/A

I've tried to write a small bit of code for it...

View 10 Replies View Related

Using A Vlookup Function In A Macro

Dec 18, 2008

I am trying to use the Vlookup function in a macro but I can't figure out how to write it. I am using a controlled loop to cycle through my data. So for the first entry, my lookup data is in cell A2. The lookup table is located in a sheet called 'Trade Table' and the lookup field is in Column B and value needed is in column C. The results go in column G. The excel formula is as follows:

View 2 Replies View Related

Function SpliText In A Macro

Mar 31, 2009

Two months ago, DonkeyOte help me (http://www.excelforum.com/excel-prog...e-formula.html) solve a problem, with this function:

View 6 Replies View Related

Find Function Macro

May 11, 2009

Attached is a sample in which Column"B" contains Total IDs and Column"D" contains worked labour IDs. Now I want the IDs of labour who have not worked in Column"F".

View 11 Replies View Related

Counting Up/down Macro Function

Jun 6, 2009

Is it possible to create a macro linked to a button that, once pushed, will cause a particular cell to count up from zero? I would also like to be able to have a second button that would cause the same cell to count down!

View 2 Replies View Related

Using The Regression Function From Within A Macro

Jul 9, 2009

I am wanting to write a macro which uses the excel multiple regression function (a part of the data analysis add-in). I tried recording a macro while I selected the regression function (Tools> Data Analysis... etc.) which produced the following:

View 3 Replies View Related

Macro To Execute F2 Function Key?

Feb 3, 2003

I need a macro that will execute the F2 function key so that the cell can be edited.

View 7 Replies View Related

Using Date Function With Macro?

Mar 25, 2012

I have a sub that copies a section of my sheet onto another sheet and then clears out my sheet so I can type in the sales numbers for the next day. Now I am trying to figure out how to get the date to change.

What I would like to do is to have the Macro type the date MM/DD/YYYY into cell C9. The date should be whatever date the computer clock is set to minus 1 (since we are filling out yesterday's sales). The other part that I am struggling with is making B9 appear as the specific day of the week.

The key part of this is, C9 may have the date entered in manually and if so, I would like to be sure that the day of the week matches the date entered if at all possible.

View 6 Replies View Related

Left Function In Macro?

Oct 1, 2012

I have the below line in a macro but the left function is not working, will not sum everything.

Range("g" & x).Value = "=SUMPRODUCT(--(Data!a2:a65536 = """ & val1 & """)*(data!t2:t65536=left(g1,2)))"

View 9 Replies View Related

Function / Macro To Return Above Row

Jul 25, 2013

New to creating macro's but looking for a macro or a function to get the below results. The first 2 columns are givens and looking to return the 3rd column. Is there any code that can do this?

Hierarchy
Path
Expected results
000000010HIER_NODE

[Code]....

View 4 Replies View Related

Move Function In Macro

May 14, 2008

I have a piece of my macro that creates a chart on the second sheet in my workbook.
However, it tends to appear way off to the right, and then I have to readjust it every time to be where I'm working (columns A-D approx.)

Can I improve the move to specify it to place the graph in a certain
All I'm currently doing is:

ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"

View 9 Replies View Related

Transpose Macro Or Function

Sep 25, 2009

What im trying to do below is transpose the data under each name in column A across the corresponding row the name is on. Is there a way, without copy and paste specialing, to make excel recognize the cell being a name (perhaps from the list of all names on sheet 3 (Data Range = "Names") and transpose the data under it so it ends up light the highlighted text in the image below? Im thinking a macro?

View 9 Replies View Related

Match Function In Macro

Nov 15, 2006

its says unable to get the match property

Me.doj.Value = Application.WorksheetFunction.index("A4:H574", Application.WorksheetFunction.Match("A4:A574", Me.id.Value, 0), 6)

View 4 Replies View Related

Msgbox Function Outside Of A Macro

Jan 12, 2007

is there a way to use the functionality of 'MsgBox' but within a function instead of a macro? I use: MsgBox "You must enter a Acquisition Date", vbCritical, "Error Message" now I would like to use it within a funtion.

View 2 Replies View Related

SUMIF Function In VBA Macro

Sep 18, 2007

I have a macro[A] that sums a column based on parameters passed to it from another macro[b]

Macro A

Sub SumColumns(varASheetName As Variant, varFormulaCell As Variant, varSumRange As Variant)

Sheets(varASheetName). Range(varFormulaCell).Value = Application.WorksheetFunction.Sum(varSumRange)

End Sub

' Macro B

Sub SumDebtorsStuff()
Call SumColumns("DebtorsRaw", "D10", Range("D21", Range("D65536").End(xlUp)))
End Sub ...........

View 9 Replies View Related

Change Macro Using InStr Function

Aug 30, 2012

I want to change my existing macro using InStr function in such a way that when the columns are found then it add the corresponding values. The addition of values have already been done. I just want that if similar values are found then it show the results.

The example workbook with macro is attached : comparestrings.xls

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved