Solved

locate cell in another sheet

Posted on 2014-04-14
10
264 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 31

Expert Comment

by:Rob Henson
Comment Utility
Return the equivalent value from Plan or physically select it??

Thanks
Rob H
0
 

Author Comment

by:DarrenJackson
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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

by:DarrenJackson
Comment Utility
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 31

Accepted Solution

by:
Rob Henson earned 500 total points
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 31

Expert Comment

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

Author Comment

by:DarrenJackson
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
Thanks Rob
0
 

Author Closing Comment

by:DarrenJackson
Comment Utility
Thanks
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now