#### poitbot

##### New Member

- Joined
- Nov 22, 2006

- Messages
- 34

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