Cell Referencing with a Macro
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Cell Referencing with a Macro

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I'm trying to reference a particular row in a spreadsheet through a macro. My problem is the row moves moves up and down depending on the number of media codes above it. Is there a way to reference the row in a macro by searching for a cell with certain text within in it and then selecting that row? Hope this is clear

    thanks

    Matt

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In Row A1 type this formula

    =Row(A15)

    When Row15 is the current position of the row you wish to track. Now if any rows are inserted or deleted between Row 1 and "your row" A1 will display the current row number of "your row".

    Now get your macro to refer to the row displayed in A1, eg
    Rows(Range("A1").Value).EntireRow.Select

    Hope this helps
    Regards
    Derek


  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Derek, that's great!

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Derek

    Have come across a problem. The file from which I need to reference a row is actually generated from an AS400, thus although the file has the same name each time the macro picks it up, the formula cannot be saved in the file. Is there another way of selecting the row, e.g. by searching for text in a particular cell and then selecting the entire row?

    thanks

    Matt

  5. #5
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-03-01 07:22, Matt wrote:
    Derek

    Have come across a problem. The file from which I need to reference a row is actually generated from an AS400, thus although the file has the same name each time the macro picks it up, the formula cannot be saved in the file. Is there another way of selecting the row, e.g. by searching for text in a particular cell and then selecting the entire row?

    thanks

    Matt
    Hi Matt
    Assuming data text to search is in Column A
    then this may help

    Sub GetAddress()
    Dim Addr As Range

    Set Addr = Columns("A:A").Find(What:="Test", After:=[A1], LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

    Addr.EntireRow.Select

    End Sub


    Post if you need more

    Ivan

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com