How to find a number Using VBA

[Mike Guest98]

Using VBA (must be used because there is more to this coding) I need to find the location of a manually entered number, 1 to 10 are the only choices. The number gets entered in cell C17.
As an example say I’m looking for number 7 (could be any number 1- 10).

I have 5 columns where the above selected number is located and only there:


C30 TO C300,
E30 TO E300,
G30 TO G300,
H30 TO H300
L30 TO L300.


Each of the above columns has numerous 7’s in them but only one 7 has a value in the cell (use “1-1”) in the next column to the right of it. So, as an example if it finds 7 in C75 and a value of 1-1 in D76 it is a positive and if their was nothing (blank) in D76 it would be negative.

Not an easy one. Thanks in advance for any help.








<style type="text/css">p { margin-bottom: 0.1in; direction: ltr; color: rgb(0, 0, 10); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }</style>

 

[rpaulson]

try this

Sub do_it()

n = [C17]

For Each cell In Range("C30:C300,E30:E300,G30:G300,H30:H300,L30:L300")
If cell.Value = n And Range(cell.Address).Offset(0, 1) = "1-1" Then
MsgBox "Found a postivive result in " & cell.Address
End If
Next

End Sub

hth,
Ross

 

[Mike Guest98]

THANK-YOU SO MUCH, YOU’RE AMAZING! Not sure if I’m allowed to complete this program here by asking for the last step:


When the program has a positive result I would like to copy and paste it to a “unique location”. So if I use the original request above and it finds the number 7 located in C75 it’s the contents in C76 I would like to copy and paste. There are 10 rows to place the contents in:


Cell H1 is row 1

Cell H2 is row 2
Cell H3 is row 3
Cell H4 is row 4
Cell H5 is row 5
Cell H6 is row 6
Cell H7 is row 7
Cell H8 is row 8
Cell H9 is row 9
Cell H10 is row 10


In my above example the cell contents (C75) are 1-1 so it would use the first number of 1 and place it I1 (if the contents was 2-1 it would go to I2 (using the first number of 2) and so on). This is an ongoing process so every time a copy and paste occurs it must use the next cell to the right of the previous one and not overwrite the contents in any cell.


I hope this is possible, I think it should be. Thanks so much for any assistance.




<style type="text/css">p { margin-bottom: 0.1in; direction: ltr; color: rgb(0, 0, 10); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }</style>