Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# locate cell in another sheet

Posted on 2014-04-14
Medium Priority
278 Views
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
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
• 5
• 5

LVL 33

Expert Comment

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

Thanks
Rob H
0

Author Comment

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

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

Author Comment

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

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
``````
Thanks
Rob H
0

LVL 33

Expert Comment

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

Author Comment

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

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
``````

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
``````
would get value from row R and column 1 (column A)

Thanks
Rob H
0

Author Comment

ID: 39998777
Thanks Rob
0

Author Closing Comment

ID: 39998782
Thanks
0

## Featured Post

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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.
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â€¦
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month5 days, 13 hours left to enroll