Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    34

    Smile Unanswered: change row after finding cell

    ok so in my spreadsheet I and looking for part numbers in one of two colomns so I used the find function

    cells.find(what.after,lookin,lookat,searchorder,se archdirectoin,matchcase).active

    only now that I have found my cell say 'B55' I need to start changing all kinds of things in the entire row. 'A55 - M55' using my nifty userforms I made.

    Only I donno how to go from found the cell to let it know what row I am in. Is there a way to say like

    row= Active Cell row ???

    cuz then I could go F,row change to blue

    Thanks

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    You were almost there

    iRow=ActiveCell.Row


    MTB

  3. #3
    Join Date
    Jul 2004
    Posts
    34

    hmmmm more info

    I thinks I am going to need a little more information about all that.

    Ok lets say found my cell (c55) using that find thing and now it's active

    I type irow=activecell.row

    is irow a variable called out somewhere or is it already built in. I tried calling it out as a variant but it dont seem to be working right.

    anyway my next bit of code is going to be

    if MEtxtbox = 1 then
    range ("E4").select
    Activecell.formmulaR1C1 = "x"


    only i need "E4" in terms of "irow"

    so then i tried

    cells.(irow,E).select

    but that don't work either

  4. #4
    Join Date
    Jul 2004
    Posts
    34

    opps

    sorry guys that is MEcheckbox = 1

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    What you need is

    dim iRow as integer

    iRow=ActiveCell.Row
    Range("C" & iRow).Select

    or more directly
    Range("C" & ActiveCell.Row).Select


    Note you do not have to SELECT the cell or row to format it etc. just refer to it ie

    Range("C" & ActiveCell.Row).NumberFormat = "#,#0.00"

    or whatever format you want, if that helps


    MTB

  6. #6
    Join Date
    Jul 2004
    Posts
    34

    woah

    thanks a bunch. worked sweetly.

  7. #7
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Just a note on this topic just as another perspective on it

    If you set the range of the find to a variable you can just get the row of the find from that variable, the advantage of this is that if you can't find anything then you can handle this

    Here is a quick example

    Code:
    Sub Test()
        Dim rngFind As Range
        
        
        Set rngFind = Cells.Find(What:="Find This")
        
        'check to see if anything found
        If Not rngFind Is Nothing Then
            'if so run this
            Range("C" & rngFind.Row).NumberFormat = "#,#0.00"
        Else
            'or code if not found
            MsgBox "Not Found"
        End If
        
    End Sub

Posting Permissions

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