Range problem
 

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

Range problem
 

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

-----Original Message-----
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


.

Range problem
 

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


--

HTH

Bob Phillips

"Reno" wrote in message
...
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

Range problem
 

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

"James S" wrote in message
...
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

-----Original Message-----
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


.

Range problem
 

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


"Bob Phillips" wrote in message
...
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


--

HTH

Bob Phillips

"Reno" wrote in message
...
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

Range problem
 

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.

--

HTH

Bob Phillips

"Reno" wrote in message
...
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


"Bob Phillips" wrote in message
...
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


--

HTH

Bob Phillips

"Reno" wrote in message
...
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

Range problem
 

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


"Bob Phillips" wrote in message
...
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.

--

HTH

Bob Phillips

"Reno" wrote in message
...
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


"Bob Phillips" wrote in message
...
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


--

HTH

Bob Phillips

"Reno" wrote in message
...
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

Range problem
 

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)


--

HTH

Bob Phillips

"Reno" wrote in message
...
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


"Bob Phillips" wrote in message
...
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.

--

HTH

Bob Phillips

"Reno" wrote in message
...
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


"Bob Phillips" wrote in message
...
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


--

HTH

Bob Phillips

"Reno" wrote in message
...
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