Hi Everyone! I'm new here and new to VBA. I'm trying to automate few things but I've spent lots of hours already looking on the internet, videos, forums, etc... and haven't been able to achieve anything, no success.
Here is what I'm trying to achieve, let see if I know how to explain it clearly.
- I have sheet1 with a list of descriptions
- I have sheet2 with a list of references
- The references in sheet2 can be found in the sheet1 list of descriptions but it is not an exact match since the descriptions have a lot of gibberish text in front and in the back of the reference.
- I need to do a partial match check from the reference in sheet2 with sheet1
- If the reference from sheet2 exists in any of the sheet1 descriptions (if it matches), then I would like to return columns b, c and d from sheet2 to columns in sheet1 f, h and i.
- Note: There may be times in sheet2 that one of the columns may not have data since it will get provided later. So if no data I would like to leave a blank field in sheet1.
I've been trying to test only to return c from sheet2 to h in sheet1 to start with, but not success.
These are my 2 sheets examples:
Sheet1:
Sheet2:
And here is a piece of code that I've been trying, but not results appear anywhere. If the code is crap, please change it as you prefer. I'm already too lost... maybe it is easier coding it differently.
I hope one of you brilliant and intelligent members can help me out. I'm sure it is much easier that I've been trying to code it for. But at this point, after hours of researching and testing, I come here for help.
Thank you very much in advance. I will appreciate any help.
Bests,
ZioS
Here is what I'm trying to achieve, let see if I know how to explain it clearly.
- I have sheet1 with a list of descriptions
- I have sheet2 with a list of references
- The references in sheet2 can be found in the sheet1 list of descriptions but it is not an exact match since the descriptions have a lot of gibberish text in front and in the back of the reference.
- I need to do a partial match check from the reference in sheet2 with sheet1
- If the reference from sheet2 exists in any of the sheet1 descriptions (if it matches), then I would like to return columns b, c and d from sheet2 to columns in sheet1 f, h and i.
- Note: There may be times in sheet2 that one of the columns may not have data since it will get provided later. So if no data I would like to leave a blank field in sheet1.
I've been trying to test only to return c from sheet2 to h in sheet1 to start with, but not success.
These are my 2 sheets examples:
Sheet1:
Sheet2:
And here is a piece of code that I've been trying, but not results appear anywhere. If the code is crap, please change it as you prefer. I'm already too lost... maybe it is easier coding it differently.
VBA Code:
Sub Find_ISL_REF()
Dim rng2 As Range, c2 As Range, cfind As Range
Dim x, y
With Worksheets("Sheet1")
Set rng2 = .Range(.Range("C2"), .Range("C2").End(xlDown))
For Each c2 In rng2
x = c2.Value
With Worksheets("Sheet2").Columns("A:A")
On Error Resume Next
Set cfind = .Cells.Find(what:=x, lookat:=xlPart, LookIn:=xlValues)
If (Not (cfind Is Nothing)) Then
y = cfind.Offset(0, 5).Value
c2.Offset(0, 5) = y
End If
End With
Next c2
End With
End SubI hope one of you brilliant and intelligent members can help me out. I'm sure it is much easier that I've been trying to code it for. But at this point, after hours of researching and testing, I come here for help.
Thank you very much in advance. I will appreciate any help.
Bests,
ZioS
