Insert Relative Formula Into Range Via VBA
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
ADVERTISEMENT
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
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
Jan 23, 2008
Here is my array code
Dim Values() As String
Dim count As String
count = 9
For count = 9 To 1800
Values(count) = "=IF(AND(E" + count + "<>"""",AH" + count + "<>0),(AH" + count + "/E" + count + ")*100,0)"
count = count + 1
Next
I'd make it a static array but eventually I'm going to have to set it up where I wont know the upper bound.
Here is the other part of my code that I don't know what I'm doing.
If Range("A9") > 0.01 Then
Range("AI9:AI1800") = 0.01
Else
Range("AI9:AI1800") = Values
End If
I keep getting mismatch errors on the count variable, but I need it to be a string so I'm not sure what do here.
View 9 Replies
View Related
Feb 13, 2008
I am using VB in Excel to write a formula for summing a column of numbers that will vary in length. I have been using the Indirect formula and have the start and end cells of the range identified in another location. My problem is, the macro in vb is sending the Indirect formula to the cells containing the start and end points by using relative functions ie. go across 30 columns and up 10 rows to get start cell. I wanted it to just save the cell location of the start and end points.
EG.
VB formula is: "= SUM(INDIRECT(R[-23]C[27]&R3C29):INDIRECT(R[-23]C[27]&R3C30))"
whereas I just want it to read "=Sum(Indirect(C4&D5):INDIRECT(C4&D6))"
where cell C4 gives the column letter and D5 the row number of the start of the range and C4 and D6 give the column letter and row number of the end of the range.
View 9 Replies
View Related
Sep 22, 2007
using a macro im trying to paste in a sum formula, however this will be pasted onto different sheets and the number of records will be different for each so i cannot use absolute locations. the only constant is that all ranges will start in row I9. when it runs the following code
Range("G" & (ActiveCell.Row)).Select
ActiveCell = "Total"
Range("I" & (ActiveCell.Row)).Select
ActiveCell.FormulaR1C1 = "=Sum(I8:R[-1]C)"
Range("J" & (ActiveCell.Row)).Select
ActiveCell.FormulaR1C1 = "=Sum(J8:R[-1]C)"
Range("I:J" & (ActiveCell.Row)).Select
Selection.Copy
Range("I4:J4").PasteSpecial
it fills in =SUM('I8':I13) how can i make it insert it without the ' ' around the I9? and how can i select the two cells? (again, cannot be absolute..)
View 3 Replies
View Related
May 26, 2008
In my data validation I have the following formula as the Source: =INDIRECT(SUBSTITUTE($F$2," ","_")). I want to be able to copy the cell and paste it into another cell and have the formula update to reference the new cell. Currently the validation is for cell G2 and references F2. I want to be able to copy G2 and paste it in G3-G6 having the formula update so it refrences F3-F6.
View 3 Replies
View Related
Mar 16, 2008
Is there a way to absolute reference multiple cells at one time?
View 5 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
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
View Related
Sep 6, 2012
i am trying to insert a formula into a range of cells using a For... to loop
This is what it looks like:
VB:
For i=1 To n
Worksheets("Ret_sheet").Cells(i, 8).Formula = "=if(mid(B" & i & ",3,1)=""A"",""PY Campaigns"",mid(B" & i & ",4,3)"
Next i
The row with the formula returns a syntax error.
what the right syntax is? I have read a lot about inserting a formula in a cell using VBA, but i never met the case with a counter (i) usage. I guess the problem might be there.
View 8 Replies
View Related
Oct 6, 2007
My macro inserts the formulas correctly up until the last loop where I get an Application Defined or Object Defined error on the .FormulaR1C1 line. The msgbox returns the correct values.
iCtr = 0 'data starts on 1
For c = 6 To 14 'columns F to N
iCtr = iCtr + 1
MsgBox ("Column " & c & " Index " & iCtr)
With . Cells(6, c)
.HorizontalAlignment = xlCenter
.NumberFormat = "#,##0;;"
.FormulaR1C1 = "=IF(ISBLANK(INDEX(MyNamedRange," & iCtr & ",1)),"""",'" & Replace(MainPage.Name, "'", "''") & "'!R" & NextRow & "C6)"
End With
Next
View 5 Replies
View Related
Oct 12, 2007
I am trying to insert a formula into the cells in a column dynamic range.
I never know how long the column will be from instance to instance.
I have been trying to use the following code,
Sub insertf()
'
' insertf Macro
' Macro recorded 10/11/2007 by bbactadmin
'
'
Do Until myrange.Value = ""
ActiveCell.Range("f10").Select
ActiveCell.Offset(1, 0).Range("f1").Select
Range.Formula = _
"=VLOOKUP(LEFT(E11,3),fam,2,FALSE)"
ActiveCell.Offset(1, 0).Range("f1").Select
Loop
End Sub
I am trying to insert the lookup in all of the cells in the column within the dynamic range.
View 8 Replies
View Related
Jun 3, 2014
Trying to get this short piece of code to work. I'm trying to show the formula in the cell not just return the result.
[Code] ......
Why this isn't working ?
View 5 Replies
View Related
May 29, 2009
I have recorded a macro code for which is as follows. This Macro goes into a worksheet and gives percentile value for a range of data. But this is becoming cubersome as this sheet is 65531 rows and can span multiple spread sheets. Can this be put in a loop of some sort to go through the whole worksheet? ....
View 9 Replies
View Related
Jul 21, 2006
I am trying to put a SUMIF formula at the bottom of the data. Doing it manually is fine, but I want some vba to automate it, and my vba doesnt work.
Sub balance()
'adds journal line so journal balances in each month
Dim Rga As Range
Dim Rgb As Range
Dim Rgc As Range
Dim Rgd As Range
Dim Rge As Range
Dim Rgf As Range
This code just inserts text into the formula, rather than the range of cells.
View 6 Replies
View Related
Sep 9, 2013
I am try to get the following VBA macro to work; however, I keep getting hung up on errors regarding the formula I am trying to input. It is getting hung up on the apostrophes and dollar signs. I am fairly new to VBA so I am lost when it come to converting my sheet formulas to VBA.
Code:
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks)
.Formula = "=RAND()*0+VLOOKUP(INDIRECT(ADDRESS(1,COLUMN(),3),TRUE),INDIRECT("'"&TEXT(INDIRECT("$A"&ROW(),TRUE),"DD-MMM-YYYY")&" Inv'!"&"$J:$K",TRUE),2,FALSE)"
.Value = .Value
End With
View 3 Replies
View Related
Dec 27, 2012
I have data headings in A1-E1 and actual data in A2-E2. I have a SUMIF function in F2.
A B C D E F
A B A B B
1 2 3 4 5 =SUMIF(A1:E1;"A";A2:E2)
When I insert new data columns between column E and F, the formula still refers to columns A-E although I would like to have the newly added columns in that SUMIF-function included.
For example, if I add 3 new columns, the new SUMIF-function (now in cell I2) should become =SUMIF(A1:H1;"A";A2:H2).
Do to modify the SUMIF-function to do that?
View 4 Replies
View Related
Jan 17, 2005
I have a spreadsheet with very long formulas that reference a whole lot of variables in the same column. I tried naming the cells so that writing of the formula is easier to do. However, I need to copy the column across for other entities of varying nature, and these copied cells refer to the same named cell. Can I name a cell and copy it sideways so that it retains some sort of name with allusion to the column that it is in?
Andrew van Gruting"
I have tried copying a formula with a named range and, as expected, it maintains an absolute reference. Is there a way that he might be able to copy but drop the absolute reference and copy with relative references. Can I place something in front - the equivalent to the $ sign, but rather a relative sign.
View 6 Replies
View Related
Jun 5, 2009
I have a workbook with a single worksheet. The worksheet includes some named ranges, which are used in a dynamic chart (so that when I add data, the chart updates).
I want to duplicate worksheet#1 so that I can have worksheets 2, 3, 4 etc.
Thus I can paste new data in the new worksheets, and have all my calculations done.
Naturally, the only probelm is the graph. It is using the named ranges from worksheet #1.
Is there a solution, so that I don't have to re-create my graphs on each worksheet? (each worksheet has varying numbers of rows, AND, I will be updating each worksheet with new data from time to time, thus the need for a dynamic chart)
View 4 Replies
View Related
Feb 24, 2009
Is there a way to write formulas so the variables are relative to a sheet?
I have a workbook that collects monthly electrical meter readings and calculates billing for each tenant. A new sheet is used for each month and the formulas need to refer to the previous month’s sheet for the prior meter reading.
For example cell C36 in the sheet Feb09 might contain: =Jan09!D36 Where Jan09 is the sheet name from the prior month. When I copy sheet Feb09 to make sheet Mar09 I’d like the formula in C36 to be =Feb09!D36.
View 9 Replies
View Related
Aug 6, 2008
I'm importing comma delimited files into Excel. These files will contain formulas, but do not know on which row they will end up in once imported. My formula's need to reference the row they have been pasted to. For example, If I paste the formula on Row 2 the formula would be =A2+B2
Row 3 =A3+B3
However, since these formulas are coming in via a non-excel file, I need the cell reference to be dynamic. Something along the lines of =A#+B# where # would be interpreted by Excel as the current row the formula is on. (I know that '#' isn't correct.)
View 2 Replies
View Related
Apr 1, 2014
I am trying to create a formula to count a range relative to a dynamic reference (at least I think that's you would phrase it).
I thought I was on to a winner with this:
Formula:
[Code] .....
But it doesn't seem to accept the : as a legal operator.
I suspect the answer is to use SUMPRODUCT somehow.
View 6 Replies
View Related
Sep 7, 2009
I'm working on or any part of it on here, because the work belongs to my employers and would constitute the loss of trade secrets. It would also take me way longer than I have the time to invest to put together a functional duplicate of the relevant part of the sheet just to be able to post it.
With that in mind, what I'm trying to do is refer to part of a range that spans five columns (though it could conceivably span more in the future, so I'd rather not count on that detail) and a constantly changing numbers of rows. The part I want to refer to includes all columns, and all rows except the first and the last in the range.
View 5 Replies
View Related
Mar 8, 2013
I have a data that is split into multiple cells and needs to concatenated. Unfortunately, the number of columns wherein lies the data varies throughout the workbook. As such, I wanted to select a range of cells, define this range as the reference point for the macro, run the macro, then move on (selecting a different number of columns on the next try).
For example, I have this:
A
B
C
D
E
1
No
Not Very Far
[Code] .....
I want to select cells A1:B1, run a macro concatenated the two columns, then select cells C1:E and run the same macro to get this:
A
B
1
No
Not Very Far
[Code] ........
So far, I have this:
Code:
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(5, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[1],RC[2])" '
View 9 Replies
View Related
Dec 11, 2013
I'm trying to use relative references and am have having trouble with selecting a range for the fill down command.
I'd like to copy a formula down in the last column from row 1 to the last row with data. Column D is static so I can always use it to find the last row. The last column will vary; sometimes it might be G another time it might be X. I'm able to get the formula entered into the proper cell but I can't figure out how to select the range for a fill down.
If I was not using relative references and cell X1 always had the formula then Id code it like this:
'*** Find last row with data
Range("D1").Select
LastRow = Range("D65536").End(xlUp).Row
'*** Calculate length of cell in previous column
[Code]....
It's the last range statement that stops me. I haven't come up with a variation that allows me to select a range from the cell on the first row of the last column and select the last row with data for the same column.
View 3 Replies
View Related
Nov 4, 2008
I've been trying to get the sum of a range of cells based on their relation to the active cell. Here is the Pseudo
If the SUM of (the cell 2 rows up THROUGH the cell 2 rows up and 2 columns to the left) = 0 then .......
*****end pseudo
I think that activecell.offset is the best way to do it, but I can't figure out how to work that in a range....
Here is my code so far:
Worksheets("Generic SPC").Activate
Worksheets("Generic SPC").Range("c2").Select
'Do for all cells in the row
Do
ActiveCell.Offset(0, 1).Select
If Application.WorksheetFunction.Sum(Range( _
ActiveCell.Offset(-2, 0), ActiveCell.Offset(-2, 2))) = 0 Then
ActiveCell.Value = 0
ElseIf Application.WorksheetFunction.Sum(Range _
("ActiveCell.Offset(-2, 1), ActiveCell.Offset(-2, 3)")) = 0 Then
ActiveCell.Value = ActiveCell.Offset(-2, 0).Value
Else: ActiveCell.Value = 0
End If
Loop Until IsEmpty(ActiveCell.Offset(0, 1)) = True
******End Code
Range doesn't like activecell.offset. Does anyone know how to do this?
View 9 Replies
View Related
Oct 27, 2013
can I put formula to cells on relative basis?
For instance can I write "=A1 + B1" and have a change event the formula populate for the corresponding column that changes? such that a change in C7 for instance populated the result of the formula for A7 + B7 in cell D7?
View 7 Replies
View Related
Dec 8, 2008
It's been a long time since I have worked with Excell, it use to be the " $ " that made things absolute or relative.
I have this formula:
='1'!C194+C193 on sheet 1 named "1" without the " ". I need to copy it across 31 sheets. I thought that it was the " ! ", without the " ", that makes the address absolute so I took it out and then attemped to copie it to other sheets. The idea was that the formula would then read: ='2'!C194+C193 on sheet 2, and ='3'!C194+C193 on sheet 3 and so on. The formula is in the exact same location on each sheet, C194. But this won't work. I am at this time doing each formula one at a time. I have many workbooks that will use the same style of copy and paste; about 15 workbooks each with 31 sheets.
It would help if someone could explain how to save time. I do know how to use the Ctrl to select different cells and how to use Ctrl+Page UP/Down to change tabs. I just have to get the formula correct.
View 14 Replies
View Related
Sep 21, 2009
I currently have a list of totals that i want to easily be able to change the cells that make up this total.
I have been trying to set up one cell that contains the formula and then the remaining totals below are linked to this cell. Thus I change the formula in the top cell all the other totals also change.
View 9 Replies
View Related
