Solved find which row a text value is in

July 24, 2013 at 14:06:15
Specs: Windows 7
Sub delduplicatesc()
'
' deletes duplicates and sorts list list

'
    If MsgBox("HAVE YOU SELECTED THE CORRECT DETAIL FROM THE DROPDOWN LIST?", vbYesNo) = vbYes Then
 
   With Sheets("LEGEND").Range("A4:A34")
     Set d = .Find(Sheets("NGCCARD").Range("H12"))
      If Not d Is Nothing Then
  
'Determine row number with text value equal to Range "H12"



' clear data from cell range in determined row
        .Range("A(?)").ClearContents
            .Range("C(?):U(?)").ClearContents
                .Range("W(?):AN(?)").ClearContents
        
' sorts detail list into order
    ActiveWorkbook.Worksheets("LEGEND").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("LEGEND").Sort.SortFields.Add Key:=Range("A4:A34") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("LEGEND").Sort
        .SetRange Range("A4:AN34")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
   
     Sheets("NGCCARD").Visible = False
         
          Else
        MsgBox "Detail Information DOES NOT Exist !!", vbCritical Or vbOKOnly
        Sheets("NGCCARD").Range("C4").Select
          
    End If
       End With
         End If

End Sub


Hi there,
I am trying to adapt the above vba, but as you can see, I am a little stuck with the coding

With Sheets("LEGEND").Range("A4:A34")
Set d = .Find(Sheets("NGCCARD").Range("H12"))
If Not d Is Nothing Then

'Determine row number with text value equal to Range "H12"

NOT SURE OF THE CODING FOR THIS BIT

' clear data from cell range in determined row
.Range("A(?)").ClearContents
.Range("C(?):U(?)").ClearContents
.Range("W(?):AN(?)").ClearContents

AND THEN THESE THREE ROWS ARE AFFECTED AS WELL

Any assistance on this would be greatly appreciated


See More: find which row a text value is in

Report •

#1
July 24, 2013 at 15:03:37
✔ Best Answer
I recently asked if you were using the debugging techniques in the How-To found here.

http://www.computing.net/howtos/sho...

There is a section on using the Watch window. Set a Watch on your variable "d" as you single step through the code. Once "d" is no longer empty, a plus sign will appear next to it in the Watch window. If you click the plus sign, you will see that "d" now contains everything you could ever want to know about it, including what Row the search string was found in.

So, how do you use that information? I'll give you a hint:

There's a . (dot) involved.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 28, 2013 at 04:18:48
    Rownumb = d.Row
    

    
'clear contents of the cell range
        Sheets("LEGEND").Range("A" & Rownumb).ClearContents
          Sheets("LEGEND").Range("C" & Rownumb & ":U" & Rownumb).ClearContents
            Sheets("LEGEND").Range("W" & Rownumb & ":AN" & Rownumb).ClearContents

Thanks so much for your help. The 'watch' function really helps. The above is the coding I needed and with the rest of the code works as required.
Again, thank you


Report •

#3
July 28, 2013 at 09:46:02
The above is the coding I needed

I knew that. ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question