How to use Cells.Find function to search only in one column?

[dduranic]

I want to find a content of ActiveCell only in column "C", beginning from cell C4.What must be in Look in, and Look at?How to accomplish to match whole number,ex. "1" in cell with "1",no to find "1" in "123"!

This function searches in whole sheet,but i need only in C column!
Cells.Find(What:=ActiveCell, After:=[c4], LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate

Please,help me!

 

[Scott Huish]

Hi, welcome to the board!

It's better to set it to a range object, rather than using Activate. This way you can check to see if anything was actually found or not.

I think this should do it for you:

Sub findit()
Dim c As Range
Set c = Range("C4:C65536").Find _
    (What:=ActiveCell, _
    LookIn:=xlValues, LookAt:=xlWhole, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then c.Select
End Sub

xlwhole in LookAt will look at the whole value of the cell rather than part of it.

 

[dduranic]

Thank you very much!!
That works fine.
I need to programm something in this macro,and if you know how,I would be greateful to help me!
I need to add the content (number) of the cell in D column behind the active cell in C column (same row) to the cel in D column in the same row where is the found cell.
For exampe,if the content of active cell is found in C10,I need to add the number in cell D (same row as the active cell) to the number in cell D (same row as the found cell)
How to do this?

 

[Scott Huish]

Will there be more than one of what you are searching for? Is what you are looking for a set value or do you want it to look for what is in the contents of a cell, any cell in particular?

 

[dduranic]

No,there will be only one of what I'm searching for!This cells in C and D column are only connected with row number.When it finds content of active cell in some cell in same column (C),i need to add number in cell in column D in the same row as the active cell, to a number in cell in column D of the same row where is the found cell in column C.There is nothing to work with found cell,only to use it's row number to calculate cells in D column.

 

[Scott Huish]

Perhaps:

Sub findit()
Dim c As Range
Set c = Range("C4:C65536").Find _
    (What:=ActiveCell, _
    LookIn:=xlValues, LookAt:=xlWhole, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then c.Offset(, 1) = c.Offset(, 1) + c
End Sub

 

[dduranic]

That functoin adds found cell in C to a D.I need to add cell in D to D.
Ex. active cell is C10,and i need to add D10 to a cell in D in the same row where it found cell in C.

 

[Scott Huish]

You lost me, you are searching for the value in the activecell, so the value it found should be the same value as in the activecell.

Also why is your activecell in the same column you are searching in, if you are clicked on the value you are searching for, you already know where it is.

What am I missing?

 

[dduranic]

Column C is identitiy number of product,and column C is quantity of that product.When I intput new ID for ex. in row 10000 it writes me a message in F column that this product whit same id is already written somewhere.I need to find it and add quantity of new intput in cell D to a quauantity of found record.

 

[Scott Huish]

OK, uncomment out this line
'ActiveCell.Resize(, 2).ClearContents
if you want it to clear the active cell and the value in Column D of the same row after it does it.

Sub findit()
Dim c As Range
Set c = Range("C4:C65536").Find _
    (What:=ActiveCell, _
    LookIn:=xlValues, LookAt:=xlWhole, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
    c.Offset(, 1) = c.Offset(, 1) + ActiveCell.Offset(, 1)
    'ActiveCell.Resize(, 2).ClearContents
End If
End Sub