VBA - Finding first cell address with formula in a column

[dellzy]

Hi Experts,

I wish to know which cell in column M in Excel worksheet that has formula so that it will be the first range address when user selecting a range to be pasted as values. So far, what I've got seems to check the cell which is not blank only. So, it will still return the cell address even it contains values only, not formula. Also, I don't know how to stop the loop once it finds the first cell address with formula.

Below what I have so far. Appreciate some good help, please.

Function IsFormula(cell_ref As Range)
    IsFormula = cell_ref.HasFormula
End Function


Sub ShowCellWFormula()
Dim j As Integer
Dim DestRng As Range
j = 5
LRow = 8


For j = j To LRow
If Range("M" & j).HasFormula() = False Then
MsgBox "nope"
j = j + 1
End If
Set DestRng = Range("M" & j)
MsgBox DestRng.Address


Next j
End Sub

Thank you so much in advance.

 

[JoeMo]

This will find the first formula cell in Col M. You can adapt it to do whatever you want once the first cell is identified.

Sub ShowCellWFormula()
Dim DestRng As Range
LRow = Range("M" & Rows.Count).End(xlUp).Row
With Range("M1:M" & LRow)
       On Error Resume Next
       Set DestRng = .SpecialCells(xlCellTypeFormulas).Areas(1).Cells(1, 1)
       On Error GoTo 0
       If Not DestRng Is Nothing Then
              MsgBox "First formula cell in col M is cell " & DestRng.Address
       Else
              MsgBox "Col M doesn't have any formula"
       End If
End With
End Sub

 

[dellzy]

Wow! It works exactly how I want it to be! Tq soooo much JoeMo ! Your code brings so much benefit to my other function codes!

God Bless You..

 

[Rick Rothstein]

This will find the first formula cell in Col M. You can adapt it to do whatever you want once the first cell is identified.

Sub ShowCellWFormula()
Dim DestRng As Range
LRow = Range("M" & Rows.Count).End(xlUp).Row
With Range("M1:M" & LRow)
       On Error Resume Next
       Set DestRng = .SpecialCells(xlCellTypeFormulas).Areas(1).Cells(1, 1)
       On Error GoTo 0
       If Not DestRng Is Nothing Then
              MsgBox "First formula cell in col M is cell " & DestRng.Address
       Else
              MsgBox "Col M doesn't have any formula"
       End If
End With
End Sub

You can shorten the highlighted line of code to this..

Set DestRng = .SpecialCells(xlCellTypeFormulas)(1)

Since LRow is used only once in the code and since the object of the With statement is referenced only once, it is possible to condense your code to this...

[SIZE=1]Sub ShowCellWFormula()
  Dim DestRng As Range
  On Error Resume Next
  Set DestRng = Range("M1", Range("M" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeFormulas)(1)
  On Error GoTo 0
  If Not DestRng Is Nothing Then
         MsgBox "First formula cell in col M is cell " & DestRng.Address
  Else
         MsgBox "Col M doesn't have any formula"
  End If
End Sub[/SIZE]

While in "real life", I would probably use the method you posted, I thought the readers of this thread might find this alternated code of interest in that it eliminates calling SpecialCells which, in turn, eliminates the need for the error handler, although I will admit it obfuscates the code just a "little bit"...

[SIZE=1]Sub test()
  Dim Rw As Long, ColM As Variant, DestRng As Range
  ColM = Range("M1", Cells(Rows.Count, "M").End(xlUp)).Formula
  ColM = Join(Application.Transpose(ColM), Chr(1))
  If InStr(Chr(1) & ColM, Chr(1) & "=") Then
    Set DestRng = Cells(UBound(Split(Chr(1) & Split(Chr(1) & ColM, Chr(1) & "=")(0), Chr(1))), "M")
    MsgBox "First formula cell in col M is cell " & DestRng.Address(0, 0)
  Else
    MsgBox "Col M doesn't have any formula"
  End If
End Sub[/SIZE]

 

[dellzy]

You can shorten the highlighted line of code to this..

Set DestRng = .SpecialCells(xlCellTypeFormulas)(1)

Since LRow is used only once in the code and since the object of the With statement is referenced only once, it is possible to condense your code to this...

[SIZE=1]Sub ShowCellWFormula()
  Dim DestRng As Range
  On Error Resume Next
  Set DestRng = Range("M1", Range("M" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeFormulas)(1)
  On Error GoTo 0
  If Not DestRng Is Nothing Then
         MsgBox "First formula cell in col M is cell " & DestRng.Address
  Else
         MsgBox "Col M doesn't have any formula"
  End If
End Sub[/SIZE]

While in "real life", I would probably use the method you posted, I thought the readers of this thread might find this alternated code of interest in that it eliminates calling SpecialCells which, in turn, eliminates the need for the error handler, although I will admit it obfuscates the code just a "little bit"...

[SIZE=1]Sub test()
  Dim Rw As Long, ColM As Variant, DestRng As Range
  ColM = Range("M1", Cells(Rows.Count, "M").End(xlUp)).Formula
  ColM = Join(Application.Transpose(ColM), Chr(1))
  If InStr(Chr(1) & ColM, Chr(1) & "=") Then
    Set DestRng = Cells(UBound(Split(Chr(1) & Split(Chr(1) & ColM, Chr(1) & "=")(0), Chr(1))), "M")
    MsgBox "First formula cell in col M is cell " & DestRng.Address(0, 0)
  Else
    MsgBox "Col M doesn't have any formula"
  End If
End Sub[/SIZE]

Well, tqvm for the detailed alternate codes to simplify it, Mr. Rick Rothstein.. Totally appreciate the time you spent and knowledge you transfer here. You guys are awesome!