?
Solved

locate cell in another sheet

Posted on 2014-04-14
10
Medium Priority
?
284 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
  • 5
  • 5
10 Comments
 
LVL 35

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 35

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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 

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 35

Accepted Solution

by:
Rob Henson earned 2000 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 35

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 35

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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

588 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