Solved: newbie problem

[emem]

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,

[vba]
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("D265536") '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

[/vba]
If anyone can help me out I'd be grateful, like I said I'm very new to all this,

Many Thanks

Em

[Bob Phillips]

[vba]

Sub CheckMass()
Dim topCel As Range, bottomCel As Range
Dim sourceRange As Range, targetRange 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")
Exit Sub
End If ' test if imported mass is empty
Set sourceRange = Range(topCel, bottomCel)
Set targetRange = Range("E1") 'where data copied to
numofRows = sourceRange.Rows.Count 'number of rows gives number of times looped thru' imported mass
x = 1 'top of imported mass colum eg A1
For i = 1 To numofRows
If IsNumeric(sourceRange(i)) Then
If sourceRange(i).Value > sourceRange(i).Offset(0, 2).Value And _
sourceRange(i).Value < sourceRange(i).Offset(0, 3).Value Then
targetRange.Offset(x, 0).Value = sourceRange(i).Value
x = x + 1
End If
End If
Next i
End Sub
[/vba]

[emem]

Thanks for that, but I don't think I explained the problem well..sorry

I've attached the worksheet to see if it can explain it better than me

The real one will have about 5000 masses to check against a theoretical mass list, this one is just made up of a few random numbers.

It's for a research project-I'm a biochemist and I'm just learning VBA to automate many of the labs Excel's processes.

I'd love any help you can give me.

Many Thanks

Em

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim NextRow As Long
Dim res As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
NextRow = 1
For i = 2 To LastRow
res = .Evaluate("SUMPRODUCT(--(A" & i & ">=C2:C" & LastRow & ")," & _
"--(A" & i & "<=D2" & LastRow & "))")
If res > 0 Then

NextRow = NextRow + 1
.Cells(NextRow, "E").Value = .Cells(i, "A").Value
End If
Next i
End With

End Sub
[/vba]

[emem]

Thanks,

This really helps, you're a star!!

seriously this will save so much time, many, many thanks

All the Best

Ems