Consulting

Results 1 to 5 of 5

Thread: Help with simplifying my background substraction (data analysis)

  1. #1
    VBAX Newbie
    Joined
    Dec 2022
    Posts
    4
    Location

    Help with simplifying my background substraction (data analysis)

    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!

  2. #2
    can you use:

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

  3. #3
    VBAX Newbie
    Joined
    Dec 2022
    Posts
    4
    Location
    Quote Originally Posted by arnelgp View Post
    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!

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,700
    Doesn't
    Range("C2:D601").FormulaR1C1 = "='340 nm'!RC-'340 nm'!C3"
    do it?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Newbie
    Joined
    Dec 2022
    Posts
    4
    Location
    Quote Originally Posted by p45cal View Post
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •