Cell Referencing with a Macro

Matt

Board Regular
Joined
Feb 16, 2002
Messages
212
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top