open another workbook and select cell

[xzaqus]

Hello-

I have a workbook with values in column A and the corresponding reference workbook, sheet in column B and C respectively (Col B has workbook name and C has sheet within the workbook).

I need a code such that if a value is missing in Col A, then it should bring up an inputbox, open up the reference workbook, such that the user can select the value from the reference sheet.

This all should be done within the if loop when Col A values are checked.

Help!

 

[jproffer]

Sub test()
Dim i As Long
Dim lastrow As Long

lastrow = Range("A1").End(xlDown).Row + 1

For i = 1 To lastrow
    If Range("A" & i).Value = "" Then
        Workbooks(Range("B" & i).Value).Open
        Workbooks(Range("B" & i)).Worksheets(Range("C" & i)).Activate
    End If
Next i
        
End Sub

This might give you a start. I wasn't sure the purpose for the inputbox if you were opening a workbook to find the value that should be in Col A, but I imagine it could be incorporated fairly easily.

 

[xzaqus]

Thank you for the help in providing a start. However, I need an InputBox so the user can click on the cell in the newly opened workbook and the value from the cell gets filled in the empty cell in the original workbook.

I got the inputbox to show up, but the opened workbook is greyed out. The workbook actually opens up only after the inputbox is canceled. It looks like that excel is not opening another workbook until the sub is active in the first workbook. Any suggestions!

Sub test()
Dim i As Long
Dim lastrow As Long

lastrow = Range("A1").End(xlDown).Row + 1

For i = 1 To lastrow
    If Range("A" & i).Value = "" Then
        Workbooks(Range("B" & i).Value).Open
        Workbooks(Range("B" & i)).Worksheets(Range("C" & i)).Activate
    End If
Next i
        
End Sub

This might give you a start. I wasn't sure the purpose for the inputbox if you were opening a workbook to find the value that should be in Col A, but I imagine it could be incorporated fairly easily.

 

[jproffer]

That's not really what an inputbox does. Post the code you have so far. Is the "cell in the newly opened workbook" always the same cell? If so, we can just say

Range("A" & i).Value = WB(Range....).Worksheets(Range....).Range(whatever)

Or at least, is it consistently different? IOW, could a loop with a variable and a certain step rate be used to grab the value of this certain cell in the newly opened workbook?

 

[xzaqus]

It changes every time. That is why I need to pick up through inputbox
The code I have is

If ActiveSheet.Range(checkCell).Count > 1 Then
Workbooks.Open fileName:=rFilePath

Set checkCell = Application.InputBox(" Select the cell that has the value", "Select cell", Type:=8)


MsgBox checkCell.Address


End if


rFilePath has the path to the file to open. When I run this, an inputbox opens up on top of a greyed out blank new workbook. So, it is of no use. The moment I close the inputbox, the file in rFilePath opens up.

That's not really what an inputbox does. Post the code you have so far. Is the "cell in the newly opened workbook" always the same cell? If so, we can just say

Range("A" & i).Value = WB(Range....).Worksheets(Range....).Range(whatever)

Or at least, is it consistently different? IOW, could a loop with a variable and a certain step rate be used to grab the value of this certain cell in the newly opened workbook?

 

[xzaqus]

Correction: The code I have is

If IsEmpty(ActiveSheet.Range(referenceCellAddress)) Then
Workbooks.Open fileName:=rFilePath

Set checkCell = Application.InputBox(" Select the cell that has the value", "Select cell", Type:=8)


MsgBox checkCell.Address


End if


rFilePath has the path to the file to open. When I run this, an inputbox opens up on top of a greyed out blank new workbook. So, it is of no use. The moment I close the inputbox, the file in rFilePath opens up.[/QUOTE]