Vlookup

[ADAMC]

Hi all,

i found a macro online which is really useful for me but unfortunately i am useless at Excel. can anyone help me modify it slightly?
In the code below an input box pops up for me to choose the columnn but can this be changed so it always just looks at colum A:A? i would still like to be able to select the workbook and everything else. thanks for any help!! i have put in bold the line i think this is? can this be changed so it just always chooses Column A instead of having to pick it every time?

Private Function GetColumn(Num As Integer) As String
If Num <= 26 Then
GetColumn = Chr(Num + 64)
Else
GetColumn = Chr((Num - 1) \ 26 + 64) & Chr((Num - 1) Mod 26 + 65)
End If
End Function
Sub FindValue()
Application.ScreenUpdating = False
Dim xAddress As String
Dim xString As String
Dim xFileName As Variant
Dim xUserRange As Range
Dim xRg As Range
Dim xFCell As Range
Dim xSourceSh As Worksheet
Dim xSourceWb As Workbook
On Error Resume Next
xAddress = Application.ActiveWindow.RangeSelection.Address
Set xUserRange = Application.InputBox("Lookup values :", "Comments Lookup", xAddress, Type:=8)
If Err <> 0 Then Exit Sub
Set xUserRange = Application.Intersect(xUserRange, Application.ActiveSheet.UsedRange)
xFileName = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx", 1, "Select a Workbook")
If xFileName = False Then Exit Sub
Application.ScreenUpdating = False
Set xSourceWb = Workbooks.Open(xFileName)
Set xSourceSh = xSourceWb.Worksheets.Item(1)
xString = "='" & xSourceWb.Path & Application.PathSeparator & _
"[" & xSourceWb.Name & "]" & xSourceSh.Name & "'!$"
For Each xRg In xUserRange
Set xFCell = xSourceSh.Cells.Find(xRg.Value, , xlValues, xlWhole, , , False)
If Not (xFCell Is Nothing) Then
xRg.Offset(0, 1).Formula = xString & GetColumn(xFCell.Column + 1) & "$" & xFCell.Row
End If
Next
xSourceWb.Close False
Application.ScreenUpdating = True
MsgBox "Comments from the previous day have been updated", vbOKOnly, "Success!"
End Sub

 

[ADAMC]

Can anyone help with the below at all please. Amazing if someone could!

Thank you.

 

[James006]

Hi,

You could test following

Sub FindValueColumnA()
Application.ScreenUpdating = False
Dim xAddress As String
Dim xString As String
Dim xFileName As Variant
Dim xUserRange As Range
Dim xRg As Range
Dim xFCell As Range
Dim xSourceSh As Worksheet
Dim xSourceWb As Workbook


On Error Resume Next
xAddress = Application.ActiveWindow.RangeSelection.Address
[B]Set xUserRange = Range("A:A")[/B]
If Err <> 0 Then Exit Sub
Set xUserRange = Application.Intersect(xUserRange, Application.ActiveSheet.UsedRange)
xFileName = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx", 1, "Select a Workbook")
If xFileName = False Then Exit Sub
Application.ScreenUpdating = False
Set xSourceWb = Workbooks.Open(xFileName)
Set xSourceSh = xSourceWb.Worksheets.Item(1)
xString = "='" & xSourceWb.Path & Application.PathSeparator & _
"[" & xSourceWb.Name & "]" & xSourceSh.Name & "'!$"
  For Each xRg In xUserRange
    Set xFCell = xSourceSh.Cells.Find(xRg.Value, , xlValues, xlWhole, , , False)
    If Not (xFCell Is Nothing) Then
      xRg.Offset(0, 1).Formula = xString & GetColumn(xFCell.Column + 1) & "$" & xFCell.Row
    End If
  Next
xSourceWb.Close False
Application.ScreenUpdating = True
MsgBox "Comments from the previous day have been updated", vbOKOnly, "Success!"
End Sub

Hope this will help

 

[ADAMC]

Thanks.
Not at my workbook right now but I will try that in the morning. I did try something similar but maybe I didn’t code it right.

Thanks again