0

I am trying to make a macro button that will automatically select column H and then search and select one by one in an array(one every time I click the macro) every cell in that specific column, that contains the € symbol. I can do that exactly as I want manually using the native excel search function but it is time consuming. Yet I don't know how to do that in VBA. Note that the cells in column H are currency formatted..The code that almost works for me so far is this:

Search = InStr(ActiveCell.NumberFormat, Chr(128))

Selection.Find(What:=Search, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

BUT the above code doesn't automatically select column H for search. I have to do that manually. When i insert in the above code Columns("H").Select (in order to make the code select the column H automatically) the macro selects the first cell that contains the € symbol in the column H (which is what i want) BUT when clicking again it does not go to the NEXT cell that contains the € symbol in that column. It sticks on the first finding. Could you please help me?

braX
  • 11,506
  • 5
  • 20
  • 33
Kostantinos Kalominidis
  • 21
  • 5
  • 2
    Possible duplicate of [VBA find macro stops working when search range included in code - Please advice](https://stackoverflow.com/questions/48643712/vba-find-macro-stops-working-when-search-range-included-in-code-please-advice) – vacip Feb 08 '18 at 10:20

3 Answers3

1

You should always avoid using Selection. or .Select.

Instead of Selection.Find specify the correct range:

Worksheets("MySheetName").Columns("H").Find

Also have a look at the Range.FindNext Method (Excel). With find you will always find the first occurrence only. For further searches you will need to use FindNext.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
1

I am not sure what do you want to achieve, but if you need to find cells formatted as Currency, I would rather use this code:

Sub findCur()
    Dim rngCol As Range
    Set rngCol = Range("H:H")

    With Application.FindFormat
        .Clear
        .NumberFormat = "$#,##0.00"
    End With
    rngCol.Find(What:="*", After:=ActiveCell, SearchFormat:=True).Select
End Sub
MarcinSzaleniec
  • 2,246
  • 1
  • 7
  • 22
0

Add a condition to the selection, something like:

If Selection.Column<>7 then Columns("H").select

This way if you are already in column H, it won't reselect it, but if you are not there, it will go there.

vacip
  • 5,246
  • 2
  • 26
  • 54