emem
12-17-2007, 05:02 AM
Hi,
I'm very new to programming so this is probably an easy one. I have a spreadsheet with 5 columns. Column A has a list of number (Imported mass)as does B (theory mass). Column C is the value of column B minus 0.01%, Column D is the value of column B plus 0.01%.
Now what I would like to do is for excel to search through Column 1 (recorded Mass) and copy the value into Column E (Matched Masses) if it is >= Column 3 AND <=Column 4
So far I've got it to look at the first pair of less than and greater than, but I can't see how to loop it through all the values in Columns C AND D. And it also just see the first value which lies in between and stops.
Here's what i've got so far,
Sub CheckMass()
Dim topCel As Range, bottomCel As Range, _
sourceRange As Range, targetRange As Range, minerrtol As Range, Maxerrtol As Range
Dim x As Integer, i As Integer, numofRows As Integer, g As Integer, h As Integer
Set topCel = Range("A2") 'Imported mass
Set bottomCel = Range("A65536").End(xlUp) 'imported mass
If topCel.Row > bottomCel.Row Then
MsgBox ("Oi, copy and paste the masses, muppet")
End If ' test if imported mass is empty
Set sourceRange = Range(topCel, bottomCel)
Set targetRange = Range("E2:E65536") 'where data copyied to
Set minerrtol = Range("C2:C65536") 'theory mass minus 0.01%
Set Maxerrtol = Range("D2:D65536") 'theory mass plus 0.01%
numofRows = sourceRange.Rows.Count 'number of rows gives number of times looped thru' imported mass
crows = minerrtol.Rows.Count
drows = Maxerrtol.Rows.Count
x = 1 'top of imported mass colum eg A1
g = 1 'top of MinErrTol eg C1
h = 1 'top of MaxErrTol eg D1
For i = 1 To numofRows
If Application.IsNumber(sourceRange(i)) Then
If sourceRange(i) > minerrtol(g) And sourceRange(i) < Maxerrtol(h) Then
targetRange(x) = sourceRange(i)
x = x + 1
g = g + 1
h = h + 1
End If
End If
Next
End Sub
If anyone can help me out I'd be grateful, like I said I'm very new to all this,
Many Thanks
Em
I'm very new to programming so this is probably an easy one. I have a spreadsheet with 5 columns. Column A has a list of number (Imported mass)as does B (theory mass). Column C is the value of column B minus 0.01%, Column D is the value of column B plus 0.01%.
Now what I would like to do is for excel to search through Column 1 (recorded Mass) and copy the value into Column E (Matched Masses) if it is >= Column 3 AND <=Column 4
So far I've got it to look at the first pair of less than and greater than, but I can't see how to loop it through all the values in Columns C AND D. And it also just see the first value which lies in between and stops.
Here's what i've got so far,
Sub CheckMass()
Dim topCel As Range, bottomCel As Range, _
sourceRange As Range, targetRange As Range, minerrtol As Range, Maxerrtol As Range
Dim x As Integer, i As Integer, numofRows As Integer, g As Integer, h As Integer
Set topCel = Range("A2") 'Imported mass
Set bottomCel = Range("A65536").End(xlUp) 'imported mass
If topCel.Row > bottomCel.Row Then
MsgBox ("Oi, copy and paste the masses, muppet")
End If ' test if imported mass is empty
Set sourceRange = Range(topCel, bottomCel)
Set targetRange = Range("E2:E65536") 'where data copyied to
Set minerrtol = Range("C2:C65536") 'theory mass minus 0.01%
Set Maxerrtol = Range("D2:D65536") 'theory mass plus 0.01%
numofRows = sourceRange.Rows.Count 'number of rows gives number of times looped thru' imported mass
crows = minerrtol.Rows.Count
drows = Maxerrtol.Rows.Count
x = 1 'top of imported mass colum eg A1
g = 1 'top of MinErrTol eg C1
h = 1 'top of MaxErrTol eg D1
For i = 1 To numofRows
If Application.IsNumber(sourceRange(i)) Then
If sourceRange(i) > minerrtol(g) And sourceRange(i) < Maxerrtol(h) Then
targetRange(x) = sourceRange(i)
x = x + 1
g = g + 1
h = h + 1
End If
End If
Next
End Sub
If anyone can help me out I'd be grateful, like I said I'm very new to all this,
Many Thanks
Em