Range problem

  • Thread starter Thread starter Reno
  • Start date Start date
R

Reno

Folks:

Been staring at this code for days, and am frankly lost. Not really an Excel
programmer but cobbled this together from samples and research. All I wish
to do is to take a value from one sheet and position to that same value in
another sheet, and then set the row in the sheet to current row. Here is the
code....

Sub current_Cell()

MsgBox ActiveCell.Value

For Each RosRng In Sheets("sheet2").UsedRange.Rows
If RosRng.Range("D1").Value = ActiveCell.Value Then
MsgBox "gotit"
End If
Next

End Sub

I have 4 rows in the other sheet and it does loop through 4 times. However,
there seems to be NOTHING in RosRng.Range("D1").Value. It will not light up
in the debugger either which leads me to believe that something ain't quite
right.

Can someone please shed some light on what I may be doing wrong.

Also, can someone tell me how to set current row. If I find the above value
in say, row 7, how can I postion to that row or even column.

Thanks a bunch!!

Reno
 
Hi Reno,

A couple questions for you. Are you trying to move one
cell value or a range of cell values? (e.g., move value of
cell A1 from Sheet1 to cell A1 Sheet2, then select A1 on
Sheet2 OR move cell values A1:A4 Sheet1 to A1:A4 Sheet2.)

Regards,
James S
 
Reno,

Try this

Sub current_Cel()
Dim cellValue
Dim oCell As Range

cellValue = ActiveCell.Value

With Worksheets("Sheet2")
.Activate
Set oCell = .Cells.Find(cellValue)
If Not oCell Is Nothing Then
MsgBox "Got it"
oCell.Select
End If
End With

End Sub
 
Hi James,

Nope, I don't want to move anything. They (users) have basically two
worksheets of vendor information. They are complaining that when they have
to change the info in sheet2 (when they are in sheet1), that they have to do
a search for the vendor number each time and it takes long and it is
inconvenient yadda, yadda, yadda. So I said, how hard could it be to just
grab the current row they are on, get the "vendor id" and look it up on the
other sheet, then bring up the sheet with the row highlighted?

Well, this is day three!! LOL!!

From all of the code I have been looking at, this at least should GET ME to
that row on the other sheet, but alas, it doesn't.

Any help would be greatly appreciated.

Thanks,

Reno
 
Bob! THANKS!!! It works like a charm!!

May I ask you something though. These spreadsheets are huge! Will that
impact the search time (searching by cell)? Do you think I could limit it
somehow to the row that the ID is in on sheet 2?

Know what I mean?

Once again, THANK YOU!!!

Reno
 
Reno,

You are using the VBA Find method, so that's a big plus, it's not a cell by
cell search. To limit it though, use

Set oCell = .Rows(4).Find(cellValue)
instead of
Set oCell = .Cells.Find(cellValue)

and it wil be limited to the 4th row.
 
Shoot, Bob, I meant to say column!!

Maybe this?

Set oCell = .Cols(4).Find(cellValue) ???

But anyway, I'll give it a shot as is and see what happens...

Once again, thanks!!

Reno
 
Isn't that typical. I first created it with column, then saw that you had
said row.

You want

Set oCell = .Columns(4).Find(cellValue)
 
Back
Top