• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup replacement.

W

Wulluby

Member
Hi,


Hoping someone might be able to put me on track for something to replace the VLOOKUP in the following code. It works, but not if the situation has multiple ‘var1s’ for one entry

[pre]
Code: 
Dim numRows As Integer
Dim var1 As Variant
Dim var2 As String
numRows = ActiveCell.CurrentRegion.Rows.Count

For r = 2 To numRows
var3 = Worksheets("Sheet1").Cells(r, 5).Value
var2 = Worksheets("Sheet2").Cells(r, 2).Value
var1 = Application.WorksheetFunction.VLookup(var2, Range("u2r"), 2, False)

If var1 = "One Status" Then
If var3 = "Condition w" Or var3 = "Condition x” Then
Range("A" & r & ":I" & r).ClearContents
End If

ElseIf var1 = "Another Status" Then
If var3 = " Condition y" Or var3 = " Condition z” Then
Range("A" & r & ":I" & r).ClearContents
End If
‘and so forth till next r
[/pre]

Thanks in advance.
 
Wulluby


I would use a For Each loop and change the Range U2R to be the first column of U2R

[pre]
Code: 
Dim c As Range
For Each c In Range("u2r")

then you can use c to do your comparisons

If c.Offset(0, 1).Value = "one Status" Then
If var3 = "Condition w" Or var3 = "Condition x" Then Range("A" & r & ":I" & r).ClearContents
End If

If c.Offset(0, 1).Value = "Another Status" Then
If var3 = " Condition y" Or var3 = " Condition z" Then Range("A" & r & ":I" & r).ClearContents
End If
Next c
[/pre]
 
You should be able to use the Find method. Here's my stab at implementing it:

[pre]
Code: 
Sub UseFind()

Dim MyValue As String
Dim ResultCell As Range
Dim firstAddress As String
Dim numRows As Integer
Dim var1 As Variant
Dim var2 As String
numRows = ActiveCell.CurrentRegion.Rows.Count

For r = 2 To numRows
var3 = Worksheets("Sheet1").Cells(r, 5).Value
var2 = Worksheets("Sheet2").Cells(r, 2).Value

With Range("u2r") 'Range we are searching for the MyValue
Set ResultCell = .Find(what:=MyValue)
If Not ResultCell Is Nothing Then
var1 = ResultCell.Offset(0, 1).Value

'NOTE: Might want to look into using a Select Case here. Not sure
'how many different possible statuses you are dealing with
If var1 = "One Status" Then
If var3 = "Condition w" Or var3 = "Condition x" Then
Range("A" & r & ":I" & r).ClearContents
End If

ElseIf var1 = "Another Status" Then
If var3 = " Condition y" Or var3 = " Condition z" Then
Range("A" & r & ":I" & r).ClearContents
End If
End If
End If

'Loop through any remaining cells
Do While Not ResultCell Is Nothing And ResultCell.Address <> firstAddress
Set ResultCell = .FindNext(ResultCell)
If ResultCell Is Nothing Then
Exit Do
Else
var1 = ResultCell.Offset(0, 1).Value

'NOTE: Might want to look into using a Select Case here. Not sure
'how many different possible statuses you are dealing with
If var1 = "One Status" Then
If var3 = "Condition w" Or var3 = "Condition x" Then
Range("A" & r & ":I" & r).ClearContents
End If

ElseIf var1 = "Another Status" Then
If var3 = " Condition y" Or var3 = " Condition z" Then
Range("A" & r & ":I" & r).ClearContents
End If
End If
End If
Loop
End With
Next r
End Sub
[/pre]
 
Thanks Hui,


I think I tried to be to clever in changing my data in the name of discretion and end result is I've made it more confusing than it needs to be. The condition I am trying to meet is if a 'Login' has a 'Role' then remove a row with this 'Permission' listed in it.


So my code with relevant info instead of meaningless variables would be:

[pre]
Code: 
Dim numRows As Integer
Dim role As Variant
Dim login As String
numRows = ActiveCell.CurrentRegion.Rows.Count

For r = 2 To numRows
Perm = Worksheets("Sheet1").Cells(r, 5).Value
login = Worksheets("Sheet2").Cells(r, 2).Value
role = Application.WorksheetFunction.VLookup(login, Range("u2r"), 2, False)

If role= "One Status" Then
If perm = "Condition w" Or perm = "Condition x” Then
Range("A" & r & ":I" & r).ClearContents
End If

ElseIf role = "Another Status" Then
If perm = " Condition y" Or perm = " Condition z” Then
Range("A" & r & ":I" & r).ClearContents
End If
‘and so forth till next r

Am I right in thinking then that I need to do something like the following if I want to clear a row if that role can be seen to on a login in range u2r?

Dim numRows As Integer
Dim Login As String
Dim c as Range

numRows = ActiveCell.CurrentRegion.Rows.Count
For r = 2 To numRows
Perm = Worksheets("Sheet1").Cells(r, 5).Value
Login = Worksheets("Sheet1").Cells(r, 2).Value

If c.Offset(0, 1).Value = "A Given Role Name" And c.Offset(0, 0).Value = Login Then
If Perm = "Condition y" Or Perm = "Condition z" Then
Range("A" & r & ":I" & r).ClearContents
End If
[/pre]
That should work right?
 
I think I have that offset working with:

[pre]
Code: 
Dim numRows As Integer
Dim role As Variant
Dim login As String
Dim c As Range

numRows = ActiveCell.CurrentRegion.Rows.Count
For r = 2 To numRows

perm = Worksheets("Sheet1").Cells(r, 5).Value
login = Worksheets("Sheet1").Cells(r, 2).Value

For Each c In Range("u2r")
If c.Offset(0, 1).Value = "Role1" And c.Value = login Then
If perm = "Perm1" Or perm = "Perm2" Then Range("A" & r & ":I" & r).ClearContents
End If

If c.Offset(0, 1).Value = "Role2" And c.Value = login Then
If perm = "Perm3" Or perm = "Perm4" Then Range("A" & r & ":I" & r).ClearContents
End If
Next c

Next r
[/pre]

Looks to be doing the job on some example data and it's picking up second instances of users which the vlookup was going to miss
 
You must log in or register to reply here.
Back
Top