Hi All,
I am using the below code to go down a column of data and then perform a search in another tab, retrieving the matching data.
My problem occurs when I try to paste that piece of data in the same row as the search item but one column along. i.e. this piece of code >
I have tried a few options and all seem to come up with an error. The only paste option that works is to select a specific range like this
. This does work but I need the paste point to change.
The current error I am getting from the code below is "Application-defined or object-defined error"
Any help would be greatly appreciated!
I am using the below code to go down a column of data and then perform a search in another tab, retrieving the matching data.
My problem occurs when I try to paste that piece of data in the same row as the search item but one column along. i.e. this piece of code >
Code:
[B].Range(ActiveCell) = HospFound[/B]
I have tried a few options and all seem to come up with an error. The only paste option that works is to select a specific range like this
Code:
[B].Range("A2") = hospfound[/B]
The current error I am getting from the code below is "Application-defined or object-defined error"
Any help would be greatly appreciated!
Code:
[B]Sub NameMatch()[/B]
[B]Dim HospFound As Range, hospital As String, c As Range, n As Range[/B]
[B]For Each n In Range("AH2:AH2000")[/B]
[B]n.Select[/B]
[B]ActiveCell.Offset(0, 1).Select[/B]
[B] Set c = Sheets("Proced Volumes").Range("L4:L500")[/B]
[B] Set HospFound = c.Find(What:=n, After:=c.Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)[/B]
[B] If Not HospFound Is Nothing Then[/B]
[B] With Worksheets("NHS Stated Volumes")[/B]
[B] .Range(ActiveCell) = HospFound[/B]
[B] End With[/B]
[B] End If[/B]
[B]Next[/B]
[B]End Sub[/B]