Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,424
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Macro to range value items

    I Would like a macro to range value the first value on the current sheet in Columns E to F directly after the text "Reconciliation" appears

    For eg if Reconciliation is in E 289 , then E 290 must be range used , If reconciliation is in G 301, then G 302 must be range valued etc

    Your assistance in this regard is most appreciated

    http://www.mrexcel.com/forum/excel-q...ues-items.html

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Howard,

    Not sure what you mean by range valued but here are a couple lines of code that will search columns E and F for "Reconciliation" and select the cell just below it.

    HTH,
    Maud

    Code:
    Public Sub GetNextCell()
        Set keyword = Cells.Columns("E:F").Find(What:="Reconciliation")
        keyword.Offset(1, 0).Select
    End Sub
    If you are looking for the cell address then
    Code:
    Public Sub GetNextCell()
        Set keyword = Cells.Columns("E:F").Find(What:="Reconciliation")
        x = keyword.Offset(1, 0).Address
        msgbox x
    End Sub
    Last edited by Maudibe; 2013-08-23 at 00:13. Reason: spelling

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,424
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Hi Maudibe

    Thanks for the assistance. What I meant by range value is to copy the value appearing directly below the text "Reconciliation" and to paste value. There is a formula in cell appearing below the text "reconciliation"

    Regards

    Howard

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,642
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Howard,

    Maybe this is what you want. This will copy the value in the cell below "Reconciliation" and place it in the adjacent cell. Change the ActiveCell.Offset(0, 1).Value to any range you want the value pasted.

    HTH,
    Maud


    Code:
    Public Sub GetNextCell()
        Set keyword = Cells.Columns("E:F").Find(What:="Reconciliation")
        keyword.Offset(1, 0).Select
        ActiveCell.Offset(0, 1).Value = ActiveCell.Value
    End Sub

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,424
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Hi Maudibe

    Thanks for the help, much appreciated

Posting Permissions

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