Cell Referencing with a Macro

[Matt]

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

 

[Derek]

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

 

[Matt]

Thanks Derek, that's great!

 

[Matt]

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

 

[Ivan F Moala]

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