View Full Version : Solved: Using Union to combine two ranges
kevvukeka
05-28-2013, 03:49 AM
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...
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
kevvukeka
05-28-2013, 04:47 AM
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...
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.
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
.
kevvukeka
05-28-2013, 09:12 PM
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
05-28-2013, 10:23 PM
Hi Sam,
Its working now. I changed cel to cel2 in the .findnext option
Thanks so much....
or
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
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
05-30-2013, 12:14 AM
Thanks Sam and Thanks snb for your valuable help....For a beginner like me these value a lot....
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.