Solved

locate cell in another sheet

Posted on 2014-04-14
10
269 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 32

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 32

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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 32

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 32

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 32

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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…

810 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