Search for String in a cell in Particular Range in Various Sheets

[Meesam]

Hi Everyone,

I have almost 90 different workbooks and in each of them there are like 5-10 sheets. I basically have to search through Each of these sheets in each workbook to find a Value NEXT to a certain cell. e.g. If in a sheet, in a particular range it is written "Contract Value:" then i will get the value NEXT to this cell which of course will be the value of contract or value required.
The problem is while searching a certain range that contains the string "Contract" its NOT always getting it right due to the fact that somewhere it is written "Contract Value:" and somewhere else it might be "Contract Value: " (i.e. with spaces after "Value:") and sometimes it can be like "Contract Value:" (more spaces b/w Contract and value).
Here is my overall code but its NOT giving correct values where it deviates from the EXACT "Contract Value:" string.

For Each ws In wb.Worksheets
            count = count + 1
            On Error Resume Next
                With ws
                    ws.Unprotect
                    Set valLoc1 = .Range("F30:F70").Find(What:="Gross Margin", LookIn:=xlValues, LookAt:= _
                        xlPart, SearchOrder:=xlByRows)
                    valNew1 = valLoc1.Offset(0, 1).Value
                    For Each rCell In Range("F5:F10")
                        If InStr(1, rCell.Value, "Contract Value:") Then
                            valNew2 = rCell.Offset(0, 1).Value
                            Else
                            End If
                    Next
                    newRng.Offset(count, 1).Value = wb.Name
                    newRng.Offset(count, 2).Value = ws.Name
                    newRng.Offset(count, 3).Value = valNew1
                    newRng.Offset(count, 4).Value = valNew2
                End With
        Next ws

Any help is appreciated.

 

[alvinwlh]

Hi

You can try:

For Each ws In wb.Worksheets
            count = count + 1
            On Error Resume Next
                With ws
                    ws.Unprotect
                    Set valLoc1 = .Range("F30:F70").Find(What:="Gross Margin", LookIn:=xlValues, LookAt:= _
                        xlPart, SearchOrder:=xlByRows)
                    valNew1 = valLoc1.Offset(0, 1).Value
                    For Each rCell In Range("F5:F10")
[COLOR=red]                        If InStr(1, UCase(rCell.Value), "CONTRACT") > 0 And InStr(1, UCase(rCell.Value), "VALUE:") > 0 Then[/COLOR]
                            valNew2 = rCell.Offset(0, 1).Value
                            Else
                            End If
                    Next
                    newRng.Offset(count, 1).Value = wb.Name
                    newRng.Offset(count, 2).Value = ws.Name
                    newRng.Offset(count, 3).Value = valNew1
                    newRng.Offset(count, 4).Value = valNew2
                End With
        Next ws

Any help is appreciated.

 

[Meesam]

Well I just tried it but now NOTHING comes. I think the problem is with the spaces and like finding a way to search for a string inside another string and WHENEVER that string is found, it should work. I tried with FIND but it doesn't work when its NOT exactly equal.

 

[alvinwlh]

Hi,

I am able to get below result, and the code capture the last row as well.


<TABLE style="WIDTH: 165pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=220><COLGROUP><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 5302" width=145><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 109pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" id=td_post_2957452 height=20 width=145>contract value:</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=75>FOUND</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>Contract Vaalue: </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>contract value:</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">FOUND</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>contract value:</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">FOUND</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>contract vdfalue:</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=20>contract dfd value:</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">FOUND</TD></TR></TBODY></TABLE>

Just wandering what is your Range("F5:F10")

 

[Meesam]

Well basically "F5:F15" is the RANGE in which the Value "Contract Price:" exists. I Tried your code but still its NOT working at all and giving absolutely NOTHING now.

 

[alvinwlh]

Hi,

For Each rCell In Range("F5:F10")

If you are looking at each sheet,

I think it should be .Range("F5:F10") instead of Range("F5:F10")

But if it is a fix range which taken from a sheet then it is ok.

 

[Meesam]

No that's not the case but anyways brainstorming worked and really THANKS for your help to give me a direction though solution is bit different.

Here is my Solution:

For Each rCell In Range("F5:F15")
                     If InStr(1, rCell.Value, "Contract Price:") > 0 Then
                            valNew2 = rCell.Offset(0, 1).Value
                            Else
                            End If
                    Next

Regards.