Insert Relative Function That Uses Variables Into Cell
Mar 4, 2008
I am using VB to insert a function into a cell on an excell worksheet. The relative references work fine. For the absolute reference, I want to use a named range (LowTotal). If I simply put the name into the function, then I just get the name as text in the function. The second insert formula is were the named range is intended to go. Here is the code so far...
I need to know how to use the varriable name in the .formulaR1C1, so that VB knows it is a variable and not just text....
View 4 Replies
ADVERTISEMENT
Jul 17, 2008
I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place.
Is someone aware of a function which will target a relative location instead of an exact cell?
View 13 Replies
View Related
Mar 2, 2008
I have been looking around have this much code from this site, modified. What I am trying to do is calculate numbers by a position with cells D through K having numeric values. I have 'hardcoded' the cells (D2, E2, etc in the code below), but in reality I only want the current row (so if the formula is on the 2nd row, I want D2, if it is on the 3rd row, I want D3).
My problem is obviously the formula isn't working because I am not correctly tying back to the spreadsheet (Positioncalc.xls). When I put the formula in the spreadsheet it works, but in my script I get 0 everytime.
My Script:
Function Position(rCell As Range, Optional RightPosition As Boolean)
Dim vResult
Select Case rCell.Text
Case "QB"
vResult = (2*D2) + (2*E2) + (2*F2) + (4*G2) + (2*H2) + (1*I2) + (4*J2) + (3*K2)
Case Else
vResult = "Invalid Position"
End Select
If RightPosition = True Then
Position = vResult
Else
Position = "Position not valid"
End If
End Function
So, when I put =Position(A2,True) I expect to see the formula results of those cells calculated based on the position (QB, HB, etc with their unique formulas).
My next challenge after this is to highlight certain cells based on the Position. So if A2 = QB, I want cell D2 boldface and Red, etc. I have seen some scripts on colors and such here, so I might be able to figure it out.
View 9 Replies
View Related
Apr 24, 2008
Hello I'm looking for some clarification about how to use "= SUM(RC:RC)". I don't understand what the RC:RC represents. I thought that this was the directions for where to "position" the formula, but I think I'm missing something.
I was able to include the correct cells that I need summarized, but the answer is not right; it's zero.
Public Sub SubColumn(CritStr As String) ...
View 8 Replies
View Related
Jul 27, 2006
Can a Function give two or more output variables. e.g.
Sub a()
x = 5
result = Y(x)
End Sub
Function Y (x As Integer) As Integer
Dim B
B = ... * x
Y = ... * B
this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?
I need this because function works with large matrix and I want to extract some values appeared in between.
View 2 Replies
View Related
Nov 25, 2007
I am updating a wsheet via a form using vb to locate next empy row etc.
The issue I have is with the formula's no matter how I try I cannot get the formula to increment with each row. I know I can drag and auto fill on the sheet but I want to use vb if poss. eg
Previous entry to sheet:-
A1=1
B1=2
c1=A1+B1(formula)
New entry via form selecting next empty.
A2=(updated from form.)
B2=(Updated from form.)
C2=(Update code VB)
This is all okay and functional the problem starts from here
I so far have been unable to increment the formula in c1 from A1+B1 to A2+B2 using vb, is this possible or am I just chasing my tail.
View 3 Replies
View Related
May 22, 2008
I would like to use a varible Cell for the following forumula:
ActiveCell.Formula = "= ROUND(PIExpDat(""TimeEq('""&Cells(RowNdxG,4)&F$2,F$3,F$4,F$5,0,)/3600,1)"
I have underlined the variable. This is not working but is rather showing up as a string value Cells(RowNdxG, 4).
View 6 Replies
View Related
Jul 30, 2009
I was wondering if i could get a hand with inserting a file. Is there a way to insert the file and have it placed a relative distance from the insert button without using cell references?
View 2 Replies
View Related
Jun 10, 2008
I have a formula that works fine in the cell of an Excel spreadsheet but I'm struggling to translate it into VBA (your help please).
The cell formula is:
=If(B2="", "", B2 & " (version: " & F2 & ")")
I want to iterate through all rows in my spreadsheet (about 2000) incrementing the relevant row numbers in the formula @ each pass - eg changing B2 -> B3 -> B4 etc and F2 -> F3 -> F4 etc where column 'B' contains the name of the product and column 'F' contains the version number, resulting in "Product Name (version: 123)" per row.
The closest I've got to this is:
Sub LookupNameInColumnA()
Range("A2").Select
Dim i As Integer
For i = 1 To Selection. CurrentRegion.Rows.Count - 1
ActiveCell.Formula = "=IF(B2="""", """", B2 & "" Version: 999"")" ' problem line?
ActiveCell.Offset(1, 0).Select
Next i
End Sub
This works OK'ish but I want each line to reflect the different data per row. I'm struggling to increment row 'B' and row 'F' in the formula, having tried "B & i + 1" and "F & i + 1" but am getting confused with quotes and concatenating strings within formulas.
View 5 Replies
View Related
Aug 16, 2014
What I want to do is call a value from a cell of a separate workbook file by just inserting the file name. So basically:
Cell A1: File_Name.xlsx
Cell A2: ='[A1]Sheet1!'$B$2
Such that the formula effectively reads: ='[File_Name.xlsx]Sheet1!'$B$2 , and outputs the value of B2 from Sheet1 of File_Name.
But I keep getting error messages.
View 3 Replies
View Related
Jan 4, 2013
I'm trying to insert an IF function in a cell with VBA. But it keeps giving me an error and I don't understand why.
Here's the code:
Sub IFinsert_test()
Dim C_IndexKol As String
C_IndexKol = "Q"
Dim C_DebnrKol As String
C_DebnrKol = "A"
Worksheets("blad1").Range(C_IndexKol & "9").Formula = _
"=if(Worksheets("blad1").Range (C_debnrKol & "9")"""","testA","testB")"
End Sub
View 2 Replies
View Related
Dec 13, 2013
Is it possible to insert a cell refenece into the FIND function.
So, replace JAN-2013 with Sheet1 cell A1
Selection.Find(What:="JAN-2013", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
View 3 Replies
View Related
Apr 29, 2014
Input:
A
B
CA001
CA001
CA001
CA002
[Code]...
Output:
A
B
CA001
CA001
CA001
CA001
[Code]...
1. Insert a row whenever cell value change.
2. Insert cell value (in previous column) in inserted row.
View 3 Replies
View Related
Oct 1, 2008
I'm a definite newbie when it comes to macros but can figure out formulas ok.
So I wrote a formula to break out a sort string and it got pretty complicated so I’m trying to create a macro so I don't have to write out the formula all the time. However the sort tag can be in different columns when I need this formula so I tried the "run macro" function to create a relative reference.
So it changed my formula from this:
PHP
=IF(ISERR(FIND("("&(COLUMN()-COLUMN(EM2))&")",EM2)),"",IF(ISERR(FIND("("&(COLUMN()-COLUMN(EM2)+1)&")",EM2)),MID(EM2,FIND("("&(COLUMN()-COLUMN(EM2))&")",EM2)+3,100),MID(EM2,FIND("("&(COLUMN()-COLUMN(EM2))&")",EM2)+3,FIND("("&(COLUMN()-COLUMN(EM2))+1&")",EM2)-FIND("("&(COLUMN()-COLUMN(EM2))&")",EM2)-3)))
PHP
"=IF(ISERR(FIND(""(""&(COLUMN()-COLUMN(RC[-1]))&"")"",RC[-1])),"""",IF(ISERR(FIND(""(""&(COLUMN()-COLUMN(RC[-1])+1)&"")"",RC[-1])),MID(RC[-1],FIND(""(""&(COLUMN()-COLUMN(RC[-1]))&"")"",RC[-1])+3,100),MID(RC[-1],FIND(""(""&(COLUMN()-COLUMN(RC[-1]))&"")"",RC[-1])+3,FIND(""(""&(COLUMN()-COLUMN(RC[-1]))+1&"")"",RC[-1])-FIND(""(""&(COLUMN()-COLUMN(RC[-1]))&"")"",RC[-1])-3)))"
Now my problem is in the first formula I need "$EM2" not "EM2".
I tried the obvious to me fix of R$C[-1] which did not work... And when trying to re-run the macro it puts RC143 which will not work because the sort string is not always in column EM.
View 8 Replies
View Related
Nov 25, 2011
I'd like to know if it's possible (Excel 2007) to insert a function/button into a cell, which when clicked, will paste whatever is on the clipboard?
You can see a screenshot of my document here, and I'd like to insert a paste funtion in the red circle. Is it possible?
[URL]
View 9 Replies
View Related
May 7, 2009
I can use the MATCH function when i am in excel but i am having trouble getting it to work when i am trying to insert the formula using VBA.
Here is the code i have but it wont even compile........
View 9 Replies
View Related
Feb 14, 2013
I'm having trouble with my logic again :/
AREA
Tp std
Tp
30
5
So I want the Tp in the 3rd column to show the result:
IF AREA is less than or equal to 20, Tp=Tp std / 2
IF AREA is between 20 and 40, Tp=(((AREA-20)/(40-20))*(Tp std/2))+(Tp std/2)
IF AREA is 40 or greater Tp = Tp std
But as one equation
I have just been struggling with the range part.
View 2 Replies
View Related
Feb 14, 2007
I have and Indirect function that works.... I need to modify it to include a cell address reference, but this requires the use of a Vlookup function to find the address ....
I have this formula: but it does not work
I'm not sure how to include the VLOOKUP function in my argument to
[/quote][/code]=INDIRECT(CELL("contents",B3)&"!&Address(VLOOKUP(B16,'1'!B:AE,match(RAY_ANALY!D2,'1'!B2:AE2,0),0)")
View 9 Replies
View Related
Dec 9, 2013
I need to figure out a vlookup function that searches for two variables and return an answer. such as the example attached;
vlookup -- 12253 & 605.34 (if these are matching then) 131001
View 5 Replies
View Related
Aug 30, 2006
I wrote my function as it is shown below:
Function MyFunction1(r, n, xrange, yrange)
If r <= n Then
For i = 1 To 5
MyFunction1 = MyFunction1 + xrange(i)
Next
Else
For i = 1 To 5
MyFunction1 = MyFunction1 + xrange(i) * yrange(i)
Next
End If
End Function
This is just a very basic idea of what I need to do, so it might not make sense why I am doing it, but I am just trying to test that the function works. Anyhow, the thing is that it works with something like this when I input it in a cell in a worksheet: =MyFunction1(RAND(),0.5,{5,6,7,8,9},{10,20,40,50,100})
However, if I try to define the range or set {5,6,7,8,9} with other cells, it does not work. so for example if I try the following:............
View 3 Replies
View Related
May 14, 2008
I am creating a worksheet and I have a formlua where the value is text. I can make it work standing alone but not with other variables. =IF(E11=S1,0)+(IF(E12=S1,0)+(IF(E13=S1,0.0025))+IF(E14=S1,"Call for Prciing"))
View 2 Replies
View Related
Sep 14, 2006
A most of time I'm using VLOOKUP function. I want to call specially this function by pressing custom button (w/o pressing "Insert Function" and choosing VLOOKUP)
View 4 Replies
View Related
Aug 25, 2009
I am trying to make the "A4:A" portion of this line of code generic so it can work in multiple different files. I have defined a variable FirstGridRow that will take care of the '4' but I need to find a way to splice it all together. Essentially I am stuck trying to tack on the ":A". The first piece shows where I am coming from and the second is where I am trying to go to.
View 3 Replies
View Related
Jun 2, 2014
I am having a hard time getting my match functions to work with letters and numbers in the same range. I am also trying to figure out a way to have the ranges switch if there is an error. because the data is in multiple columns. It is hard to explain so I have attached a sample workbook.
ExampleFile.xlsm
View 13 Replies
View Related
Nov 21, 2005
I have a worksheet that has 6 columns of numerical data that all column
datasets are referenced together for each row. So for example the data
in row 5 in all colums is related.
Column C through F have 4 numbers that refer to the data in the first 2
columns. These are also ordered by row.
I need to look in column A for 0.00, and column B for 3.14 Both of
these must be in the same row or I need the closest value. When these
values are found in columns A & B, return the values from the same row
in Columns C through F.
View 13 Replies
View Related
Apr 10, 2012
I have quite a number of 4th degree polynomials and attempting to extract the variables from every equation. I used the formula below (from another thread);
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))
but it only extracts the first variable.
4th degree polynomial function: f(x) = ax^4 + bx^3 + cx^2 + dx + e
Example of one of the equations: Y = -42.4276 + 319.989 X - 809.094 X^2 + 942.247 X ^3 - 397.587 X^4
I'll like to have the final result like below (Based on the Example equation above)
edcba-42.4276319.989-809.094942.247-397.587
View 4 Replies
View Related
Jan 27, 2014
In a macro I want to automatically insert a formula in several cells. The formula depends on other cells, and I want to be able to manipulate these cells with variables. Here is my code:
[Code] .....
All my variables are declared:
Sheets("DATOS YTD 2014").Cells(I, Semana + 1) is the cell I want to be able to chose due to the variables
Sheets("DATOS YTD 2014").Cells(I, 3) is the reference cell for the VLOOKUP formula, that will vary with I
SheetMonth.Range("B3:W172") is a range in the sheet SheetMonth which is fixed
The error message I receive is the following: "Run-time error 13, Type mismatch"
I precise that the next step is to make the ",6," part in the vlookup formula also variable... but I guess once my current issue is solved this one will be a piece of cake.
View 3 Replies
View Related
Mar 25, 2013
Below is my code which isn't formatting the cells it's suppossed to. It looks like it isn't doing anything. I think the issue might be with the highlighted section of my code, but when I go to "Manage" my rules for conditional formatting, excel references the appropriate cell under the "Applies to" section. I am using relative cell references for for the majority of the rest of the code and this section follows a section that selects the correct cell for this conditional format.
Code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=""ABS()>.005"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority[code].....
View 1 Replies
View Related
May 2, 2014
The problem I am trying to solve is to have a cell return a cell value based on another linked cell. The cells I want to link are in separate tabs or could be another workbook.
For example if cell A5 in sheet2 is linked to A20 in sheet1...A5 has formula(=Sheet1!A20). I want the next cell B5 to look at A5 and return the value of cell Sheet1!D18.
So in essence I want the second cell to look at the first cell and return a value that is 3 columns over and 2 rows up.
View 4 Replies
View Related
Jul 9, 2008
way I can use relative cell address in VBA? For example, in the following table:
value item 1 apple item 2 peach item 3 mango
Instead of use
Range("B4").Value = "mango"
to insert mango to cell B4, I can use an address that's one row under peach, or one column to the right of item 3?
View 9 Replies
View Related
