Need to find specific values in range using loop

Hey everyone. I am currently trying to sort through several rows and columns of data to find specific values which meet the criteria I have inserted into my present code. I have several columns of data which contain for individual graphs. As of now the code is able to loop through the Column A and find the numbers which meet the criteria, and then insert them into either a new column or a new sheet. What I would like for the code to do is sort through each column, find all the numbers that meet the criteria, and then place all of those numbers into one column in a new spreadsheet. The code I have written is basically a peak-finding program that is as follows:

Sub FindMaxForce()

b = 1

For Row = 1 To 10000

Set CellBefore = Worksheets("Sheet1").Range("A1").Offset(Row - 1, 0)
Set CellMid = Worksheets("Sheet1").Range("A1").Offset(Row, 0)
Set CellAfter = Worksheets("Sheet1").Range("A1").Offset(Row + 1, 0)

If CellMid = "" Then Exit Sub
If CellMid >= CellBefore And CellMid >= CellAfter And CellMid >= 30 Then
Cells(b, 3) = CellMid
b = b + 1
CellBefore.Activate

End If

Next Row

End Sub

I am grateful for any suggestions anyone out there might have for me. Thanks!

I made some minor changes

Sub FindMaxForce()

b = 1

For Row = 1 To 10000

Set CellBefore = Worksheets("Sheet1").Range("A1").Offset(Row - 1, 0)
Set cellmid = Worksheets("Sheet1").Range("A1").Offset(Row, 0)
Set CellAfter = Worksheets("Sheet1").Range("A1").Offset(Row + 1, 0)

If cellmid = "" Then Exit Sub
If cellmid >= CellBefore And cellmid >= CellAfter And cellmid >= 30 Then
'Cells(b, 3) = CellMid

Worksheets("sheet2").Range("a" & b).Value = cellmid
b = b + 1

ActiveCell.Offset(Row - 1, 0).Activate

End If

Next Row

End Sub

Thanks for the post. Unfortunately the modified code is still doing the same thing as the previous one (only gathering numbers from column A). Do you think this is due to the Range("A1") criterion in the code? I've tried several different things, but to no avail. If you have any more suggestions I would appreciate your feedback.

I was guessing that you want to check your criteria for 10 columns, you can change the number of columns you want the check criteria by changing first "for loop"

Sub FindMaxForce()

b = 1

For col = 1 To 10
' MsgBox col
For Row = 1 To 10000

Set CellBefore = Worksheets("Sheet1").Cells(1, col).Offset(Row - 1, 0)
Set cellmid = Worksheets("Sheet1").Cells(1, col).Offset(Row, 0)
Set CellAfter = Worksheets("Sheet1").Cells(1, col).Offset(Row + 1, 0)

If cellmid = "" Then GoTo a:
If cellmid >= CellBefore And cellmid >= CellAfter And cellmid >= 30 Then
'Cells(b, 3) = CellMid

Worksheets("sheet2").Range("a" & b).Value = cellmid
b = b + 1

CellBefore.Activate

End If

Next Row
a:
Next
End Sub

I've tried the new changes, but I am still having trouble getting the code to work. When I try to run it, I get a "type mismatch" error on the line after the three Sets. Do you think it doesn't recognize the Col variables?

I forgot to mention that in nearly all of the columns there are several #DIV/0! errors present. Is there a code short subcode I could add that would look through the range first, enter values of 0 for these errors, and then start to check again? When i went through and deleted these errors manually and re-compiled the program it worked fine! Thanks again for your help.

Awesome! Thanks again for all the help!