Solved

locate cell in another sheet

Posted on 2014-04-14
10
274 Views
Last Modified: 2014-04-14
Guys

Im working on a project and need a little help with a excel vb code.

I have 2 worksheets

Plan
Rolling Plan

in Rolling plan in cell B4 I have a numeric value
in Plan I have a list of numbers in column A1 going down to A100

what I would like is what ever value is in Rolling Plan B4 to look up the value and just select it in the Plan sheet.


Any help would be appreciated

Regards

Darren
0
Comment
Question by:DarrenJackson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39998685
Return the equivalent value from Plan or physically select it??

Thanks
Rob H
0
 

Author Comment

by:DarrenJackson
ID: 39998691
Just physically select it

so if cell b4 in rolling plan = 5 then I want the cell that has the number 5 in the Plan worksheet to be selected

Thanks
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39998696
So, effectively do a find on the sheet and go to that cell?

To get the syntax of the Find routine, use the VB Recorder to record while doing the find manually.

I suggest you highlight the search area (A1:A100) before instigating the search and select the option for Match Entire cell contents.

Thanks
Rob H
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:DarrenJackson
ID: 39998701
Sorry Rob I was after some vb code to do this so that I can just press a button and it will do this for me.

Sorry for not making this clear
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39998708
Try this:
Sub Find()
    FindValue = Sheets("Rolling Plan").Range("B4").Value
    Sheets("Plan").Select
    Range("A1:A100").Select
    
    Selection.Find(What:=FindValue, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    ActiveCell.Offset(0, 0).Select
End Sub

Open in new window

Thanks
Rob H
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39998712
Once you have the cell in Plan selected, what are you planning to do with it?
0
 

Author Comment

by:DarrenJackson
ID: 39998736
What I want is the value in rolling plan is an ID it will match the value in plan
The rolling plan is a form and will have data that I want to copy over from this form and place in the cells to the right of the cell in PLAN worksheet.

As the form has multiple cells filled in they will have a match on the column in the PLAN sheet.

its a register and the form feeds the register

Thanks
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39998772
So effectively you want to identify the row location so that you can extract other data from the same row; to continue from the above:

ActiveCell.Offset(0, 0).Select
R = ActiveCell.Row

Open in new window


You can then use the R as a variable when you need to identify values from the same row.

For example:
Cells(R,1).Value

Open in new window

would get value from row R and column 1 (column A)

Thanks
Rob H
0
 

Author Comment

by:DarrenJackson
ID: 39998777
Thanks Rob
0
 

Author Closing Comment

by:DarrenJackson
ID: 39998782
Thanks
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question