VBA loop to next row

[Lorlai]

I have a loop that goes down column if the column header value = "Buyout Product Date". This loop formats the cells in that column. I then need to get back to the header row and continue my normal formating. When I am at the bottom of my column, how do I jump to the top of the next column?

 If ActiveCell.Value = "Buyout Product Date" Then
    ActiveCell.Offset(1, 0).Select
    Do
    ActiveCell.Format = "m/d/yyyy"
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell)

From here I want to continue to the next column (say "Buyout Product Date" was Column D, I've looped down to the very last data filled cell in Column D, and want to jump right back up to Column E:1. But I won't know exactly what Column this will be each time.)

Thanks for any help!

 

[kpark91]

You would probably need nested loops

Do While criteria1
    'Loop through the columns
    Do While criteria2
        'Loop through the rows
 
    Loop
Loop

This will loop through all the cells

Sub loopthroughall()
    Dim LR As Long, LC As Long, i As Long, j As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    LC = Cells(1, Columns.count).End(xlToLeft).Column
 
    For i = 1 To LC
        For j = 1 To LR
            Cells(j, i).Value = "Blarg"
        Next j
    Next i
End Sub

 

[JackDanIce]

What do you plan on doing with the next column of data, where ever you jump to? As it is, you shouldn't if possible use '.Select' or '.Activate'. Your code below could be written as:

Dim FindItem as String
Dim FRange as Range

' You can change the FindItem to whatever you're looking for
FindItem = "Buyout Product Date"


On Error Resume Next
Set FRange = Cells.Find(What:=FindItem)
If FRange Is Nothing Then 
   MsgBox FindItem & " does not exit, please check and start again. Macro stopping"
   Exit Sub
Else
   With Range(Cells(FRange.Row + 1, Frange.Column), FRange.End(xlDown))
          .Format = "m/d/yyyy"
   End With
End If

'This bit below, despite me saying don't use .Select will go to the next column
FRange.Offset(0,1).Select

 

[Lorlai]

Thank you for your fast reply. My loop is a little different, as I don't need to loop through the entire worksheet. I have formatting that needs to be done to only the header columns, and then if this one particular header appears, the rows in that header will need to be formatted. Another way I have found to do this is:

If ActiveCell.Value = "Buyout Product Date" Then
    ActiveCell.EntireColumn.Select
    Selection.NumberFormat = "m/d/yyyy"

I then want the active cell to become the first cell of the column right next to this column. Is there a fast simple way to do this?

 

[JackDanIce]

With the second code you suggested, then use:

If ActiveCell.Value = "Buyout Product Date" Then
    ActiveCell.EntireColumn.Select
    Selection.NumberFormat = "m/d/yyyy"
    ActiveCell.Offset(0,1).Select

But again, I don't advocate the use of '.Select'

 

[Lorlai]

Thanks JackDanIce, you have gotten me on the right track.

Just curious, you say I should avoid .select, I'm not sure I totally understand why. Do you have a moment to explain this to me?


Lorelai

 

[JackDanIce]

Main reason is, it slows down your code if you include it and as your code becomes more complex, it can cause problems when executing. Not using it keeps your code efficient, much more stable and easier for other people to read (helpful when you're posting code that doesn't work and you're asking for help from others)

If you do a search of this board you can find lots of reasons why. Here are a few links I found doing an internet search:

http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm
http://www.businessprogrammer.com/201105/power-excel-vba-secret-avoid-using-select/
http://bytes.com/topic/visual-basic...elect-method-worksheets-vba-macro-help-please

Hope this helps Lorelai