# Problem with equation for dynamic column

#### Lethal81

##### New Member
I am stuck on the below. I am sure its an easy problem, but I am not seeing it.

This code works well, and is doing exactly what I want it to do, with one exception.

As you can probably tell, this is creating new columns after column E, for each named spreadsheet in the range bounded by cell B1, and cell D1, but ignoring the sheets called "input" and "summary" (which are sheets 1 and 2). It is then copying the formulas in E into those columns (I did this because all the formulas down the column are not the same). The formulas from E pull data from the respective tabs.

It is then creating a new column 2 columns out, putting an equation in row 8 of the new column, which is perfect.

Here is the problem, that bolded "F8"......I need that to be the dynamically created figure in the last column. So for instance, if B1 was "3" and D1 was "5", it would create columns in F-H, and a new column in J with the equation. I need the column in J in this case to be "d8-h8", but if say B1 was "3" and D1 was "4", I would need it to be "d8-g8", which would now be in the created column i.

Any input would be great.

Bonus if there is an easy way to put text in row 6 on the created equation column as part of this.

Sub WSNames()
Dim x As Integer
Dim count As Integer
Sheets("Summary").Range("F6:AZ100").ClearContents

count = 6
For x = Sheets("Summary").Range("B1") To Sheets("Summary").Range("D1")

Select Case Worksheets(x).Name
Case "Input", "Summary"
Case Else
Sheets("Summary").Cells(6, count).Value = Worksheets(x).Name
Sheets("Summary").Range("\$E\$8:\$E\$94").Copy (Sheets("Summary").Cells(8, count))

count = count + 1
End Select

Next x

With Worksheets("Summary")
NextCol = .Cells(6, Columns.count).End(xlToLeft).Column + 2
LastRow = .Cells(Rows.count, 1).End(xlUp).Row + 1
.Cells(8, NextCol).Resize(LastRow - 1).Formula = "=\$d8-f8"
End With

End Sub

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### mikerickson

##### MrExcel MVP
Use R1C1 notation. If the column you landed on for the f8 formula was column h, you could use this. If it was a different one, change the [-1].

VBA Code:
``.Cells(8, NextCol).FormulaR1C1 = "RC4 - RC[-2]"``

#### Lethal81

##### New Member
Thank you mikerickson, that worked perfectly!

So, follow up question, would I need to use something similar to fill down based on RC[-2]?

I know I need something like this

LastRow = Range("RC[-2]" & Rows.Count).End(xlUp).Row

Range("Equation Column, row8: " & "Equation Column" & LastRow).FillDown

That's the logic, but I'm not sure the syntax of anything bolded, or even it can be written that way.....

#### Lethal81

##### New Member
Nevermind I figured it out.

Thank you very much!

Replies
10
Views
89
Replies
0
Views
112
Replies
4
Views
181
Replies
8
Views
253
Replies
1
Views
110

1,127,505
Messages
5,625,199
Members
416,080
Latest member
blemon

### 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.

### Which adblocker are you using?

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

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