Paste special values of a formula to a cell but NOT from a defined source cell

[Hai]

Right, this sounds very odd but my problem is as follows:

I have a script (thanks to MorganO -Owen from http://www.mrexcel.com/forum/showthread.php?t=324443) to build formulas that links to different cells of different workbooks and insert into destination cells of a consolidation workbook

So my destination cells will have formulas like:

='E:\My Work\Test\Data\[CCC.xlsx]Input'!$S$31

Everything works fine. But I don't want the destination cells to display the full formula, but the actual Value only

I thought of a way to build a script that first builds the formula to one cell then Copy Paste Special value of that Cell to my desired Cell.

But when the code runs The cursor moves back and forth all the time...

I hope you have a smarter way of solving my problem. Thanks so much!

 

[DonkeyOte]

Assuming linked books are not being opened in the code building the formulae the only way you could show values as opposed to formulae would be to first return the formulae and then copy paste the results over the formulas as you say... unfortunately as with (standard) INDIRECT formula in native XL you can't use EVALUATE in VBA in closed files.

Without seeing your code it's difficult to comment but you may be able to force a recalc of the sheet each time you "book" a formula (inefficient but good way of highlighting those formulae being pasted via VBA) ... if every formula in the sheet (or in a given range) can be overwritten with values it *should* be relatively straightforward to achieve utilising specialcells.xlcelltypeformulas etc...

 

[Hai]

Here is the code:

Function Extract_data()
Dim myPath As String, myName, myText As String
Dim RowToWriteTo, ColumntoWriteto As Integer

myPath = Range("Help!A1").Value
myName = Dir(myPath)

If myPath = "" Then MsgBox ("Please select the data folder in Help!")

With ThisWorkbook.ActiveSheet

' Clear all the current non-blank cells
.Range(Cells(7, Columns.Count), Cells(Rows.Count, 1)).Value = ""
RowToWriteTo = 7
ColumntoWriteto = 2

' Find all .xls files and populate the worksheet
Do While myName <> ""
'find the extention of the file
fileextention = Right(myName, Len(myName) - InStr(1, myName, "."))
If fileextention = "xlsx" Or fileextention = "xlsx" Then
'.Cells(RowToWriteTo, 1).Clear
.Cells(RowToWriteTo, 1).Value = myName
ColumntoWriteto = 2

Do Until .Cells(6, ColumntoWriteto).Value = ""
'.Cells(RowToWriteTo, ColumntoWriteto).Clear

myText = "='" & myPath & "[" & myName & "]Input'!" & Cells(6, ColumntoWriteto).Value

.Cells(RowToWriteTo, ColumntoWriteto).Formula = myText
ColumntoWriteto = ColumntoWriteto + 1
Loop
RowToWriteTo = RowToWriteTo + 1
End If
myName = Dir
Loop
End With
End Function

My current way of converting formula to values is to replace

.Cells(RowToWriteTo, ColumntoWriteto).Formula = myText

with

.Cells(RowToWriteTo, ColumntoWriteto).Formula = myText
.Cells(RowToWriteTo, ColumntoWriteto).Copy
.Cells(RowToWriteTo, ColumntoWriteto).PasteSpecial xlPasteValues

but with XL7 - using Table Design Tool (that automatically expands table format with additional rows) the formula is copied to all the cells in the later rows. And that's really a pain! So If I have any additional rows that are still in the range of a table, the formulas are filled automatically.

So I use:

.Cells(3,3).Formula = myText
.Cells(3,3).Copy
.Cells(RowToWriteTo, ColumntoWriteto).PasteSpecial xlPasteValues

and that makes Excel dances around from Cells(3,3) to the destination cells...

Please help , thanks a lot lasw10!

 

[Hai]

I'm sure you'll get it, but FYI, row 6 contains the position of the Cells that are referred to in the workbooks that I need to get data from, for example B8

 

[DonkeyOte]

Yep, figured... assuming you KNOW anything in A7 and beyond is a formula that needs to be overwritten with value you could try using something like

Dim rng As Range
Set rng = Range(Cells(7, 1), Cells(7, 1).SpecialCells(xlCellTypeFormulas))
With rng
    .Formula = .Value
End With

This approach would require you leave the routine as before -- ie let it paste in all the formulas (don't try and change them to values yet), then force a calculation of the sheet (just in case) then put in the above... it should convert the formulas in A from row 7 down to just values)

 

[Hai]

lasw10, yeap your code would convert formula to values of the range. But since I use XL7 with Table Design tool - that auto fill formulas of the above cells down to the very last row within that table.

Assumming I have the table of 10 rows, but I have only 5 files to get data from. So the first 5 rows will have formulas inputted. And XL7 "cleverly" fills the last 5 rows with the formulas of the first 5 rows ... that's a pain!

So I need to convert to values before putting the values to the cells.

Any idea? Should I send a sample?

 

[DonkeyOte]

EDIT:

Use this... above would also clear A1:A6 formulas, apologies:

Dim rng, rng2 As Range
Set rng = Range(Cells(7, 1), Cells(7, 1).SpecialCells(xlLastCell))
With rng
    Set rng2 = rng.SpecialCells(xlCellTypeFormulas)
    With rng2
        .Formula = .Value
    End With
End With

EDIT: posted whilst you posted above... let me have a look at 07 -- can you turn off the 07 functionality for ex ?
I'm too poor to own 07 so I can't test ...

 

[DonkeyOte]

OK maybe I'm misinterpreting the problem... if you're worried about the screen flicker as you copy and paste value from C3 to next cell A7 down (where C3 is result of formula being pasted from VBA and evaluated in native XL) -- turn off ScreenUpdating in your code -- this is a recommendation anyway as it will enhance the performance of your routine as XL doesn't waste resources/time redrawing the screen all the time...

so

Sub....
Application.ScreenUpdating = False
....
Application.ScreenUpdating = True
End Sub

As was pointed out to me here not so long ago - you don't technically need to reset ScreenUpating to true as XL will default to True on completion of routine... however most people would code this out of habit (and best practice).

I hope that helps.

 

[Hai]

Tks a lot for the Screen Update thing. That should really help! I'll try again and see.

 

[Hai]

EDIT:

EDIT: posted whilst you posted above... let me have a look at 07 -- can you turn off the 07 functionality for ex ?
I'm too poor to own 07 so I can't test ...

I'm just so lucky (or not) to have XL 07. I don't know how to turn that off.

But strange enough if the formula is just ='E:\.... then it's fine, XL07 Table Design tool would not fill up the formulas for the remaining rows.

However the auto fill ocurs for the formula that combines ='E:\.... & " " & 'E:\... - as I combine 2 cells of Family Name with First Name into 1 cell.

like:

.Cells(RowToWriteTo, 1).Value = "='" & myPath & "[" & myName & "]Input'!" & "A6" & "&" & Chr$(34) & Chr$(32) & Chr$(34) & "&'" & myPath & "[" & myName & "]Input'!" & "A7"

so all the cells of that columns takes just 1 formula to give identical results, although there are different source files workbooks.

So there's something about that... Anyway, many thanks if you can think of an alternative to combine values of cells in a closed workbook into one cells of an active workbook with a formula!