I am active in the field of analytical chemistry for my internship and wish to compare large datasets (two columns up to 15,000 rows). The main idea of this is that I have two columns with mass data (with 4 decimals) in which a macro should look for each mass in column one in column two, but with a mass defect/error. What this means is that the value does not perfectly correspond (due to instrumental errors in measurements) but should fall within a lower limit and an upper limit. When the macro has cycled through dataset2 to check each value, dataset1's checked cell is offset to the next (.Offset(1,0)) to repeat the search for that specific value. The upperlimit and lowerlimit are automatically adjusted.
To give an example (code format used, otherwise the table would not display correctly):
Value to check from dataset1 is 101.1048, mass error is 5 ppm (parts-per-million, 0.000005%),
so the lower limit is 101.1043 and the upper limit is 101.1053. So in the example shown below,
the mass in dataset2 falls within the boundaries, after which the macro should sum the intensity
(linked to the mass column) of all mass values from dataset2 that fall within the dataset1 limits
for the checked cell. So SumIntensity=105+209 in the example, if no corresponding value is found,
the intensity of the dataset1 is used (so 100).
**Dataset1** " **Dataset2**
Mass ' Intensity " Mass ' Intensity
''''''''''''''''''''''''''''''''''''''''''''''''''''
101.1048 ' 100 " 101.1045 ' 105
101.1272 ' 300 " 101.1051 ' 209
I am however not well experienced with VBA (I have only written some basic macros to compare values in the same dataset with a mass defect/error) and after countless attempts I have not yet been able to get the macro to work. My current code is as follows but keeps crashing (most likely due to the loops):
Sub CompareColumnsTest2()
Dim wscalc, wsdata, wscontrol As Worksheet
Set wscalc = Sheet2
Set wsdata = Sheet1
Set wscontrol = Sheet4
''-----------------------------------------------------------
''Compares datasets 1 and 2 in two steps:
''Looks up each Rounded Mass from dataset1 in dataset2 and substracting the relative intensity respectively
''Looks up each Rounded Mass from dataset 2 in dataset1 and if NOT present in dataset 1, copies Rounded Mass and (negative) Intensity
wscalc.Range("B3:B" & wscalc.Range("B" & Rows.Count).End(xlUp).Row).Copy
wscalc.Range("K3").PasteSpecial (xlPasteValues)
''Step one
Dim refcl, refint, massdefect, lowerlimit, upperlimit As Range
Set refcl = wscalc.Range("B3")
Set refint = wscalc.Range("D3")
Set pastecell = wscalc.Range("L3")
Set massdefect = wscontrol.Range("D4")
Set lowerlimit = wscalc.Range("Z2")
Set upperlimit = wscalc.Range("Z4")
Set checkcl = wscalc.Range("G3")
Set checkint = wscalc.Range("I3")
Dim refclnext, refintnext, checkclnext, pastecellnext As Range, sumint As Long
Do While Not IsEmpty(refcl)
Set refclnext = refcl.Offset(1, 0)
Set refintnext = refint.Offset(1, 0)
Set pastecellnext = pastecell.Offset(1, 0)
Set checkclnext = checkcl.Offset(1, 0)
Set checkintnext = checkint.Offset(1, 0)
sumint = 0
lowerlimit.Value = refcl / (1 + (massdefect / 1000000))
upperlimit.Value = refcl * (1 + (massdefect / 1000000))
Do While Not IsEmpty(checkcl)
If checkcl <= upperlimit And checkcl >= lowerlimit Then
sumint = sumint + checkint
End If
Set checkcl = checkclnext
Set checkint = checkintnext
Loop
Set pastecell.Value = refint - sumint
Set refcl = refclnext
Set refint = refintnext
Set pastecell = pastecellnext
Loop
End Sub
I hope my description is clear enough to be able to help me out. I do not ask of you to completely rewrite my code as that would ofcourse take a lot of time, but any tips/modifications would be highly appreciated.
Best, JamesLooks
Edit 1: Here are some screenshots showing some data and how the sheet is organized for cell references.
