store formula in cell and use cell ref in vba code

D

DBus

New Member
Joined
Mar 6, 2011
Messages
14
Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Is it possible to store a formula in a cell and then ref to that cell in the macro code, instead of hard coding the formula into the macro. <o:p></o:p>
<o:p></o:p>
Very simply, I want to use a variation of the following code to populate a table, but rather than have to look up the formula within the code to make changes, I would like it taken from a cell, where it is more visible. That is store the sumproduct formula below in, say, the cell B1 and refer to B1 in the code.<o:p></o:p>
<o:p></o:p>
Sub Macro1()
Dim i As Integer
For i = 3 To 14<o:p></o:p>
Cells(i, 2).FormulaR1C1 = "=SUMPRODUCT((Validity=RC[-1])*(In=1)*(Out=1))"
Cells(i, 2).Value = Cells(i, 4).Value
Next i<o:p></o:p>
End Sub <o:p></o:p>
<o:p></o:p>
Hope this makes sense.<o:p></o:p>
DBus
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
If B1 is formatted as Text and contains your formula, try:

Code: 
Cells(i, 2).FormulaR1C1 = Range("B1").Value

By the way, what is the purposeof the next line in your macro?

Code: 
Cells(i, 2).Value = Cells(i, 4).Value
 
Upvote 0
Andrew, Thanks. I probably did not explain well enough. I have tried out your sugestion and it copies the answer (value) from B1 down the column - each of the other cells contains the same value as B1. What I was hoping for was to hold the formula in B1 without the = sign so that it can be read as text (and amended if required), then use the formula as a formula in each of the subsequent cells.

If B1 contains "Sumproduct((Validity=RC[-1])*(In=1)*(Out=1))" as text, then I would like this text used as a formula in B2 (and so on) with the RC[-1] working against each corresponding value in column A.

My example is very simple. My ultimate purpose is to build it into a macro to calculate results in a Stats return and once calculated convert the results into values - hence my second line Cells(i, 2).Value = Cells(i, 4).Value. Here I made a small mistake it should read Cells(i, 2).Value = Cells(i, 2).Value. Currently each cell holds the formula and that makes the file extremely large.
<TABLE style="WIDTH: 444pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=592 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 204pt; mso-width-source: userset; mso-width-alt: 9947" width=272><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 204pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=272></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Validity</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>In</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Out</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SUMPRODUCT((validity=A2)*(In=1)*(Out=1))</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>1</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num x:fmla="=SUMPRODUCT(($D$2:$D$13=A3)*($E$2:$E$13=1)*($F$2:$F$13=1))">3</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>2</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>2</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num x:fmla="=SUMPRODUCT(($D$2:$D$13=A4)*($E$2:$E$13=1)*($F$2:$F$13=1))">2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>4</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>4</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num x:fmla="=SUMPRODUCT(($D$2:$D$13=A5)*($E$2:$E$13=1)*($F$2:$F$13=1))">1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>1</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>6</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num x:fmla="=SUMPRODUCT(($D$2:$D$13=A6)*($E$2:$E$13=1)*($F$2:$F$13=1))">1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>6</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>1</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>8</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>2</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>4</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>3</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>3</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>1</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>1</TD></TR></TBODY></TABLE>
DBus
 
Upvote 0
Andrew - Thanks.

I must learn more of the use of " " and other such abbreviations.

One thing. The reference to A2 in my formula is supposed to be looking for the value in the cell A2, but the code currently converts it into a piece of text and returns it as 'A2'. With the resulting answer in each of the cells in row 3 to 6 showing as #Name?

I was hoping that the formula could change the reference to A3, A4, A5 etc as it move down through the cells in rows 3 to 6.

Any hope?

Dbus
 
Upvote 0
Try:

Code: 
Sub Macro1()
    Dim i As Integer
    With Range("B2:B13")
        .Formula = "=" & Range("B1").Value
        .Value = .Value
    End With
End Sub
 
Upvote 0
Amazing. Thanks very much. There is so much to learn with Excel and writing macros. What I am realising is that almost anything is possible - particularly with the experts, such as you, on the end of the web.

Thanks
Dbus
 
Upvote 0
Andrew,
I have been using your solution with great success. Now I have hit a related problem.
I can use i = 1 to 10 or even c = 5 to 15 to allow me to work through a table and the relevant references remain as expected. However, now and again I want to skip a column and use the c = 5 to 15 step 2.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
Now the formula repeats itself rather than referencing the current column
<o:p></o:p>
For instance, in cells A1 to E1, I have a number of reference points. The formula I copy into row 6 looks at each relative reference point in Row 1. So using the line of code you provided, the formula in cell A6 refers to A1 and in B6 refers to B1 etc. When I try to step from A to C to E, then the result in C6 still refers to A1 rather than C1.
<o:p></o:p>
Any ideas?
Dbus
 
Upvote 0
Hello Andrew,
Sorry for the delay in replying, I have been away.

I attach all of the code. It is probably more that you need, but better have it all than miss something vital.

I have indicated - within the code, places where my query arises and what I hope to achieve. Not sure how to attach code other than to copy and paste below.

At the moment I am having to set the first formula in each table, then fill in each table.

Thanks Dbus

Option Private Module
Sub CalcResults1()
'This macro calculates the values into Tables 0.0, 1.0, 1.1, 1.2 & 1.3
Dim WkSh As Worksheet
Dim AnchorCell As Range, MyHder As Range, MyF As Range
Dim Mycell As Range, MyRng As Range
Dim ColIndex As Range, Rng As Range, Name As Range
Dim Bm As Long, Rt As Long
' check to ensure correct sheet is active
Set Mycell = Range("A1")
If Mycell.Value = "T0.0" Or Mycell.Value = "T1.0" Or _
Mycell.Value = "T1.1" Or Mycell.Value = "T1.2" Or _
Mycell.Value = "T1.3" Then
'' still need to find way to stop spreadsheet displaying all steps
Call CalcNegValues1
Call CalcRepValues1
Call CalcIntValues1

Else
MsgBox "Check that you have the correct sheet open"
Exit Sub
End If

End Sub
Sub CalcNegValues1()
Dim ColIndex As Range

Set WkSh = ActiveSheet
Set AnchorCell = Cells.Find(What:="AllNeg1")
TbBm = Range("E9").End(xlDown).Row - 9 'Last row in Dest table

Application.CutCopyMode = True
' Calculates the number to which the column relates (Column C = 3)
Set Rng = Range("8:8")
Rng.Name = "MyRng"
AnchorCell.Name = "MyName"
Range("MyName").Select
Set ColIndex = AnchorCell.Offset(-2, 0)
ColIndex = "=MATCH(MyName,MyRng,0)"

Application.ScreenUpdating = False
' Sets appropriate formula reference
Sheets("Frmla").Activate
Set MyF = Cells.Find(What:="NoClmts").Offset(0, 1)
Application.ScreenUpdating = True

' Coding to fill each cell with relevant formula,
' calculate result and convert result to value
WkSh.Activate
'' Set first formula for No. of Claimants in each table
'' (Cells F9, Y9 and AR9)
Set MyCell2 = AnchorCell.Offset(1, 1)
MyCell2.Select
With MyCell2
.Formula = "=" & MyF.Value
.Copy
.Offset(0, 19).PasteSpecial Paste:=xlPasteFormulas
ActiveCell.Copy
ActiveCell.Offset(0, 19).PasteSpecial Paste:=xlPasteFormulas
End With

For c = ColIndex To ColIndex + 40 Step 19
'' Fill No. of Claimants (Columns F, Y and AR from row 9 to 43)
TbBm = Range("E9").End(xlDown).Row - 9 'Last row in Dest table
Set MyCell3 = Cells(9, c + 1)
Set MyRng = Range(MyCell3.Offset(0, 0), MyCell3.Offset(TbBm, 0))
MyCell3.AutoFill Destination:=MyRng, Type:=xlFillDefault
MyRng.Value = MyRng.Value
'' Set first formula for No. of Inspections in each table
'' (Cells G9, Z9 and AS9)
Set MyCell2 = AnchorCell.Offset(1, 2)
MyCell2.Select
With MyCell2
.Formula = "=" & MyF.Offset(1, 0).Value
.Copy
.Offset(0, 19).PasteSpecial Paste:=xlPasteFormulas
ActiveCell.Copy
ActiveCell.Offset(0, 19).PasteSpecial Paste:=xlPasteFormulas
End With

'' Fill No. of Inspections (Columns G, Z and AS from row 9 to 43)
Set MyCell3 = Cells(9, c + 2)
Set MyRng = Range(MyCell3.Offset(0, 0), MyCell3.Offset(TbBm, 0))
MyCell3.AutoFill Destination:=MyRng, Type:=xlFillDefault
MyRng.Value = MyRng.Value
'' Set first formula for No of Single '%' penalties
'' Cells J9, L9, N9 and P9 repeated from AC9 and AV9
'' Would like to remove need to repeat code
Set MyCell2 = AnchorCell.Offset(1, 5)
MyCell2.Select
With MyCell2
.Formula = "=" & MyF.Offset(2, 0).Value
.Copy
.Offset(0, 2).PasteSpecial Paste:=xlPasteFormulas
ActiveCell.Copy
ActiveCell.Offset(0, 2).PasteSpecial Paste:=xlPasteFormulas
ActiveCell.Copy
ActiveCell.Offset(0, 2).PasteSpecial Paste:=xlPasteFormulas
End With
Set MyRng2 = Range(MyCell2, MyCell2.Offset(0, 7))
With MyRng2
.Copy
.Offset(2, 0).PasteSpecial Paste:=xlPasteFormulas
End With
Set MyRng3 = Range(MyCell2, MyCell2.Offset(3, 7))
With MyRng3
.Copy
.Offset(0, 19).PasteSpecial Paste:=xlPasteFormulas
.Offset(0, 38).PasteSpecial Paste:=xlPasteFormulas
End With
'' Set first formula for No of Multiple '%' penalties
'' Cells S9 & U9 repeated from AL9 and BE9
'' Would like to remove need to repeat code
Set MyCell2 = AnchorCell.Offset(1, 14)
With MyCell2
.Formula = "=" & MyF.Offset(3, 0).Value
.Copy
.Offset(0, 2).PasteSpecial Paste:=xlPasteFormulas
End With
Set MyRng2 = Range(MyCell2, MyCell2.Offset(0, 3))
With MyRng2
.Copy
.Offset(2, 0).PasteSpecial Paste:=xlPasteFormulas
End With
Set MyRng3 = Range(MyCell2, MyCell2.Offset(3, 3))
With MyRng3
.Copy
.Offset(0, 19).PasteSpecial Paste:=xlPasteFormulas
.Offset(0, 38).PasteSpecial Paste:=xlPasteFormulas
End With
Range("J9:BG11").Value = Range("J9:BG11").Value

'' Set first formula for No of Single '%' penalties per Standard
'' Cells J14, L14, N14 and P14 repeated from AC14 and AV14
'' Would like to remove need to repeat code
TbBm = Range("E14").End(xlDown).Row - 14 'Last row in Dest table
Set MyCell2 = AnchorCell.Offset(6, 5)
With MyCell2
.Formula = "=" & MyF.Offset(4, 0).Value
.Copy
.Offset(0, 2).PasteSpecial Paste:=xlPasteFormulas
ActiveCell.Copy
ActiveCell.Offset(0, 2).PasteSpecial Paste:=xlPasteFormulas
ActiveCell.Copy
ActiveCell.Offset(0, 2).PasteSpecial Paste:=xlPasteFormulas
End With
Set MyHder2 = Range(MyCell2, MyCell2.Offset(0, 6))
With MyHder2
.Copy
.Offset(0, 19).PasteSpecial Paste:=xlPasteFormulas
.Offset(0, 38).PasteSpecial Paste:=xlPasteFormulas
End With

Application.CutCopyMode = False
'' Fill No of Single '%' penalties per Standard
'' Fill Columns J, L, N & P and AC & AV from row 14 to 43)
TbBm = Range("E14").End(xlDown).Row - 14 'Last row in Dest table
Set MyCell3 = Cells(14, c + 5)
Set MyRng = Range(MyCell3.Offset(0, 0), MyCell3.Offset(TbBm, 7))
Set MyHder = Range(MyCell3.Offset(0, 0), MyCell3.Offset(0, 7))
MyHder.AutoFill Destination:=MyRng, Type:=xlFillDefault
MyRng.Value = MyRng.Value
Next c
ColIndex.ClearContents
End Sub
Sub CalcRepValues1()
'' Would like to remove need to repeat formula
'' Same formula used in BL13 as BM13 & BN13, repeated from BQ13 and BV13
Dim ColIndex As Range

Set WkSh = ActiveSheet
Set AnchorCell = Cells.Find(What:="AllRep1")
TbBm = Range("E13").End(xlDown).Row - 13 'Last row in Dest table

Application.CutCopyMode = True

Set Rng = Range("12:12")
Rng.Name = "MyRng"
AnchorCell.Name = "MyName"
Range("MyName").Select
Set ColIndex = AnchorCell.Offset(-1, 1)
ColIndex = "=MATCH(MyName,MyRng,0)"

Application.ScreenUpdating = False
Sheets("Frmla").Activate
Set MyF = Cells.Find(What:="AllRep1").Offset(0, 1)
Application.ScreenUpdating = True

WkSh.Activate
Set MyCell2 = AnchorCell.Offset(1, 0)
With MyCell2
.Formula = "=" & MyF.Value
.Copy
'' the following 2 lines allow the formula to move across to next subtable
.Offset(0, 5).PasteSpecial Paste:=xlPasteFormulas
ActiveCell.Copy
ActiveCell.Offset(0, 5).PasteSpecial Paste:=xlPasteFormulas
End With
Application.CutCopyMode = False
For c = ColIndex To ColIndex + 13 Step 5
Set MyCell3 = Cells(13, c)
Set MyRng = Range(MyCell3.Offset(0, 1), MyCell3.Offset(TbBm, 3))
Set MyHder = Range(MyCell3, MyCell3.Offset(0, 3))
MyCell3.AutoFill Destination:=Range(MyHder, MyHder), Type:=xlFillDefault
Set MyHder = Range(MyCell3.Offset(0, 1), MyCell3.Offset(0, 3))
MyHder.AutoFill Destination:=MyRng, Type:=xlFillDefault
MyRng.Value = MyRng.Value
MyCell3.Value = MyCell3.Value
Next c
ColIndex.ClearContents
End Sub
Sub CalcIntValues1()
Dim ColIndex As Range

Set WkSh = ActiveSheet
Set AnchorCell = Cells.Find(What:="AllInt1")
TbBm = Range("E13").End(xlDown).Row - 13 'Last row in Dest table

Application.CutCopyMode = True

Set Rng = Range("12:12")
Rng.Name = "MyRng"
AnchorCell.Name = "MyName"
Range("MyName").Select
Set ColIndex = AnchorCell.Offset(-1, 1)
ColIndex = "=MATCH(MyName,MyRng,0)"

Application.ScreenUpdating = False
Sheets("Frmla").Activate
Set MyF = Cells.Find(What:="AllInt1").Offset(0, 1)
Application.ScreenUpdating = True

WkSh.Activate
Set MyCell2 = AnchorCell.Offset(1, 0)
With MyCell2
.Formula = "=" & MyF.Value
.Copy
.Offset(0, 6).PasteSpecial Paste:=xlPasteFormulas
ActiveCell.Copy
ActiveCell.Offset(0, 6).PasteSpecial Paste:=xlPasteFormulas
End With
Application.CutCopyMode = False
For c = ColIndex To ColIndex + 15 Step 6
Set MyCell3 = Cells(13, c)
Set MyRng = Range(MyCell3.Offset(0, 1), MyCell3.Offset(TbBm, 2))
Set MyHder = Range(MyCell3, MyCell3.Offset(0, 2))
MyCell3.AutoFill Destination:=Range(MyHder, MyHder), Type:=xlFillDefault
Set MyHder = Range(MyCell3.Offset(0, 1), MyCell3.Offset(0, 2))
MyHder.AutoFill Destination:=MyRng, Type:=xlFillDefault
MyRng.Value = MyRng.Value

MyCell3.Value = MyCell3.Value
Next c
ColIndex.ClearContents
Range("A1").Select
End Sub
Sub ClearValues()
Range("F9:BI43").ClearContents
Range("BK13:CP43").ClearContents
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
A
  • Solved
#REF! error when using INDEX MATCH
Replies
5
Views
80
Fluff
Fluff
K
  • Solved
Advance formula 3 columns need update
Replies
8
Views
124
Kim B
K
U
  • Solved
VBA code with Formula that applies a Conditional Formatting border to a range of cells
Replies
9
Views
286
Peter_SSs
Peter_SSs
H
  • Solved
Range.Formula does not lead to cell "selected" or "active"
Replies
5
Views
97
heathball
H

Forum statistics

Threads
1,214,591
Messages
6,120,432
Members
448,961
Latest member
nzskater

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top