Copy formulas with text to new sheet

[poitbot]

Hey again!

Really loving this place.

I have a macro that extracts text from multiple worksheets and places it in a column along with the cell reference and worksheet name. I'd like to do the same thing but instead of text I'd like it to pull formulas with text only. Some of the formulas have more than one word associated with them.

EX:

=IF($B34="","Enter Part Number",IF($D34="","Enter OEM",IF($F34="","Enter Quantity",IF($G34="","Enter Published List Unit Price",IF(VLOOKUP($D34,LAN_Range,2,FALSE)="","Enter NE Discount",($G34-(VLOOKUP($D34,LAN_Range,2,FALSE)*$G34))*$F34)))))

Here's the current formula:

Public Sub texttonewsheet()
Dim n As Long, i As Long
Dim Rng As range
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
n = 1
For i = 2 To Sheets.Count
For Each Rng In Sheets(i).range("A1:CI200")
If Rng.Value <> "" Then
If Not Application.IsNumber(Rng) Then
Sheets(1).range("A" & n).Value = Rng.Value
Sheets(1).range("B" & n).Value = Rng.Address
Sheets(1).range("C" & n).Value = Sheets(i).Name
n = n + 1
End If
End If
Next Rng
Next i
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub


Any help would be appreciated...

Thanks!

Ad

 

[mikerickson]

Try using the moving the .FormulaR1C1 property of the cells rather than the .Value property.

 

[poitbot]

It's still copying the text over...Also creates an error which is probably due to the quotations.

Btw, love the beard in your picture.

 

[poitbot]

Anyone else?

 

[mikerickson]

Try one of these changes

If Not Application.IsNumber(Rng) Then
    Sheets(1).range("A" & n).Value = Chr(39) & Rng.Formula
    Sheets(1).range("B" & n).Value = Rng.Address
    Sheets(1).range("C" & n).Value = Sheets(i).Name
    n = n + 1
End If

If Not Application.IsNumber(Rng) Then
    Sheets(1).range("A" & n).Formula = Rng.Formula
    Sheets(1).range("B" & n).Value = Rng.Address
    Sheets(1).range("C" & n).Value = Sheets(i).Name
    n = n + 1
End If

 

[poitbot]

Those were the first things I tried...

Error: Application-Defined or Object-defined error

Sheets(1).range("A" & n).Formula = Rng.Formula and Chr(39) & Rng.Formula

Same results.

 

[mikerickson]

Is that an exact copy of the line that errored? What was n when it errored?

 

[poitbot]

Oh,

that wasn't the exact line. I was abbreviating.

Sheets(1).range("A" & n).Value = Chr(39) & Rng.Formula

And

Sheets(1).range("A" & n).Formula = Rng.Formula

Are the formulas that error.

n is always at 1915

Problem is it's grabbing some of the formulas but it's also grabbing some text as well?

Odd.