Selecting range in a column based on another cell value

Hello all,

I have a spreadsheet that has values that go up by intervals of 10 that extends from 500 to 1500; however each value occurs more than once (so the value 500 might occur 150 times, and 510 might occur 285 times etc). This data is in Column A. In cell B1, I have a value (e.g. 886). I want to be able to select the points before and after that interval. So it select all cells that are contain 880 and 890 and copies them to cell C1. Also if the cell in B1 changes, the range that is selected also changes to select the appropriate range of cells.

Any help would be greatly appreciated!

Thanks!

Edit: Sorry for the error, but I've cross posted this to "https://www.mrexcel.com/forum/excel-questions/1062781-selecting-range-column-between-two-values.html#post5103684"

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

nekmat, Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Please make sure to include the full link, so that other members will be permitted to continue helping you

Note that if you have fewer than 10 posts, you may need to type the link, or past it in pieces (or ask a mod to do that for you)

https://www.mrexcel.com/forum/excel-...wo-values.html

Try this:

Sub Nekmat(): Dim Num As Long, S As String, R As Range
Num = Int(Cells(1, 2) / 10) * 10
If Cells(1, 1) = Num Then S = Cells(1, 1).Address Else _
Set R = Range("A:A").Find(Num, lookat:=xlWhole): S = R.Address
Set R = Range("A:A").Find(Num + 20, lookat:=xlWhole).Offset(-1, 0)
S = S & ":" & R.Address: Cells(1, 3) = S
End Sub

Thank you so much. This works perfectly.

Could you explain to me how the lines below work:

If Cells(1, 1) = Num Then S = Cells(1, 1).Address Else _
Set R = Range("A:A").Find(Num, lookat:=xlWhole): S = R.Address
Set R = Range("A:A").Find(Num + 20, lookat:=xlWhole).Offset(-1, 0

Also is there an extra line that you could add that copies all the contents from the range to column D.

Thank you so much for your help.

You're welcome and thanks for the rep!

Well, if you have a continued line ( space underscore) it's like having your "If" one a single line and, with the colon you can add an action without needing the "End If":

If Cells(1, 1) = Num Then S = Cells(1, 1).Address Else _
Set R = Range("A:A").Find(Num, lookat:=xlWhole): S = R.Address

The next line is just a regular action to concatenate the tail of the range:

Set R = Range("A:A").Find(Num + 20, lookat:=xlWhole).Offset(-1, 0)

Maybe:

Sub Nekmat(): Dim Num As Long, S As String, R As Range
Num = Int(Cells(1, 2) / 10) * 10
If Cells(1, 1) = Num Then S = Cells(1, 1).Address Else _
Set R = Range("A:A").Find(Num, lookat:=xlWhole): S = R.Address
Set R = Range("A:A").Find(Num + 20, lookat:=xlWhole).Offset(-1, 0)
S = S & ":" & R.Address: Cells(1, 3) = S
Range(S).Copy Cells(1,4)
End Sub