Help with simplifying my background substraction (data analysis)

[JB1409]

Dear all,

For some data analysis I have to do, I need to format some formulas inside my macro with a fixed background substraction. The code I've written for it is turning out really long now that my measurements are increasing. The following code was doable for 100 rows, but I'm approaching over 600 rows and want some help with simplifying what I'm doing here. There should be an easier way, I just can't find it anywere on the internet. Could you guys help me with this? Also for people having to use my macro in the future it would be way easier to interpret it if I can simplify what I do with a dedicated function. I'm enjoying learning to code in VBA a lot, it just gets hard to figure out the specific functions I can use sometimes.

The code I have now looks as follows and repeats for over 600 rows:

Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C601"), Type:=xlFillDefault
Range("C2").Select
ActiveCell.FormulaR1C1 = "='340 nm'!RC-'340 nm'!R2C3"
Range("C3").Select
ActiveCell.FormulaR1C1 = "='340 nm'!RC-'340 nm'!R3C3"
Range("C4").Select
ActiveCell.FormulaR1C1 = "='340 nm'!RC-'340 nm'!R4C3"
Range("C5").Select
ActiveCell.FormulaR1C1 = "='340 nm'!RC-'340 nm'!R5C3"
Range("C6").Select
ActiveCell.FormulaR1C1 = "='340 nm'!RC-'340 nm'!R6C3"
Range("C7").Select
ActiveCell.FormulaR1C1 = "='340 nm'!RC-'340 nm'!R7C3"
Range("C8").Select
ActiveCell.FormulaR1C1 = "='340 nm'!RC-'340 nm'!R8C3"
Range("C9").Select
ActiveCell.FormulaR1C1 = "='340 nm'!RC-'340 nm'!R9C3"
Range("C10").Select
ActiveCell.FormulaR1C1 = "='340 nm'!RC-'340 nm'!R10C3"

Thank you all very much for your help, it is much appreciated!

[arnelgp]

can you use:

Range("C2:C601").FormulaR1C1 ="='340 nm'!RC-'340 nm'!C[3]"

[JB1409]

can you use:

Range("C2:C601").FormulaR1C1 ="='340 nm'!RC-'340 nm'!C[3]"

Thank you for the suggestion. It seems to work partially now. I have a huge field of data from which I have to substract the backgroun in the "340 nm" tab from the "C" column. It does work if I use your suggestion as:

Range("C2:C601").FormulaR1C1 = "='340 nm'!RC-'340 nm'!C[0]"
Range("D2:D601").FormulaR1C1 = "='340 nm'!RC-'340 nm'!C[-1]"

The only problem now is that I basically have to repeat this function for each column. Is there a better way to do this? If I use the following I strike the whole field, but I always substract from the same column as the original data comes from, not the "C" column:

Range("C2:HZ601").FormulaR1C1 = "='340 nm'!RC-'340 nm'!C[0]"

I'm basically looking for a way to fix the "[0]" part to that specific column.

Thanks in advance for the help!

[p45cal]

Doesn't
Range("C2:D601").FormulaR1C1 = "='340 nm'!RC-'340 nm'!C3"
do it?

[JB1409]

Doesn't
Range("C2:D601").FormulaR1C1 = "='340 nm'!RC-'340 nm'!C3"
do it?

Thank you very much, this worked!

I've now put it in as:

Range("C2:HZ601").FormulaR1C1 = "='340 nm'!RC-'340 nm'!C3"

And it immediately fills the entire field that I want to cover!