Solved: Using Union to combine two ranges

[kevvukeka]

Hi All,

I need some help with my below code. I have master sheet in which i am perfoming an if function. But as it huge file, i tried to limit my find operation to specific range. I need to just use Col Ak and COl B for find operation. I tried to use Union function but it throws me an error at "Sheets("Master").combrange.Select" line. Below is the code. Kindly let me know what changes needs to be done.

Thnaks for your help...

[VBA]Sub thing()
Dim cell As Range, cell2 As Range
Dim lrow As Long
Dim policynumber As Range
Dim claimnumber As Range
Dim combrange As Range
Dim i, j As Integer



Sheets("Master").Range("AK2").Select
i = Cells(Rows.Count, 1).End(xlUp).Row

Set policynumber = Range("AK2:AK" & i)

Sheets("Master").Range("B2").Select
j = Cells(Rows.Count, 1).End(xlUp).Row
Set claimnumber = Range("B2:B" & j)
Set combrange = Union(Range("B2:B" & j), Range("AK2:AK" & i))

Application.ScreenUpdating = False
For Each cell In Sheets("Policy List").Range("A2:A260")
Sheets("Master").combrange.Select
With Selection
Set cell2 = .Find(cell.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not cell2 Is Nothing Then
Sheets("Wrong List").Select
lrow = Cells(Rows.Count, 1).End(xlUp).Row + 1
ActiveSheet.Cells(lrow, 1) = cell2.Offset(0, -35).Value
ActiveSheet.Cells(lrow, 2) = cell.Value
End If
End With
Next cell
End Sub[/VBA]

[kevvukeka]

Also the above code is only giving the result of first instance only. For eg. I have a policy name:ABC123, for which there 10 member id e.g, praveen1,praveen2,praveen3....praveen10.

Now the above is only searching the first instance of abc123 and returning its first memberid and skipping to next policy. whereas I need all the 10 member ids for that policy... How should i edit the above code..

I am not an expert in VBA, i learng it through google, so i am not able to think of many alternatives...


Kindly help.

Thanks a lot...

[SamT]

I repaired some parts, simplified the code, Used a different code logical flow, and added the loop to check for multiple instances of policy members. Note that the code comments are based on the names of the variables in the code and not your second post.

[VBA]Option Explicit

Sub SamT()
Dim cel As Range, cel2 As Range 'Never use a keyword as a variable name
Dim policynumber As Range
Dim claimnumber As Range
Dim FirstFound As String
Dim i As Variant 'Used to append row number characters to 3 Ranges and row#s in another



With Sheets("Master")
i = CStr(.Cells(Rows.Count, "AK").End(xlUp).Row)
Set policynumber = .Range("AK2:AK" & i) 'Note the dot. It makes policynumber specific to Master
'Alternate method:
'Set policynumber = .Range("AK2:AK" & CStr(.Cells(Rows.Count, "AK").End(xlUp).Row))

i = CStr(.Cells(Rows.Count, "B").End(xlUp).Row)
Set claimnumber = .Range("B2:B" & i)
End With

Application.ScreenUpdating = False

'The following code looks at each Policy number on the Policy List sheet
'and searches for that number in the policynumber Range on the Master Sheet.
'
'If that Policy is found, it appends the corresponding Claim number from the
'claimnumber Range of the Master sheet, and the Policy Number, to the end of
'the list on the Wrong List sheet. Then it looks for another instance of that Policy

i = CStr(Sheets("Policy List").Cells(Rows.Count, 1).End(xlUp).Row)
For Each cel In Sheets("Policy List").Range("A2:A" & i)
With policynumber
Set cel2 = policynumber.Find(cel.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not cel2 Is Nothing Then
FirstFound = cel2.Address 'Set up test to check if Find is back at first cell found

Do
With Sheets("Wrong List")
i = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Cells(i, 1) = claimnumber.Cells(cel2.Row).Value 'Row#s in claimnumber and policynumber are equal
.Cells(i, 2) = cel.Value 'Note dots. .Cell belongs to Wrong List, but cel is set to a range in Policy List
End With
Set cel2 = .FindNext(cel)
Loop While Not cel2 Is Nothing And cel2.Address <> FirstFound

End If
End With
Next cel

Application.ScreenUpdating = True
End Sub
[/VBA].

[kevvukeka]

Hi Sam,

Thanks for the help. I used this code but it throws an error at finding the next instance..

i.e Set cel2 = .FindNext(cel)

the first policy details, first instance are filled in the first row of the wrong list sheet. but its not going any further...

[kevvukeka]

Hi Sam,

Its working now. I changed cel to cel2 in the .findnext option

Thanks so much....

[SamT]

[snb]

or
[VBA]
Sub M_snb()
With Sheets("Master")
sn=.columns(2).specialcells(2)
sp=.columns(37).specialcells(2)
End With

c00=join(application.transpose(Sheets("Policy List").columns(1).specialcells(2)),"|")

for j=2 to ubound(sp)
replace("|"& c00 & "|","|" & sp(j) & "|","|" & sp(j) & "_" & sn(j) &"|")
next

Sheets("Policy List").columns(1).specialcells(2)=application.transpose(split(c00,"|"))
Sheets("Policy List").columns(1).texttocolumns ,,,,,,,false,false,false,false,True,"_"
End Sub
[/VBA]

[SamT]

Kevvukeka,

Analyzing snb's code is always a treat.

Put his code in any VBA code page and place the cursor in the last dotted keyword, (ex: .SpecialCells,) in a line of code and press F1 for help on that Keyword.

Proceed with Keywords from Right to left.

Pay close attention to Parenthesis and commas. (ex:the first [,"|")] belongs to "Join".)

Open NotePad, paste a code line in it and format the line with CR's and tabs.

[kevvukeka]

Thanks Sam and Thanks snb for your valuable help....For a beginner like me these value a lot....