UPENDER Mahato
New Member
- Joined
- Jun 1, 2015
- Messages
- 3
Hi All,
I am looking for a code which can help me to find a word based on provided range input in another range and then change the value of another equivalent range. I have write below query but it's not working fine. Please help to correct the code.
For Ex. Reference Range value = Adobe and Fiand Range Values are redbooks.pdf - Adobe Reader, 02-COBOL-Dataname-Editing Charaters.pdf - Adobe Reader, TSO_and_ISPF.pdf - Adobe Reader, Adobe Reader, BW-Handout-V2 0 0.pdf - Adobe Reader, etc.
I am looking a code which serach Adobe in all the range text and change the next column value as 1.
Please help to correct code.
Sub Second_Allocation3()
Dim msht As Worksheet
Dim sht As Worksheet
Dim myarr As Variant
Dim rng As Range
Dim srng As String
Set msht = Worksheets("Data")
Set sht = Worksheets("Reference Data")
lr = sht.Range("D125000").End(xlUp).Row
lr1 = msht.Range("A125000").End(xlUp).Row
msht.AutoFilterMode = False
' msht.Range("K:K").Value = msht.Range("A:A").Value
For i = 2 To lr
srng = sht.Range("D" & i).Value
' msht.Range("A1:I" & lr).AutoFilter Field:=1, Criteria1:=srng
For J = 2 To lr1
If msht.Range("A" & J).Find(What:=srng, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) Then
msht.Range("I" & J).Value = "1"
End If
Next J
' msht.AutoFilterMode = False
Next i
End Sub
I am looking for a code which can help me to find a word based on provided range input in another range and then change the value of another equivalent range. I have write below query but it's not working fine. Please help to correct the code.
For Ex. Reference Range value = Adobe and Fiand Range Values are redbooks.pdf - Adobe Reader, 02-COBOL-Dataname-Editing Charaters.pdf - Adobe Reader, TSO_and_ISPF.pdf - Adobe Reader, Adobe Reader, BW-Handout-V2 0 0.pdf - Adobe Reader, etc.
I am looking a code which serach Adobe in all the range text and change the next column value as 1.
Please help to correct code.
Sub Second_Allocation3()
Dim msht As Worksheet
Dim sht As Worksheet
Dim myarr As Variant
Dim rng As Range
Dim srng As String
Set msht = Worksheets("Data")
Set sht = Worksheets("Reference Data")
lr = sht.Range("D125000").End(xlUp).Row
lr1 = msht.Range("A125000").End(xlUp).Row
msht.AutoFilterMode = False
' msht.Range("K:K").Value = msht.Range("A:A").Value
For i = 2 To lr
srng = sht.Range("D" & i).Value
' msht.Range("A1:I" & lr).AutoFilter Field:=1, Criteria1:=srng
For J = 2 To lr1
If msht.Range("A" & J).Find(What:=srng, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) Then
msht.Range("I" & J).Value = "1"
End If
Next J
' msht.AutoFilterMode = False
Next i
End Sub