Claire Jackson
Board Regular
- Joined
- Jun 30, 2020
- Messages
- 74
- Office Version
- 2016
- Platform
- Windows
Hi,
Im new to all this and have been googling forever on this
I basically have two workbooks, one with data in a another where i want to lookup cell $E$1 and return a value from column 61 (BI) in the other workbook.
The code i have is this:
Option Explicit
Sub Get_CM_Name()
Dim Range_Lookup As Range
Dim ContMan_Name As String
Dim found_value As Variant
ContMan_Name = "$E$1"
'tables.xls must be open
Workbooks.Open ("\\PDDC01\ClaimManagement\0-CLAIRE\Daily Report CRM.xlsx")
Set Range_Lookup _
= Workbooks("Daily Report CRM.xlsx").Worksheets("table").Range("A1:DE1000")
found_value = Application.VLookup(ContMan_Name, Range_Lookup, 61, False)
If IsError(found_value) Then
'n/a would have been returned
Else
'no error
End If
End Sub
What am I doing wrong as it opens the data file but then just sits in cell B8 of the datafile without returning anything to my other workbook. I'm not sure how to point the data which is being looked up back to Cell B4 in my current workbook
Please help?
Im new to all this and have been googling forever on this
I basically have two workbooks, one with data in a another where i want to lookup cell $E$1 and return a value from column 61 (BI) in the other workbook.
The code i have is this:
Option Explicit
Sub Get_CM_Name()
Dim Range_Lookup As Range
Dim ContMan_Name As String
Dim found_value As Variant
ContMan_Name = "$E$1"
'tables.xls must be open
Workbooks.Open ("\\PDDC01\ClaimManagement\0-CLAIRE\Daily Report CRM.xlsx")
Set Range_Lookup _
= Workbooks("Daily Report CRM.xlsx").Worksheets("table").Range("A1:DE1000")
found_value = Application.VLookup(ContMan_Name, Range_Lookup, 61, False)
If IsError(found_value) Then
'n/a would have been returned
Else
'no error
End If
End Sub
What am I doing wrong as it opens the data file but then just sits in cell B8 of the datafile without returning anything to my other workbook. I'm not sure how to point the data which is being looked up back to Cell B4 in my current workbook
Please help?