mike guest98
New member
- Joined
- Jun 6, 2018
- Messages
- 28
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- 2010
Hi
I have been using the VBA program on Excel 2010, Windows 7 below but I would like to change it from searching one number, entered in cell C17 to one set of numbers (see my notes below). Can this program be used for that purpose? Wondering if someone could look at it and provide any feedback.
1) Currently the program searches based on what’s entered in cell C17. I would to change the search to cells B30,E30,H30,K30,N30 for any pairs of numbers that has a 15 in the last number, see cell B30 (eg. 8-15). If there is no positive result end the program.
If it finds a 15 two things must happen:
1- The value in the adjacent cell, C30 (2.22) in this example, must be divided by 10 (rounding up) and each amount moved to the following cells: A35,D35,G35,J35,M35,Q35,A39,D39G39,J39,M39,O39. The only issue is the amount divided can not exceed 10.00. The program would divide the 10.00 but anything over that number would be moved via referencing the first number in cell B30 (8) and search the following 10 possible cells: A35,D35,G35,J35,M35,Q35,A39,D39G39,J39,M39,O39 to find 8, in this example, and would find it in cell D39 and the remaining number, if any, would then be ADDED to any number in cell D40 (note: there is an error in my Excel spreadsheet example, cell D40 should not be 2.22).
After this operation there are no contents in cell C30.
2- After a 15 positive result I also need the program to reference the following number in the following 5 cells:
Cell B30 would go to cell B32 so the reference number is 1
Cell E30 would go to cell E32 so the reference number is 2
Cell H30 would go to cell H32 so the reference number is 3
Cell K30 would go to cell K32 so the reference number is 4
Cell N30 would go to cell N32 so the reference number is 5
Using example B30 the reference number is 1 so the program would search the range in column D1
12 for the number 1 and move the set of numbers there and increase the last digit, so it would become 8-16 in cell number E1. After this operation there are no contents in cell B30.
I have been using the VBA program on Excel 2010, Windows 7 below but I would like to change it from searching one number, entered in cell C17 to one set of numbers (see my notes below). Can this program be used for that purpose? Wondering if someone could look at it and provide any feedback.
Code:
Sub do_it()
n = [C17]
For Each cell In Range("B30,E30,H30,K30,N30")
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 Sub1) Currently the program searches based on what’s entered in cell C17. I would to change the search to cells B30,E30,H30,K30,N30 for any pairs of numbers that has a 15 in the last number, see cell B30 (eg. 8-15). If there is no positive result end the program.
If it finds a 15 two things must happen:
1- The value in the adjacent cell, C30 (2.22) in this example, must be divided by 10 (rounding up) and each amount moved to the following cells: A35,D35,G35,J35,M35,Q35,A39,D39G39,J39,M39,O39. The only issue is the amount divided can not exceed 10.00. The program would divide the 10.00 but anything over that number would be moved via referencing the first number in cell B30 (8) and search the following 10 possible cells: A35,D35,G35,J35,M35,Q35,A39,D39G39,J39,M39,O39 to find 8, in this example, and would find it in cell D39 and the remaining number, if any, would then be ADDED to any number in cell D40 (note: there is an error in my Excel spreadsheet example, cell D40 should not be 2.22).
After this operation there are no contents in cell C30.
2- After a 15 positive result I also need the program to reference the following number in the following 5 cells:
Cell B30 would go to cell B32 so the reference number is 1
Cell E30 would go to cell E32 so the reference number is 2
Cell H30 would go to cell H32 so the reference number is 3
Cell K30 would go to cell K32 so the reference number is 4
Cell N30 would go to cell N32 so the reference number is 5
Using example B30 the reference number is 1 so the program would search the range in column D1
12 for the number 1 and move the set of numbers there and increase the last digit, so it would become 8-16 in cell number E1. After this operation there are no contents in cell B30.Attachments
Last edited by a moderator: