Setting If Function Argument In Macro

Sep 12, 2006

In the code bellow I would like to automate a if function until the 1st emptycell in the row 7 of my spreadsheet.

In my IF function, I would like to refer to a fix cell ("B1") while the other argument is in the same column as the function but two rows bellow.

My problem is that I don't figure out how to refer to a fix cell with the syntax I would like to use t run the macro.

I think it's more clear when you'll see the code

Sub Face2face()
Range("B5").Activate
Do
If ActiveCell.FormulaR1C1="=If(R[2]C>=Range("B2"),Range("B2"),R[2]C)"
ActiveCell.Offset(0, 1).Select
Loop Until IsEmpty(ActiveCell.Offset(-1, 0))
End Sub

View 8 Replies


ADVERTISEMENT

Function Argument

Sep 25, 2008

how to create a function argument containing my own defined formula?

View 9 Replies View Related

Multi Argument IF Function

May 23, 2008

formula to calculate the commission based on 9 cases each case is subjected to 4 differant arguments.

here is the formula for the first case (the answer if true 1.4) & the last case( if all the casses are fails it should show 0.6) u will find it in cell K5

=IF(AND(E5>=20,H5=1,I5>=80,J5>=80),1.4,0.6)

this is only 1 case HOW can i calculate it basd on 9 casses??

View 10 Replies View Related

Return Function As An Argument

Jan 23, 2008

When I try a MATCH function, I substituted the range through the ADDRESS function. But, it returns #VALUE error. When I manually typed the same range address, it produces the results. I've been behind this more than 8 hours still I can resist the heat on my cool head! But, I thought you coolest head guys need some heat to warm up for to-day's challenge.

View 9 Replies View Related

Function Argument's Cell Adress

Sep 16, 2009

I have an attached file and I am trying to build a VBA function to calculate total values. First row is "RollingTime" and for example if I am trying to calculate the "Total" value. For "RollingTime" = 2 it should be

RollingTime(2)*Percentage(2)+RollingTime (1)*(1-Percentage(1))*Percentage(2)+RollingTime(0)*(1-Percentage(0)*(1-Percentage(1))*Percentage(2)

Which is 109732508*0,3 + 1017508995*(1-0,2)*0,3+1587172158*(1-0,1)*(1-0,2)*0,3

And here is the code I have tried to produce:

View 10 Replies View Related

Dir Function - Invalid Procedure Call Or Argument

Jun 25, 2013

When I step-through my code below, it always opens the first file in the directory "C:Pyramid Files", but when it comes back to the Pyramid Files sub after fully processing the first file via various other subs, the VB Editor apparently doesn't like something about this line: StrFile = Dir(), since it quits after "snapping-back" to the previous sub Initialize(). I have also tried StrFile = Dir, but that doesn't work either. I did Dim Strfile in the General Declarations. When I set Watches for Dir and Dir(), I get the value "Invalid procedure call or argument" for both, as if the directory function lost the value. I can't determine why this is happening.

VB:
Dim WSM As Worksheet, WSB As Worksheet, WS1 As Worksheet, [U]StrFile As String[/U], StrDirectory As String, ClientCode As String
Dim Filename As String, LastRowb As Long, LastColB As Integer, LastRow1 As Integer, NextRowC As Integer, x As Integer, y As Integer

[Code] .......

View 4 Replies View Related

Using Multiple Criteria In Second Argument Of CountIfs Function

Feb 26, 2014

Need to use CountIFs formula to evaluate multiple conditions in the same column. Here is the formula I tried. It works for Resolved but returns 0 when i try to add Duplicate.

=COUNTIFS('P12 Source'!H:H,A18,'P12 Source'!F:F,"Resolved,duplicate")

Also used this formula with success by referencing a cell that had "Resolved" in it but when I tried to add another cell with "Duplicate" it again returned 0.

=COUNTIFS('P12 Source'!$H:$H,A19,'P12 Source'!$F:$F,'P12 Source'!$F$75)

View 8 Replies View Related

Reading Function Argument From Separate Cell?

Dec 3, 2012

I have the following problem: I have this formula, that works correctly:

=SOMMA.SE('[myfile_12112012.xlsx]RIEPILOGO'!$C$2:$C$12;E1;'[myfile_12112012.xlsx]RIEPILOGO'!$B$2:$B$12)

I wish I could read the name of the file (myfile_12112012.xlsx) or, alternatively, all of the function's first argument (('[myfile_12112012.xlsx]RIEPILOGO'!$C$2:$C$12) from a separate cell.

I tried this:

SOMMA.SE = (C2, E1, '[myfile_12112012.xlsx] ERROR SUMMARY'! $ B $ 2: $ B $ 12)

putting in the cell C2 the string:

'[myfile_12112012.xlsx]RIEPILOGO'!$C$2:$C$12

In this way, however, the formula does not work.

How can I read and evaluate correctly the first argument from the cell c2?

View 2 Replies View Related

Define Argument Prompt On Function Module

May 6, 2009

I have made my own excel function that my users needs every day. it works just fine, however if you do not know the required argument for this function, you couldn't use it. How can i prompt my users for the right order of the argument when they type the function in excel?. example is when you type the Function "=left(" you will be prompted by excel of the correct arguments.

View 3 Replies View Related

Preventing A Function Being Called If Argument Is Unchanged

Jun 27, 2006

I have a function in a cell triggered on the value returned by a DDE link in another cell.

e.g. cell A1 contains =function1(A2) and cell A2 contains
=ADVFN|NYSE_CAT!CUR

function1 is triggered each time the DDE link updates regardless of whether the result returned has changed or not. How do I prevent function1 being triggered if the returned value has not changed?

View 9 Replies View Related

Invalid Procedure Call Or Argument In Function

Aug 30, 2012

This error message in line vpp:

Invalid procedure call or argument

Code:
Function fn1(ByVal a, ByVal i, ByVal e, ByVal N, ByVal w, ByVal ta)
Pi = Application.WorksheetFunction.Pi
mhu = 398600
vpp = (mhu / Math.Sqr(mhu * a * (1 - e ^ 2))) * (-Math.Sin(ta * Pi / 180))
fn1 = 2 * vpp
End Function

View 9 Replies View Related

Translating Argument Of A Function Between Versions Of Excel

Jul 2, 2013

When I attempt to send a spreadsheet using a German version of Excel, the following formula does NOT work properly.

=ZELLE("adresse",C14)

The function gets translated into =CELL("adresse", C14), and what I need is, obviously, = CELL("address",C14). The argument does not get translated.

View 1 Replies View Related

Using Array For Column Or Row Argument In INDEX Function?

Dec 28, 2013

Can we use an array like {1;3;4;5} as Row or Column argument in INDEX function? Like shown below

=INDEX($A$1:$E$10,0,{1;3;2;4;5}) OR =INDEX($A$1:$E$5,{1;2;4;5;3},0)

View 9 Replies View Related

Determining Data Type Of Argument In Custom Function

May 29, 2007

In VBA, True takes the integer value of -1. However, in Excel, it is 1.

I am writing a custom function that does arithmetic on a value. However, due to above, if the value is True, my custom function produces a different result than if I wrote an Excel formula to do the same thing.

Is there some way to identify the data type of a value passed into a function? Currently my function looks like this:

' Return the valueToIncrement with its magnitude increased by PctToAdd
Public Function INCREMENTPCT(valueToIncrement As Variant, PctToAdd As Variant) As Variant

INCREMENTPCT = valueToIncrement * (1 + PctToAdd)

End Function

Ideally I would like to identify whether the type is Boolean and adjust the value so as to give the same result as an Excel formula would give.

View 8 Replies View Related

Macro To Have Multiple If Argument

Nov 7, 2011

I am trying to write a macro that has multiple if arguments.

An example is as follows:

If cell b11 had fill colour red, then colour fill cell B8 green,
If cell b11 had fill colour green, then colour fill cell B8 red,
If cell b11 had fill colour yellow, then colour fill cell B8 blue,
If cell b11 had fill colour blue, then colour fill cell B8 yellow,
end if.

View 5 Replies View Related

Call Macro With An Argument

May 7, 2007

i want to create a lot of buttons, one in each line, that, when you click on them, open a file. The filename is in a cell in the same row as the button.

Of course every button should open a different file, and that is the problem. As far as i know (not very much) the buttons can only call other subs, and not with an argument.

What should i put into the "onAction" property of each button, so that each button opens a different file? I really dont want to create hundreds of subs just to get this done, there must be another way.

PS: By the way, how can i delete all but one buttons of a sheet with a vba script? Or shall I open another post for that?

View 3 Replies View Related

Setting Range.Formula With Custom Function

May 18, 2009

I recently set up some functions based on Chip Pearson's tutorial for referencing worksheets from Formulas. (http://www.cpearson.com/excel/sheetref.htm)

The problem I'm now having is that I can't use VBA to set these functions in place; it returns a syntax error.

For example, I want I37 on most sheets to have the same formula. So I have the following:

View 3 Replies View Related

Macro Recorder :: Compile Error: Argument Not Optional

Aug 24, 2007

I have recorded a simple macro ( copying a cell, and then pasting the formula into various others), and I get the following error

Compile Error: Argument Not Optional

I have highlighted where the error first happens

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 24/08/2007 by Michael Traynor
'

'
Range("K7:K8").Select
Selection.Copy
Range("K167:AJ168").Select
Range("AJ167").Activate
Range("K167:AJ168,K175:AJ176").Select
Range("AJ175").Activate
Range("K167:AJ168,K175:AJ176,K183:AJ184").Select
Range("AJ183").Activate
Range("K167:AJ168,K175:AJ176,K183:AJ184,K191:AJ192").Select

As I've said I didn't write this, it was recorded from Excel.

View 7 Replies View Related

VBA Function For "Mean" Using Array As Argument

Sep 8, 2005

I am trying to create a function in VBA (possibly an add-in in the future) to
calculate Relative Standard Deviation (RSD). I would like to be able to use
an equation such as: =RSD(values), where the values are cell references (a
variable quantity) chosen by the user.

To do this I've copied scripts for "Mean" and "StdDev", then wrote a small
function for RSD that calls those two. When I try to implement RSD in the
worksheet (or even just Mean), the cell returns an error: #VALUE! I think
the problem has something to do with the Arr() variable used as the argument,
as I've tried some simpler functions with array arguments and can't get them
to work, but using standard non-array arguments works fine. I can write a
subroutine that calls the functions and displays the correct result, but I
would like to be able to use RSD as a typical function in my worksheet.

If I could get Mean to work properly, I should be able to apply that
knowledge to StdDev and RSD. The text for Mean is listed below. I've tried
quite a few modifications to try to make it work, so if someone could get it
to work and reply with the correct function text (or with another function
that performs similarly), I'd appreciate it. Thanks.

Function Mean(Arr() As Single)
Dim Sum As Single
Dim i As Integer

Sum = 0
For i = 1 To UBound(Arr)
Sum = Sum + Arr(i)
Next i

Mean = Sum / UBound(Arr)
End Function

View 14 Replies View Related

Setting Up Deleting Macro

Oct 19, 2013

I'm trying to delete both rows of the same data not just one.

item
warehouse
location

PANT
7050
2
A1-05-89

PANT
5051
2
B1-19-19

SHIRT
7050
2
J1-08-67

SHIRT
7051
2
J1-08-67

What i need is if there is a same location delete both rows and i cant figure it out to get it to do both of them not just one row.

View 9 Replies View Related

Setting Up An Array On A Macro

Jun 24, 2008

I need to setup an array to look in 408 or so names and I don't want to have to write ("billy", "beth", ... ) etc - I just want it to scan array A3:A408 instead and unsure how to write the macro to do this.

Sub Echo_Monthly_CSR_June()
Windows("Monthly Macro Insert.xls").Activate
Sheets("CSR Data").Select
Dim MyArray As Variant, x As Long, c As Range

MyArray = Array("A3:A408").Select
For x = 0 To 405
With Workbooks("Monthly Macro Insert.xls").Sheets("Copy & Paste Echo")
Set c = .Cells.Find(What:=MyArray(x), After:=.Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not c Is Nothing Then..............................

View 9 Replies View Related

Setting Up Password In Macro

Aug 2, 2008

I have set up a form with the following macro with a password. The Macro works fine, but When the user enters the password it appears on the screen. I want the password to be seen as ************ on the screen. Please amend my macro, so that when the user enters the password, it appears as ********** on the screen

Private Sub CommandButton1_Click()
If Me.txtpass.Value "Mike1" Then
MsgBox "Incorrect Password!"
Exit Sub
Else
Me.Label3.Visible = True: Me.Label4.Visible = True
Me.ComboBox1.Visible = True
Me.CommandButton2.Visible = True
Me.ComboBox2.Visible = True
End If
End Sub

View 9 Replies View Related

VBA- Setting Id Of Textbox In Macro Code

Nov 12, 2008

Trying to make a error checking macro (if field is blank, then erro prompt and change textbox backcolour to red). However, I have a lot of text boxes, and my current macro is

View 3 Replies View Related

Trusted Macro Regardless Of Security Setting

Oct 22, 2009

I've been told the latest Excels will allow any workbooks in a certain folder
to run macros as trusted (regardless of security setting?).

Can anyone confirm this please? Is there more to it? What versions are affected? Mine is 2003 so I can't check for this.

View 9 Replies View Related

Page Setting Print Macro

Aug 18, 2006

I have a excel sheet with multiple sheets in it.And I have a VBA macro which split all those sheets into one file each.When I am trying to print one file(SHeet) which has more than 15 columns it is printing in two pages(Width).Is there any code i can use so that when ever i open a file which was split by me and give print command the width should fit to the page and peint.

View 6 Replies View Related

Macro Error Setting SeriesCollection

Nov 7, 2006

I try to plot an XY graph with a VB macro but I don't manage to select the right name of the active sheet.

Sub plot()
WksName = ActiveSheet.Name
Worksheets(WksName).Activate

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets(WksName).Range("A11:F12"), PlotBy:= _
xlRows
ActiveChart.SeriesCollection(1).XValues = "=WksName!R5C8:R643C8"
ActiveChart.SeriesCollection(1).Values = "=WksName!R5C9:R643C9"
ActiveChart.SeriesCollection(1).Name = "=""Specular"""
ActiveChart.Location Where:=xlLocationAsObject, Name:=WksName


End Sub

View 6 Replies View Related

A Macro Setting That Would Cause A Run-time Error 1004

May 6, 2009

Is there a setting within excel that could cause a user to get a run-time error 1004? I have a workbook with a macro that adds additional sheets to the workbook when a button is clicked. The thing works fine on my machine and 3 or 4 other machines that I have had guys test it out, but I have a user that it will not work for. He is at a location about 3 hours away so I cant see exactly what he is doing, but he says after he clicks the button he gets a run-time error 1004 that says "Unable to set the PrintQuality property of the Page Setup class". I'm thinking that it is a setting within excel?

View 5 Replies View Related

Setting Font For A Textbox/Listbox With Macro

Aug 15, 2009

How do you set the font for a textbox and or listbox? I have tried:

View 4 Replies View Related

Error Setting Range In Transcribing Macro

Oct 7, 2009

Trying to write a macro to transcribe data from columns in Sheet1 to Rows in Sheet2. Assuming there is a blank between each record. I get an error on the "Set Rng1....." line.

View 4 Replies View Related

Auto Run A Macro Even On Medium Security Setting

May 23, 2007

I have my Macro Security setting sets to 'Medium'. How can I auto launch my macro (ex. name - MyMacro) everytime I open the workbook ?

View 9 Replies View Related







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