CODEWITHSUNDEEP
excelvba
Etien V
Etien V

Reputation: 35

VBA Help. Simple code help. Make the activecell the first cell with value>100

I created a simple excel file with 1 column and 200 rows. Each cell contains an integer value.

I'm trying to create a VBA code that will automatically make the first cell with an integer value of over 100 the active cell.

This is the code that i came up with. My problem is that it does nothing :(

 Sub test1()

      Dim trial As Range
      Dim cell As Range

      Set trial = Range("A1:A100")

      For Each cell In trial          
           If cell > 100 Then
      End If         
      Next cell

 End Sub

Upvotes: 3

Views: 172

Answers (2)

brettdj
brettdj

Reputation: 55672

Without loops:

Dim rng1 As Range
Dim lngPos As Long

Set rng1 = [a1:a100]
lngPos = Evaluate("=MIN(IF(" & rng1.Address & ">100,ROW(" & rng1.Address & "),1000))")

If lngPos < rng1.cells.count Then Application.Goto rng1.Cells(lngPos)

Upvotes: 2

Jean-Pierre Oosthuizen
Jean-Pierre Oosthuizen

Reputation: 2683

Try this.

Always use .Value when working with a Range and you are wanting to use the Cell Value. As mentioned in a comment below @gizlmeier mentioned. Its good to get the smalls things like this right when you start programming for cleaner coding at a later stage.

Also you Sub will run and then stop without changing anything, but it does work. So you just need to add the Activate to the If statement and then decide to End just the For Loop or to End the Sub

 Sub test1()

      Dim trial As Range
      Dim cell As Range

      Set trial = Range("A1:A100")

      For Each cell In trial

           If cell > 100 Then
                cell.Activate
                'Exit For will exit the For Loop
                Exit For
                'End will stop the code from running
                End
           End If

      Next cell

 End Sub

Upvotes: 2

Related Questions