Loop until ActiveCell is a formula

[pincivma]

Hi again

I am doing a Do Loop. I want the cursor to stop when the Cell has a formula in it. The formulas very so it can't be a specif formula that the loop stops at. That I can program myself. Is there such a code? If there is one, just send me the code say starting in Column A1 and I ca figure out the rest myself, I hope.

 

[Kenneth Hobson]

Sub Main()
  Dim c As Range
  For Each c In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    If c.HasFormula Then Exit For
    Debug.Print c.Address, c
  Next c
End Sub

 

[Rick Rothstein]

Here is another macro to consider...

Sub LoopFromA1UntilFormulaCell()
  Dim Cell As Range
  For Each Cell In Columns("A").SpecialCells(xlConstants).Areas(1)
    Debug.Print Cell.Address(0, 0)
  Next
End Sub

 

[rlv01]

Using a DO Loop

Sub DoLoop()
    Dim I As Long
    
    I = 1
    Do While I <= 100
        With ActiveSheet.Cells(I, 1)
            If .HasFormula Then
                Debug.Print "Cell " & .Address & " contains a formula"
            End If
            I = I + 1
        End With
    Loop
End Sub

 

[pincivma]

Hi Kenneth, Rick

Thank you so much for the code. You have made my day. I was just wondering if this is possible to do. Say I have range("A1:H400") in sheet1, Workbook1. Now I want to copy Everything In Sheet1, Workbook1, Range("A1:H400") except formulas to Workbook2, Sheet1. Let me explain. Sheet1, Workbook1 has numbers, dropdown lists, formulas, text, numbers etc. What I want to accomplish is to copy everything from Workbook1, Sheet1 to Workbook2, Sheet2 except formulas. I other words I want the formulas to stay in Workbook1, Sheet1 and not copied over to Workbook2, Sheet1. Not sure if this is possible or not to do.

Thanks