Results 1 to 5 of 5

Thread: Move cell nubers down

  1. #1
    Acolyte mike guest98's Avatar
    Join Date
    Jun 2018
    Posts
    22
    Articles
    0
    Excel Version
    2010

    Move cell nubers down



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi

    I have been using the VBA program below but I would like to change it to search the cells for numbers 1, 2, 3, 4. Can I use this program, with changes to the first line, and if so what is that first line?
    Wondering if someone could look at and provide any feedback.


    Code:
    Sub do_it()
    
    ????????? (to locate 1, 2,3,4, see no 1 notes below) 
        Set reg = CreateObject("VBScript.RegExp")
        reg.Pattern = "^[0-9]*\-[0-9]*$"
        reg.Global = True
        For Each cell In Range("B30,E30,H30,K30,N30")
            strVAL = cell.Offset(0, 1).Value
            If cell.Value = n And reg.test(strVAL) Then
                Range(E15).Value = StrVal
                   MsgBox "Found a postivive result in " & cell.Address
            End If
    
        Next
    
    
    End Sub
    1) I need the program to search cells B30,E30,H30,K30,N30 for any pairs of numbers that has a 15 in the last number, see cell B30 (8-15).

    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 D112 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.
    Last edited by Pecoflyer; 2018-08-11 at 08:28 AM.

  2. #2
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,552
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Please wrap code with code tags. Thank you
    Thank you Ken for this secure forum.

  3. #3
    Acolyte mike guest98's Avatar
    Join Date
    Jun 2018
    Posts
    22
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by mike guest98 View Post
    Hi

    I have been using the VBA program below but I would like to change it to search the cells for numbers 1, 2, 3, 4. Can I use this program, with changes to the first line, and if so what is that first line?
    Wondering if someone could look at and provide any feedback.


    <Sub do_it()>

    ????????? (to locate 1, 2,3,4, see no 1 notes below)
    <Set reg = CreateObject("VBScript.RegExp")>
    <reg.Pattern = "^[0-9]*\-[0-9]*$">
    <reg.Global = True>
    <For Each cell In Range("B30,E30,H30,K30,N30")>
    <strVAL = cell.Offset(0, 1).Value>
    <If cell.Value = n And reg.test(strVAL) Then>
    <Range(“E15”).Value = StrVal>
    <MsgBox "Found a postivive result in " & cell.Address>
    <End If>

    <Next>


    <End Sub>

    1) I need the program to search cells B30,E30,H30,K30,N30 for any pairs of numbers that has a 15 in the last number, see cell B30 (8-15).

    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 D112 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.
    Done

  4. #4
    Acolyte mike guest98's Avatar
    Join Date
    Jun 2018
    Posts
    22
    Articles
    0
    Excel Version
    2010
    Hi

    I'm requesting that this post be deleted. It doesn't make sense.
    Thanks
    Mike

  5. #5
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,552
    Articles
    0
    Excel Version
    2010 on Xubuntu
    What is it that does not make sense?
    I presume you could not see your posts after adding ( quote) tags. This sometimes happens and is now corrected.
    To add code tags click Edit post - " Go advanced", select your code and click the #button ( I added them for you in post #1 this time
    Thanks

    Edit : has this anything to do with https://www.excelguru.ca/forums/show...ng-VBA-program ?
    Last edited by Pecoflyer; 2018-08-11 at 09:14 AM.
    Thank you Ken for this secure forum.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •