Find formulas in range

[jlg5454]

Hello,

Is there an example out there of a code that will do the following:

Within a range say A7:k37, find the first row out of that range that has a formula in every cell then do a copy paste value only to that first row with formulas.

Thanks for any assistance.

 

[xlbo]

what have you tried so far?

Have a look at the For Each Next construct and the .Formula property of a range

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Loomah]

Hi
I just got intrigued by the problem so this is what I have so far...

Sub lime()
Dim rng1 As Range
Dim i As Integer
Dim flag As Boolean

With Range("A7:K37")
    Set rng1 = .SpecialCells(xlFormulas)
    rng1.Select 'not required, just a test!
    For i = 1 To rng1.Areas.Count
        If rng1.Areas(i).Cells.Count = .Columns.Count Then
            flag = True
            Exit For
        End If
    Next
End With

If flag Then
    rng1.Areas(i).Copy
    rng1.Areas(i).PasteSpecial xlValues
    Application.CutCopyMode = False
End If
End Sub

(btw, xlbo, i know what you're driving at with the question but boredom has the better of me at the moment!!)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[combo]

Loomah,
the code will not work when any of Areas(i) has more than one row.
Insted, I would use:

Dim NoOfCols As Integer
Dim rngSource As Range, rngFormulas As Range, rngRow As Range
Set rngSource = Range("A1:K37")
NoOfCols = rngSource.Columns.Count
Set rngFormulas = rngSource.SpecialCells(xlCellTypeFormulas)
For Each rngRow In rngSource.Rows
    If Not Intersect(rngFormulas, rngRow) Is Nothing Then
        If Intersect(rngFormulas, rngRow).Cells.Count = NoOfCols Then
            rngRow.Copy
            rngRow.PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
            Exit For ' if you need to convert only one row
        End If
    End If
Next rngRow

combo

 

[Loomah]

oh yeah, combo, i suppose it won't work in any situation where the area contains the same number of cells as there are columns in the source data, eg A1:A11 - or is that what you meant? it seems i was not only bored but stupid!!

anyway - off topic, why does my sig have a sponsored link? i find the link quite amusing but...

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[combo]

Just so, but there is another point too. I wasn't digging much in the way excel recognises areas, but when I first filled range "a5:g5" and next "e6:e10" excel (after SpecialCells) reported 3 areas (in this order): "a5:d5", "f5:g5" and "e5:e10".
BTW, I do not see any sponsored links now, however they appear occasionally...

combo

 

[Loomah]

to be honest i've never really understood areas and i've seen some strange results. even just selecting contiguous and non-contiguous ranges then doing selection.areas.count has thrown up strange answers in the past.

ho-hum, just another feature i suppose, and i thought i'd actually found a use for it here.

and the link was attached to the word women - recommending having a look at ask.com to "find out more about women"
computers are getting too smart (and personal) if they think (realised) i needed something like that!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[jlg5454]

Thanks guys. I'll will try these suggestions. Does anyone have any good sites to go to on learning VBA?

Thanks,

Joe

 

[SkipVought]

Learning VBA.

1. Macro recorder - observe code and modify

2. Tinker

3. VB Help/Excel Object Model

4. Play around

5. Tek-Tips read postings -- look for stars -- post your own questions

6. Tinker some more

7. Check out the bookstore. I like John Walkenback. There are other good authors.

8. Try stuff out.

Good luck!

Skip,

_[red][/red]

 

[jlg5454]

Thanks! The guys above help out tremoundously. Thanks for all your advices.