VBA to loop through non-blank cells in column and return next value in a different cell?

[AGrayson84]

Hi everyone, I'm having trouble trying to find anything anywhere online that even does anything remotely similar to what I'm looking for so I can try to learn how to tweak it, but I can't figure out how to even get started on the script, let alone write it out in full. I'm not experienced with VBA and only know how to do a very few basic things, so anyone who is willing to help out I'd super-appreciate it!

I'm looking to use cell B14 to enter an exact value that is in column A. When I run the macro I would like it to look at the value that is currently in B14, and then loop thru the next non-blank value in column A (excluding my header in row 1), and enter that next value into B14. I will be using a button to run this macro, so I guess you can think of it as a dynamic "next" button.

An example would be:

-----------------------------------------------------

A1: [HEADER]
A2: Apples
A3:
A4: Pears
A4: Grapes
A6: Peaches
A7:
A8:

If the current value of B14 was "Peaches", and the were no other non-blank cells after it in the entire column A, the value of B14 would become replaced with "Apples". If you hit the "Next" button again, the value of B14 would become "Pears".

-----------------------------------------------------

Hopefully I described that in a clear enough manner. Thanks a bunch to anyone who is willing to help.... I've spent a few hours the last couple of days trying out various functions and none of them seem to even get me started with what I'm looking to do. Thanks again!

-Andrew

 

[Fluff]

How about

Sub MyFindNext()
   Dim fnd As Range
   Set fnd = Range("A:A").find(Range("B14"), , , xlWhole, , , False, , False)
   Range("B14").Value = Range("A2:A" & Rows.Count).find("*", fnd, , , , , , , False)
End Sub

 

[offthelip]

An alternative

Sub tesdt()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(2, 1), Cells(lastrow, 1))
inp = Cells(14, 2)
Found = False
For i = 1 To lastrow - 1
 If inp = inarr(i, 1) Then
  For j = i + 1 To lastrow - 1
   If inarr(j, 1) <> "" Then
    Cells(14, 2) = inarr(j, 1)
    Found = True
    Exit For
   End If
  Next j
   
   If Not (Found) Then
    For j = 1 To i
        If inarr(j, 1) <> "" Then
         Cells(14, 2) = inarr(j, 1)
         Exit For
        End If
    Next j
  End If
  End If
Next i
End Sub

 

[Norie]

Try this.

Dim arrFruit()
Dim strCurrant As String
Dim idx As Variant

    arrFruit = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
    
    strCurrant = Range("B14").Value
    
    If strCurrant = "" Then
        idx = 1
        
    Else
        idx = Application.Match(strCurrant, arrFruit, 0)
        
        If idx = UBound(arrFruit) Then
            idx = 1
        Else
            Do
                idx = idx + 1
            Loop Until arrFruit(idx, 1) <> ""
        End If
    End If
    
    Range("B14").Value = arrFruit(idx, 1)

 

[AGrayson84]

Thanks so much for your help guys!!! I did get up getting some errors with yours Fluff, but I tried offthelip's code after that and it worked perfectly. I really appreciate your guys' help a whole lot.... I would have never been able to figure that out!!