How to ignore a column in a range search VBA code?

[Indominus]

Hi. I have a code that searches in a range for a value and returns the matching value from a matching column. I want to know how I can ignore searching in one of the 3 columns in the range as it can have duplicates from the column I need and it returns unnecessary data. In this example I need to ignore column C. Column B and E are the two I really need. I have a value in cell K3. It tries to find it in column E and returns the value in column B. The part of the code for the range is this. Any help is appreciated. Thanks!

Set rngSrch = srcWs.Range(“B2:E” & LastRow1)

 

[Michael M]

Try using

Set rngSrch = srcWs.Range("B2:B" & LastRow1 & ",E2:E" & LastRow1)

 

[DanteAmor]

In this example I need to ignore column C. Column B and E are the two I really need

Example:

Sub searchcolumns()
  Dim srcWs As Worksheet
  Dim f As Range
  Set srcWs = ActiveSheet
  Set f = srcWs.Range("B:B, E:E").Find(srcWs.Range("K3").Value, , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    'found
  Else
    'not found
  End If
End Sub

 

[Indominus]

H

Try using

Set rngSrch = srcWs.Range("B2:B" & LastRow1 & ",E2:E" & LastRow1)

Hi. I am getting an error with this one. “Method ‘Range’ of object ‘_Worksheet’ failed “

 

[Indominus]

Example:

Sub searchcolumns()
  Dim srcWs As Worksheet
  Dim f As Range
  Set srcWs = ActiveSheet
  Set f = srcWs.Range("B:B, E:E").Find(srcWs.Range("K3").Value, , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    'found
  Else
    'not found
  End If
End Sub

Hi. So how could I apply this to my full code? With me using LastRow1 as a variable would it be different? Here is my full code so it’s clearer. Sorry.

Dim LastRow1 As Long, rng As Range, sAddr As String, cDest As Range, _

Val As Range, lCol As Long, desWS As Worksheet, srcWS As Worksheet

Dim rngSrch As Range



Set srcWS = Sheets("Raw Data")

Set desWS = Sheets("Dive")



LastRow1 = srcWS.Cells.Find("*", SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious).Row



Set rngSrch = srcWS.Range("B2:E" & LastRow1)



For Each rng In desWS.Range("K3").Cells



Set Val = rngSrch.Find(rng.Value, LookIn:=xlValues, lookat:=xlWhole)

If Not Val Is Nothing Then

'first empty cell below the value being searched

Set cDest = desWS.Cells(Rows.Count, rng.Column).End(xlUp).Offset(1, 0)

sAddr = Val.Address

Do

cDest.Value = Val.EntireRow.Cells(2).Value

Set cDest = cDest.Offset(1, 0) 'next row down

Set Val = rngSrch.FindNext(Val)

Loop While Val.Address <> sAddr

sAddr = ""

End If



Next rng

 

[DanteAmor]

So how could I apply this to my full code? With me using LastRow1

@Michael M's answer is correct, check again that you correctly copy Michael's solution

 

[Indominus]

@Michael M's answer is correct, check again that you correctly copy Michael's solution

Yup! I swear I copied and pasted it straight away but it works perfectly. Thank you both! I appreciate the assistance so much

 

[Indominus]

H

Hi. I am getting an error with this one. “Method ‘Range’ of object ‘_Worksheet’ failed “

Nevermind. Works perfectly!!! There was a paste issue. Thank you so much Michael I really appreciate it!

 

[Michael M]

Glad WE could help...

 

[Indominus]

Glad WE could help...

You both for sure did. I do have one question regarding the syntax of the code due to me really trying to become proficient in VBA. What is the reason for doing the quotation marks before the comma and not have it be “E2:E” like a range normally would be? I’m curious on how you know to do that going forward. Thank you!