COPY PASTE SPECIAL DATA AS VALUES

[Mudassar.freedi]

Dear all,


I have more 500 rows, 12 column for 12 months, 5 XL sheets for different locations.


Each Row contains different farmula like:-

- vlookup

- subtotal &

- sum


on end of each month, the rows contain Vlookup func. need to be copy, pasted as values. how i can select & give command on multiple selected rows in one go and remaining farmulas will be as it is.


thanks for urgent response.


Hafiz Mudassar

 

[NARAYANK991]

Hi Hafiz ,


Do you mean that the VLOOKUP formulae will be in different cells all over the worksheet , and only those need to be copied and pasted as values ?


Where should they be pasted , in their own cells ?


Narayan

 

[Mudassar.freedi]

Hi Dear,


Yes. you got my point.


Rgds

 

[NARAYANK991]

Hi Hafiz ,


Try this out and see if it is OK.


Public Sub Check_Formula()

Application.ScreenUpdating = False

Lookup_Text = "=VLOOKUP("

Start_Range = "A1" ' Change this as per your requirement

End_Range = "AG55" ' Change this as per your requirement

Range(Start_Range, End_Range).Select

For Each cell In Selection

cell_formula = cell.Formula

If cell_formula Like Lookup_Text & "*" Then

cell.Value = Evaluate(cell.Formula)

End If

Next

Application.ScreenUpdating = True

End Sub


Narayan


P.S. : This will ignore any inner VLOOKUP formulae e.g. if they are within an IF statement.

 

[Mudassar.freedi]

THAT'S WHY I could not succeeded due to inner vlookup. Sorry for not informing you for this 'if logic'.


actually, my vlookup farmula on each cell is:-


=IF($B19=$B20,0,IFERROR(VLOOKUP($B19,Grouped_Data,8,),0))


now please guide me.

 

[NARAYANK991]

Hi Hafiz ,


Try this.


Public Sub Check_Formula()

Application.ScreenUpdating = False

Lookup_Text = "VLOOKUP("

Start_Range = "A1"

End_Range = "AG55"

Range(Start_Range, End_Range).Select

For Each cell In Selection

cell_formula = cell.Formula

If (InStr(1, cell_formula, Lookup_Text, vbTextCompare) > 1) Then

cell.Value = Evaluate(cell_Formula)

End If

Next

Application.ScreenUpdating = True

End Sub


Narayan

 

[Mudassar.freedi]

it's worked.


thanks for your efforts.


can you plz explain the logic, you used. i have not much idea regarding vb.


thnks again.

 

[NARAYANK991]

Hi Hafiz ,


The main components of the procedure are :


1. The .Formula property , which Returns a value that represents the object's formula.


2. The INSTR function which Returns a number specifying the position of the first occurrence of one string within another. We lookup the Lookup_Text ( "VLOOKUP(" ) in the cell formula. If the text is found , then the position will be greater than 1. We have excluded the "=" component of an Excel formula , since when an Excel function like VLOOKUP is used in the middle of a formula , the "=" component is not used.


3. The Evaluate function , which evaluates a formula given as a string.


Narayan