Copy formulas with text to new sheet

poitbot

New Member
Joined
Nov 22, 2006
Messages
34
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
Try using the moving the .FormulaR1C1 property of the cells rather than the .Value property.
 

poitbot

New Member
Joined
Nov 22, 2006
Messages
34
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.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920

ADVERTISEMENT

Try one of these changes
Code:
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

Code:
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

New Member
Joined
Nov 22, 2006
Messages
34
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
Is that an exact copy of the line that errored? What was n when it errored?
 

poitbot

New Member
Joined
Nov 22, 2006
Messages
34
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,518
Messages
5,602,138
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top