VBA Using Wildcards

[ccowman]

I have the following code...I'd like to enhance it to include the use of Wildcards, but am having some troubles - any help would be appreciated!


Option Explicit


Sub final_data()

Dim lrow As Long, i As Long, j As Long, lastrow As Long, x As Long, counter As Long


With Worksheets("RawData")

lrow = .Range("A" & .Rows.Count).End(xlUp).Row

For i = 2 To lrow

For j = 10 To 14

If .Cells(i, j).Value = "Not Sourced" Then

.Range("A" & i & ":I" & i).Copy Worksheets("FinalData").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

lastrow = Worksheets("FinalData").Range("A" & Rows.Count).End(xlUp).Row

Worksheets("FinalData").Cells(lastrow, j).Value = "Not Sourced"

End If

Next j

Next i

Worksheets("FinalData").Columns("J:N").HorizontalAlignment = xlCenter

End With


End Sub


I'd like to change the "Not Sourced" to be "* - Not Sourced" as I have three different versions "D - Not Sourced", "P - Not Sourced", and "W - Not Sourced". I have tried using Like, but in the second instance of "Not Sourced", it throws a syntax error.

 

[Debraj]

Hi ccowman,


I Guess the problem is with "-"


Can you please try with..

If .Cells(i, j).Value Like "* - Not Sourced" Then


For better result you can use..

[code]If .Cells(i, j).Value Like "[DPW] - Not Sourced" Then

and for more better you can use

Option Compare Text[/code] in the top of the Macro Page..


Regards,

Deb

 

[ccowman]

Thanks for the tips, Deb, but I am getting a "Compile Error: Syntax Error" on the second instance of Like...Any thoughts why?

 

[Debraj]

Hi ccowman,


Requesting to upload sample file.. as everything looks fine to me..


Regards,

Deb

 

[ccowman]

Thanks, Deb. I have uploaded a version here: https://www.dropbox.com/s/utry9gs7ahds2ib/ReqTestMatrix.xlsm

 

[Debraj]

Hi ccowman,


Please find my comments in the below Area.. Copy the Code, and update..

[pre]

Option Explicit

Sub final_data()
Dim lrow As Long, i As Long, j As Long, lastrow As Long, x As Long, counter As Long

With Worksheets("RawData")
lrow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 2 To lrow
For j = 10 To 14
If .Cells(i, j).Value Like "* - Not Sourced" Then
.Range("A" & i & ":I" & i).Copy Worksheets("FinalData").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
lastrow = Worksheets("FinalData").Range("A" & Rows.Count).End(xlUp).Row

'=====
'Please dont change this are to Like "* - Not Sourced"
'as LIKE is used for testing only not to apply in the other area..
Worksheets("FinalData").Cells(lastrow, j).Value = .Cells(i, j)
'====

End If
Next j
Next i
Worksheets("FinalData").Columns("J:N").HorizontalAlignment = xlCenter
End With

End Sub

[/pre]

Regards,

Deb

 

[ccowman]

Works perfectly, Deb! Thanks!