Mike Guest98
New Member
- Joined
- Jun 4, 2018
- Messages
- 42
Hi
I’m not sure if this is to much to ask for but it has taken quite a bit of time to really write out what I’m trying to accomplish using VBA in this program. I hope you can help.
1) I’m trying to find the location of an entered number, any number 1 to 100 is the choice. The number is entered in cell A1, say the number 4 is used (see my Excel spreadsheet at the end for everything in my examples below).
The program must search the following 6 column locations: D1:D12,A16:A30, D16:D30,G16:G30,J16:J30,M16:M30 for the number 4.
All 6 column ranges above have a number 4 located in them. What’s important is that there is only one number 4 with two adjacent cells to the right of it with numbers in them, the only exception is column D1:D12 which only has one adjacent column to the right of it, see next paragraph for details. Note: I realize that there are two number 4 listed in my Excel spreadsheet with numbers in the adjacent column to a 4 but it’s only to show as an example, this would not happen in reality, only one 4 would have a positive result.
I need the program to do the following:
In only column (D1:D12) I need the program to find the number entered in cell A1 (4) that has numbers in only one adjacent column to the right of it, see cell D4 and cell E4 (7-10). This would be considered a positive result. I need the program to do the following:
1 – I need the program to copy the adjacent cell contents located at one of the following: E1:E12,B16:B30,E16:E30,H16:H30,K16:K30,N16:N30. In the first example of E4
(7-10)copy the contents to A11.
2 -Move the contents in the adjacent column E4 in this example. The program would always use the first number of the set, 7 in this case, as the reference number. The program would then search the following 12 cells for the reference number: K1:K2:K3:K4:K5:K6:K7:K8:K9:K10:K11:K12.
Cell K1 is row 1
Cell K2 is row 2
Cell K3 is row 3
Cell K4 is row 4
Cell K5 is row 5
Cell K6 is row 6
Cell K7 is row 7
Cell K8 is row 8
Cell K9 is row 9
Cell K10 is row 10
Cell K11 is row 11
Cell K12 is row 12
In this example it would find 7 in K7 and place the contents in row L7, a “unique location” every time. This is an ongoing process so every time a move occurs for the reference number 7-* as an example, in row K7 it must use the next cell to the right of the previous one, the next cell would be L7, M7, N7 and so on. This would occur for all rows and not overwrite the contents in any one cell. After this process the cell E4 would be blank. Cells J1 to J12 and K1 to K12 never move or get copied.
3 - If the program does not find a 4 in D1:D12 with a value in the adjacent cell to the right of it the program must search the remaining columns A16:A30,D16:D30,G16:G30,J16:J30,M16:M30. In these columns a positive result would be finding the 4 with two adjacent cells in the same row to right of it that contain numbers. So in this example when the program finds a 4 in G21, a value in H21 (1-5) and I21 (7.89) it would be a positive result. If their was no value in H21 or I22 it would be a negative result and the program would continue to look for a positive result.
4 -After the program has a positive result I need the program to do the following three things:
First is to move (same as above for columns E1 to E12) the cell contents in H21 (1-5). The program would always use the first number, 1 in this case, as the reference number and would search the following cells for the reference number. The program would then search the following 12 cells for the reference number: K1:K2:K3:K4:K5:K6:K7:K8:K9:K10:K11:K12.
Cell K1 is row 1
Cell K2 is row 2
Cell K3 is row 3
Cell K4 is row 4
Cell K5 is row 5
Cell K6 is row 6
Cell K7 is row 7
Cell K8 is row 8
Cell K9 is row 9
Cell K10 is row 10
Cell K11 is row 11
Cell K12 is row 12
In this example it would find 1 in K1 and place the contents in row L1. This is an ongoing process so every time a copy and move occurs for the reference number 1 as an example, in row K1 it must use the next cell to the right of the previous one (using number example of 1-* the next cell would be L1, M1, N1 and so on, for all rows) and not overwrite the contents in any one cell. After this process the cell H21 would be blank. Cells J1 to J12 and K1 to K12 never move or get copied.
5 - Delete the contents in cell L21.
2) The next step is to have the program move all the number sets down in the column in the 6 column ranges E1:E12,B16:B30,E16:E30,H16:H30,K16:K30,N16:N30.
In only column E1:E12 I need the program to move the set of numbers (could be more than one set of numbers in the range) down to the following cell below it and increase the last number in the set (no limit to the count). So in the example of cell E1 (8-16) would move to E2 and become 8-17 (cell E1 would be blank when after the move).
When sets of number are located in cell E12 they would move to E1 but still increase the last number and would go round and round. Need help on the next one, how does the program move contents down from one cell to the next if the next cell is full without overwriting it and then move that one down, if this makes sense. An example is cell E12, how does it move up to E1 if E1 is full but has to be moved, which goes first?
For the remaining column ranges B16:B30,E16:E30,H16:H30,K16:K30,N16:N30 I need the program to move the set of numbers (could be more than one set of numbers in the range) down to the following cell below it and increase the last number in the set (no limit to the count). So in the example of cell N18 (5-3) it would move it to N19 and become 5-4 (delete the contents in N18 when finished). The second adjacent column to the right of the above, N18 in this case and located in only the following locations: C16:C30,F16:F30,I16:I30,L16:L30,O16:O30 have cells that contain values that must move but with no change in number. Cell O18 would move down to O19 and still have the number of 9.99. After the operation N18 and O18 would then be blank).
The two exceptions are if the last number is a 15, see cell B30 (8-15). Two things must happen:
1) I need the program to reference the number in the following 5 possible cells:
Cell B30 go to cell B32, reference number 1
Cell E30 go to cell E32, reference number 2
Cell H30 go to cell H32, reference number 3
Cell K30 go to cell K32, reference number 4
Cell N30 go to cell N32, reference number 5
Using example B30 the reference number is 1 (B32) so the program would search the range in column D1:D12 for the number 1 and move the set of numbers and increase the last digit, so it would become 8-16 in cell number E1.
2) The value in cell C31 must be moved via referencing the first number in cell B30 as a reference and search the following 10 possible cells: A35,D35,G35,J35,M35,Q35,A39,D39G39,J39,M39,O39 to find that number (in this example 8 would be cell D39 and the number 2.22 would be moved to cell D40.
After this operation there is no contents in either cell B30 and C30.
4) The last step is to move the contents located in cell A11 (7-10) in this example. I need the program to reference the range of cells D1:D12 and find the first number of the cell contents (in this case 7) then use the cell below (D8, number 8) and to search the following cells: A16, D16, G16, J16, M16 and find the number 8. The correct cell would be D16 but the program would place the set of numbers in the adjacent cell E16. It would would use the first numbers 7 (in this example) in the set and the last number in the set would always change to 1 once it comes from cell E16 and become 7-1.
I’m not sure if this is to much to ask for but it has taken quite a bit of time to really write out what I’m trying to accomplish using VBA in this program. I hope you can help.
1) I’m trying to find the location of an entered number, any number 1 to 100 is the choice. The number is entered in cell A1, say the number 4 is used (see my Excel spreadsheet at the end for everything in my examples below).
The program must search the following 6 column locations: D1:D12,A16:A30, D16:D30,G16:G30,J16:J30,M16:M30 for the number 4.
All 6 column ranges above have a number 4 located in them. What’s important is that there is only one number 4 with two adjacent cells to the right of it with numbers in them, the only exception is column D1:D12 which only has one adjacent column to the right of it, see next paragraph for details. Note: I realize that there are two number 4 listed in my Excel spreadsheet with numbers in the adjacent column to a 4 but it’s only to show as an example, this would not happen in reality, only one 4 would have a positive result.
I need the program to do the following:
In only column (D1:D12) I need the program to find the number entered in cell A1 (4) that has numbers in only one adjacent column to the right of it, see cell D4 and cell E4 (7-10). This would be considered a positive result. I need the program to do the following:
1 – I need the program to copy the adjacent cell contents located at one of the following: E1:E12,B16:B30,E16:E30,H16:H30,K16:K30,N16:N30. In the first example of E4
(7-10)copy the contents to A11.
2 -Move the contents in the adjacent column E4 in this example. The program would always use the first number of the set, 7 in this case, as the reference number. The program would then search the following 12 cells for the reference number: K1:K2:K3:K4:K5:K6:K7:K8:K9:K10:K11:K12.
Cell K1 is row 1
Cell K2 is row 2
Cell K3 is row 3
Cell K4 is row 4
Cell K5 is row 5
Cell K6 is row 6
Cell K7 is row 7
Cell K8 is row 8
Cell K9 is row 9
Cell K10 is row 10
Cell K11 is row 11
Cell K12 is row 12
In this example it would find 7 in K7 and place the contents in row L7, a “unique location” every time. This is an ongoing process so every time a move occurs for the reference number 7-* as an example, in row K7 it must use the next cell to the right of the previous one, the next cell would be L7, M7, N7 and so on. This would occur for all rows and not overwrite the contents in any one cell. After this process the cell E4 would be blank. Cells J1 to J12 and K1 to K12 never move or get copied.
3 - If the program does not find a 4 in D1:D12 with a value in the adjacent cell to the right of it the program must search the remaining columns A16:A30,D16:D30,G16:G30,J16:J30,M16:M30. In these columns a positive result would be finding the 4 with two adjacent cells in the same row to right of it that contain numbers. So in this example when the program finds a 4 in G21, a value in H21 (1-5) and I21 (7.89) it would be a positive result. If their was no value in H21 or I22 it would be a negative result and the program would continue to look for a positive result.
4 -After the program has a positive result I need the program to do the following three things:
First is to move (same as above for columns E1 to E12) the cell contents in H21 (1-5). The program would always use the first number, 1 in this case, as the reference number and would search the following cells for the reference number. The program would then search the following 12 cells for the reference number: K1:K2:K3:K4:K5:K6:K7:K8:K9:K10:K11:K12.
Cell K1 is row 1
Cell K2 is row 2
Cell K3 is row 3
Cell K4 is row 4
Cell K5 is row 5
Cell K6 is row 6
Cell K7 is row 7
Cell K8 is row 8
Cell K9 is row 9
Cell K10 is row 10
Cell K11 is row 11
Cell K12 is row 12
In this example it would find 1 in K1 and place the contents in row L1. This is an ongoing process so every time a copy and move occurs for the reference number 1 as an example, in row K1 it must use the next cell to the right of the previous one (using number example of 1-* the next cell would be L1, M1, N1 and so on, for all rows) and not overwrite the contents in any one cell. After this process the cell H21 would be blank. Cells J1 to J12 and K1 to K12 never move or get copied.
5 - Delete the contents in cell L21.
2) The next step is to have the program move all the number sets down in the column in the 6 column ranges E1:E12,B16:B30,E16:E30,H16:H30,K16:K30,N16:N30.
In only column E1:E12 I need the program to move the set of numbers (could be more than one set of numbers in the range) down to the following cell below it and increase the last number in the set (no limit to the count). So in the example of cell E1 (8-16) would move to E2 and become 8-17 (cell E1 would be blank when after the move).
When sets of number are located in cell E12 they would move to E1 but still increase the last number and would go round and round. Need help on the next one, how does the program move contents down from one cell to the next if the next cell is full without overwriting it and then move that one down, if this makes sense. An example is cell E12, how does it move up to E1 if E1 is full but has to be moved, which goes first?
For the remaining column ranges B16:B30,E16:E30,H16:H30,K16:K30,N16:N30 I need the program to move the set of numbers (could be more than one set of numbers in the range) down to the following cell below it and increase the last number in the set (no limit to the count). So in the example of cell N18 (5-3) it would move it to N19 and become 5-4 (delete the contents in N18 when finished). The second adjacent column to the right of the above, N18 in this case and located in only the following locations: C16:C30,F16:F30,I16:I30,L16:L30,O16:O30 have cells that contain values that must move but with no change in number. Cell O18 would move down to O19 and still have the number of 9.99. After the operation N18 and O18 would then be blank).
The two exceptions are if the last number is a 15, see cell B30 (8-15). Two things must happen:
1) I need the program to reference the number in the following 5 possible cells:
Cell B30 go to cell B32, reference number 1
Cell E30 go to cell E32, reference number 2
Cell H30 go to cell H32, reference number 3
Cell K30 go to cell K32, reference number 4
Cell N30 go to cell N32, reference number 5
Using example B30 the reference number is 1 (B32) so the program would search the range in column D1:D12 for the number 1 and move the set of numbers and increase the last digit, so it would become 8-16 in cell number E1.
2) The value in cell C31 must be moved via referencing the first number in cell B30 as a reference and search the following 10 possible cells: A35,D35,G35,J35,M35,Q35,A39,D39G39,J39,M39,O39 to find that number (in this example 8 would be cell D39 and the number 2.22 would be moved to cell D40.
After this operation there is no contents in either cell B30 and C30.
4) The last step is to move the contents located in cell A11 (7-10) in this example. I need the program to reference the range of cells D1:D12 and find the first number of the cell contents (in this case 7) then use the cell below (D8, number 8) and to search the following cells: A16, D16, G16, J16, M16 and find the number 8. The correct cell would be D16 but the program would place the set of numbers in the adjacent cell E16. It would would use the first numbers 7 (in this example) in the set and the last number in the set would always change to 1 once it comes from cell E16 and become 7-1.
Last edited by a moderator:
