Problem with equation for dynamic column

Lethal81

New Member
Joined
Apr 10, 2017
Messages
16
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,951
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
Joined
Apr 10, 2017
Messages
16
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.....

I appreciate any advice in advance
 

Watch MrExcel Video

Forum statistics

Threads
1,127,028
Messages
5,622,291
Members
415,890
Latest member
macak333

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
Top