Solved

locate cell in another sheet

Posted on 2014-04-14
10
271 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 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

808 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