• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Open workbook and jump to specific cell

W

Wim Geuens

Member
Helli,
I am new to vba so if domeone can help me out it would be nice.

I am trying to write a code that will do the following

- press a button in workbook 1
- pop up will appear where you need to select an cell in workbook 1
- this will open a second workbook (workbook 2) in read only modus
- excel will automatically jump to the cell with the value that was given step 2

This way it is easy to find a specific cell in 1 big excel file.

If you need more info,please let me know

Thanks
W
 
Hi Wim ,

You want to specify two inputs viz. the name of the second workbook which is to be opened , and the cell to which the cursor should go in that file after it is opened.

Is this correct ?

If so , how are you going to specify the first input viz. the name of the second workbook ?

Narayan
 
Hi,

Workbook 2 is a fixed file,so this can be integrated into the vba code
The selected cell in workbook 1 is indeed an input

Wim
 
Workbook 1 is a summary file and contains numbers

In workbook 2 you can find the same numbers but this is a huge file

I want to select a number in workbook 1 that will automatically open workbook 2 and jump to the first cell with this number in a specific sheet
This because workbook 2 contains a lot of cells with the same valie as workbook 1

Hope this clears it out

Thanks
 
If the Summary sheet contain Reference only..
Something like..
='C:\Users\Debraj\Documents\[Workbook2.xlsx]Sheet1'!$A$1

Did you tried with key combination..
Ctrl + [

Or its a Value..
 
hi debraj,

the cell is a number, but the summary file contains a lot of numbers so if i need to implement alle the references by hand it will be a lot of work. Thats why i wanted to do it with vba :)
 
Hi Wim ,

An Inputbox is an object in VBA which allows you to input data which can then be used by the VBA procedure ; there are different types of Inputbox objects to be used for different purposes.

The code you have given the link for , will allow you to enter a value in the Inputbox ; the code will then look for this value in the sheet mentioned in the code and if found will place the cursor in that cell.

Code: 
Sub Find_First()
    Dim FindString As String
    Dim Rng As Range
    FindString = InputBox("Enter a Search value")
    If Trim(FindString) <> "" Then
        With Sheets("Sheet1").Range("A:A")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If
End Sub
If the workbook in which you wish to search is already open , you can replace the following statement :

With Sheets("Sheet1").Range("A:A")

with the following :

With Workbooks(" ").Worksheets(" ").Range(" ")

where you need to fill in all the blanks.

If the workbook is not already opened , you will need to open it with the VBA statement :

Workbooks.Open FileName:=" ", ReadOnly:=True

where you need to fill in all the blanks.

Narayan
 
Hi narayan,

it works!

But is there a possibility that if I don't put any value in de input box that it just opens the workbook
right now the vba stops when I don't enter a value...
 
Hi Wim ,

If you can post the complete code that you are using , I can post the exact changes that need to be made.

Narayan
 
You must log in or register to reply here.
Back
Top