change row after finding cell

VBALordCorp · #1 (**permalink**) 07-29-04, 09:44

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

MikeTheBike · #2 (**permalink**) 07-30-04, 08:33

Hi

You were almost there

iRow=ActiveCell.Row

MTB

VBALordCorp · #3 (**permalink**) 07-30-04, 09:12

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

VBALordCorp · #4 (**permalink**) 07-30-04, 09:54

sorry guys that is MEcheckbox = 1

MikeTheBike · #5 (**permalink**) 07-30-04, 10:00

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

VBALordCorp · #6 (**permalink**) 07-30-04, 10:17

thanks a bunch. worked sweetly.

DavidCoutts · #7 (**permalink**) 08-02-04, 04:29

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

Internet Services

Web Development

Digital Marketing

Consumer Tech