CODEWITHSUNDEEP
excelvba
Jamie Walker
Jamie Walker

Reputation: 213

Need a find function to search one column of one sheet

I need a find function to search in Column B of my worksheet titled "Quote Sheet" to find "Profit Adjustment" and it would be nice if it was case sensitive. Below is the code I am working with but I can't get the range or the wording correct. Any help is appreciated.

Dim rFound As Range
Set rFound = Range("B10:B1000")

    Cells.Find(What:="Profit Adjustment", After:=ActiveCell, LookIn:=xlFormulas,         LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True).Activate
    ActiveCell.Offset(0, 8).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Upvotes: 1

Views: 1462

Answers (3)

StoriKnow
StoriKnow

Reputation: 5866

It wasn't immediately clear to me whether you only want to find the first occurrence of Profit Adjustment, or if you care about all occurrences. If you want to find all rows in Column B that contain Profit Adjustment, the below Macro will work as-is. If you want to find only the first occurrence, then simply uncomment the line that says Exit For.

Here's the code:

Sub FindValueInColumn()
    Dim rowCount As Integer, currentRow As Integer, columnToSearch As Integer
    Dim searchString As String
    Dim quoteSheet As Worksheet
    Set quoteSheet = ActiveWorkbook.Sheets("Quote Sheet")
    searchString = "Profit Adjustment"  'string to look for
    columnToSearch = 2                  '2 represents column B

    rowCount = quoteSheet.Cells(Rows.Count, columnToSearch).End(xlUp).Row
    For currentRow = 1 To rowCount
        If Not Cells(currentRow, columnToSearch).Find(What:=searchString, MatchCase:=True) Is Nothing Then
            Cells(currentRow, 8).Value = Cells(currentRow, columnToSearch).Value
            'uncomment the below "Exit For" line if you only care about the first occurrence
            'Exit For
        End If
    Next
End Sub

Before search: enter image description here

After search:

enter image description here

Upvotes: 1

mechanical_meat
mechanical_meat

Reputation: 169274

I would re-write your example like this:

Sub copy_paste_example()
    Dim c As Range
    With ActiveWorkbook.Sheets("Quote Sheet")
        Set c = .Columns("B").Find(What:="Profit Adjustment", _
                                   LookIn:=xlFormulas, LookAt:=xlPart, _
                                   SearchOrder:=xlByRows, _
                                   SearchDirection:=xlNext, MatchCase:=True)
        On Error GoTo NotFoundErr:
            c.Offset(0, 8).Value = c.Value
    End With
    Exit Sub
NotFoundErr:
    Debug.Print "value not found"
End Sub

Notes:

  1. You weren't ever using the rfound Range Object so I removed it.
  2. Activate and Select are not needed, and may even slow down your code.
  3. Remember that Find returns a Range Object which can be useful later on in your code.

Upvotes: 1

user2063626
user2063626

Reputation: 

Sub samPle()

    Dim rFound As Range, cellToFind As Range
    Set rFound = Sheets("Quote Sheet").Range("B10:B1000")

    Set cellToFind = Cells.Find(What:="Profit Adjustment", MatchCase:=True)

    If Not cellToFind Is Nothing Then
        cellToFind.Activate
        ActiveCell.Offset(0, 8).Copy ActiveCell.Offset(0, 8)
    End If
End Sub

Upvotes: 1

Related Questions