Excel: Selection of Cells in column Between 2

Closed
madambath - May 18, 2010 at 04:17 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 19, 2010 at 08:14 AM
All,

Selection of Cells in a column Between two Values

I am preparing a Statistical Data Sheet, for this I need to select and copy data from one sheet to another. I wish to select certain cells (From SHEET1) by entering the value of Cells (in SHEET2) from where selection to be started (example: 4152.15, in B1) and enter the value of the cell where selection to be ended (example: 4162.80, in B7). When the required cells are selected I need to offset the selection to another column corresponding to previously selected cells and paste it in SHEET2 by code

Selection.Offset(rowOffset:=0, columnOffset:=3).Activate
Selection.Copy
Sheets("Sheet2").Select
Range("F3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Provided below is my idea, if a better procedure for this is there please let me know. I have uploaded this file: https://authentification.site/files/22495431/SelectionHelp1.doc
Please see this for full view.

A B C D E F G F
1 71DS 4152.15 14.8 76.0 10.2 1.0 1.3 2.70
2 75 4156.10 - - 10.7 0.57 0.79 2.71
3 76 4157.30 - - 9.0 1.8 2.0 2.75
4 77 4158.10 - - 14.0 0.39 0.63 2.73
5 78DS 4158.90 13.2 79.8 11.4 6.6 6.8 2.72
6 79 4160.15 - - 5.5 0.21 0.27 2.71
7 81 4162.00 - - 8.7 1.6 1.9 2.71
8 82 4162.80 - - 6.0 <0.01 0.02 2.70
9 83DS 4164.15 15.8 72.2 9.5 1.0 1.3 2.76
10 84 4165.35 - - 7.9 0.28 0.39 2.71
11 85 4166.35 - - 8.3 0.32 0.44 2.70
12 86 4167.25 - - 7.1 0.01 0.02 2.71
13 87 4168.20 - - 16.3 0.21 0.36 2.70
14 88 4169.35 - - 7.2 0.62 0.73 2.75
15 89 4170.05 - - 11.3 4.7 4.9 2.71
16 90 4171.00 - - 11.9 0.09 0.12 2.71
17 91 4172.10 - - 10.2 1.5 1.8 2.72
18 92DS 4173.05 10.4 83.1 18.8 39. 58. 2.80
19 93 4174.90 - - 6.5 6.8 7.2 2.69
20 94 4175.00 - - - - - -
SHEET1

A B C D E F G
1 Enter Start Value Enter End Value Cells pasted from Sheet1
2 4152.15 4162.8 10.2 9.5 11.9
3 4164.15 4170.05 10.7 7.9 10.2
4 4171 4175 9 8.3 18.8
5 14 7.1 6.5
6 11.4 16.3 -
7 5.5 7.2
8 8.7 11.3
9 6
10

SHEET2


I want the user to type the start value in a designated Cell (in SHEET2, example: 4152.15) and in another Cell the End Value (example: 4162.80) so the selection should be from E1:E8 and to be copied and pasted in Sheet2 Cell E2

Thanks and Regards,
PramodKumar





Related:

2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 19, 2010 at 05:12 AM
Not exactly sure what you are asking and explained.

from what I understood, on sheet2, you would be entering a start value and end value and you would copy the data. what if the start value exists twice in that column etc. May be if you upload a sample excel file ( yeah I saw the word file), things might get more clearer to me at least.
Thanks for your reply.

I have uploaded a file: https://authentification.site/files/22514440/Stati_Trial2.xls
Please see this for to understand my idea about this subject.

Let me ask some additional things than my previous post.

In case for future use if I need the code to paste data from columns F, G & H of Sheet1 to Sheet2, such as Data F to: I, J & K; Data G to: M, N & O; Data H to: Q, R & S is it possible to create? What will happen if I insert few rows in Sheet1 and Sheet to above the present 1st row? Is it required to change codes in this scenario? As I am a fresher in Macros there are many doubts, hope I can find some solutions through the dedicated members of the forum.

Thanks and Regards,

PramodKumar
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 19, 2010 at 08:14 AM
From what I am getting the value range that you are defining may change. Like one day you may define range as 4152.15 - 4162.80 but other time you may have 3100-4500. so idea should be look at the define range in sheet2 and search for that value on sheet 1 to locate how many cells are to be copied and from there. One option would be as you have the start and end values defined. Other could be that you define address like B2 and B6 or you can have that start at B2 and get me next 6 cells etc. You would be the best person to know what is the best possible way of defining the range

For you future planning, it is possible. However again how the column should be pasted needs to be clarified. Like right now for each data range you have defined on sheet2, the values get copied from the first column Available from column e, so first range goes to e, 2nd to F and third to G. So how if you you want to copy 2 columns from sheet1, how will it go like e-f, g-h, and i-j ?
0