I've decided to ask this question through examples. I tried to expla开发者_Go百科in the entire project but it seemed confusing if you weren't already working on the same project so here we go.

As a user of the code I'm writing you need to decide which columns you want the program to actually grab and where to write all of the calculations to. There are nine columns total that the user can play with, though I'll focus on the first four so it doesn't get too complex.

The first two are the columns that my program needs in order to do it's calculations (explained later) and is really the basis for the entire project to run. Let's call them Data_Before and Data_After.

The next two columns are the calculations done from the information in Data_Before and Data_After. These two columns are Diff and Percent. Like the headers suggest Diff is Data_After - Data_Before and Percent is Diff / Data_Before.

As of now the columns have to be right next to each other. So if Data_Before is in column "A" then Data_After has to appear in "B" with Diff in "C" and Percent in "D". This is because of my calculations coming from the record macro function. The code that populates a cell in Diff and Percent are below

' Diff column
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]" 
' Percent column
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-3]" 

Sorry for the weird coloring in the code, the "Diff column" and "Percent column" are comments. Anyway, here we have the FormulaR1C1 which makes the program inflexible. The question being, how to do find the distance between the Diff column and the Data_Before or Data_After column so that the user can have the Data_Before in column "A" and Data_After in column "B" but the Diff and Percent columns wherever they want?

Sorry for the length of the question.

---

I only just saw your comment on the set-up of your program after I'd finished the below, hope it can help in some way.

Sub InsertDiffPercentCalc(ByVal rngDataBeforeColumn As Excel.Range, ByVal rngDataAfterColumn As Excel.Range, ByVal rngTargetDiffColumn As Excel.Range, ByVal rngTargetPercentColumn As Excel.Range)

' Diff column
rngTargetDiffColumn.FormulaR1C1 = "=RC" & CStr(rngDataAfterColumn.Column) & "-RC" & CStr(rngDataBeforeColumn.Column)
' Percent column
rngTargetPercentColumn.FormulaR1C1 = "=RC" & CStr(rngTargetDiffColumn.Column) & "/RC" & CStr(rngDataBeforeColumn.Column)

End Sub

Based on your comment, you can create a parameterless Sub, but in any case you still have to determine the target columns (or at least their addresses) - how are you doing this? edit just seen your other comment.

Sub InsertDiffPercentCalc()

Dim rngDataBeforeColumn As Excel.Range
Dim rngDataAfterColumn As Excel.Range
Dim rngTargetDiffColumn As Excel.Range
Dim rngTargetPercentColumn As Excel.Range

Set wksSettings = ThisWorkbook.Sheets("<settings sheet name>")
Set wksTarget = Application.Workbooks("<target workbook name>").Sheets("<target sheet name>")

Set rngDataBeforeColumn = wksTarget.Range(wksSetting.range("A2").Value & ":" & wksSetting.range("A2").Value)
Set rngDataAfterColumn = wksTarget.Range(wksSetting.range("B2").Value & ":" & wksSetting.range("B2").Value)
Set rngTargetDiffColumn = wksTarget.Range(wksSetting.range("C2").Value & ":" & wksSetting.range("C2").Value)
Set rngTargetPercentColumn = wksTarget.Range(wksSetting.range("D2").Value & ":" & wksSetting.range("D2").Value)

' Diff column
rngTargetDiffColumn.FormulaR1C1 = "=RC" & CStr(rngDataAfterColumn.Column) & "-RC" & CStr(rngDataBeforeColumn.Column)
' Percent column
rngTargetPercentColumn.FormulaR1C1 = "=RC" & CStr(rngTargetDiffColumn.Column) & "/RC" & CStr(rngDataBeforeColumn.Column)

End Sub

where wksSetting is the name of your settings sheet and wksTarget is the name of your target (output) sheet.

---

Per your comment, it seems you know exactly where to put the output. Right? So, if column B was data after and column A was data before and the user wanted the diff column in column G, you could use this for your diff formula:

Range("G1").Formula = "=B1-A1"

Or am I misunderstanding?

Edit

You're probably after something like this:

sheet1.Columns(15).column - sheet1.Columns(13).column ' returns 2

You could use that to create an R1C1 formula.