PDA

View Full Version : Solved: newbie problem



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

Bob Phillips
12-17-2007, 05:31 AM
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

emem
12-17-2007, 06:16 AM
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 :blush

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
12-17-2007, 07:54 AM
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:D" & LastRow & "))")
If res > 0 Then

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

End Sub

emem
12-17-2007, 08:16 AM
Thanks,

This really helps, you're a star!! :bow:

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

All the Best

Ems