Is there a way to vlookup and keep formulas?

March 28, 2012 at 02:33:52
Specs: Windows XP
Hi all,
I have been recently trying to find a way, through VBA, to VLOOKUP and keep formulas. I have quite a lot of data to check and so far haven't been able to work a way out.

I found some VBA code to VLOOKUP and keep formats but could not find anything for formulas.

Anyone would be so kind to help me out?
Thanks a lot.

See More: Is there a way to vlookup and keep formulas?

Report •

March 28, 2012 at 06:01:34
You'll have to explain a bit more.

Why are you "losing" formulas when you are simply checking the data?


Report •

March 28, 2012 at 06:15:44
I have two big tables on two sheets. A unique identifier is available for each line. I want to copy values and formulas for all matching codes from one sheet to the other.

On sheet 1:

Code 630-20-6
Amount1 4,80E+03
Amount2 1,32E+03

On sheet 2:

Code 630-20-6
Value1 100
Value2 200
Amount1 ?
Amount2 ?

I would like to copy amount 1 and amount 2 from sheet 1 to sheet 2, but I would need not only the value but also the associated formula.

I have a table of 40000 items to loop through.

I thought I could use a VBA code to VLOOKUP the amount1 and amount2 and paste the relative formula in the right cell on sheet 2.

Report •

March 28, 2012 at 07:22:25
I'm sorry but my Macro skills are just above nill.

Hopefully someone will supply a solution.

But I did find this, if it is of any help:


Report •

Related Solutions

March 28, 2012 at 08:59:33
I'm still not clear as to where the formulas are coming from, but in VBA, you can "copy" a formula as follows:

Sheets(2).Range("A1").Formula = Sheets(1).Range("A1").Formula

Since you didn't post the code you are trying to use, it's hard to tell you exactly how to use that suggested syntax.

In general, I guess you would need to determine which Row the VLOOKUP returns and use that.

It might be easier to use .Find and .Offset instead of VLOOKUP since .Find can return a Range address, including the Row and Column.

Something like:

Set c = .Find(etc...)
Sheets(2).Range("A" & c.Row).Formula = Sheets(1).Range("A" & c.Row).Formula

If you are going to post any code, please click on the following line and read the instructions found via that link first.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

March 29, 2012 at 03:05:53
I changed something in the script to copy and paste formats with VLOOKUP that was available on another page.
It gets stuck after few cells.

This is the code:

Private Sub Worksheet_Calculate()
Dim Vpos As Integer, FmlArr() As String, TgtRng As Range, cell As Range, c
Dim ShArr() As String, Sh As Worksheet, Shpos As Integer

For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
If cell.HasFormula = True Then
Vpos = InStr(1, cell.Formula, "VLOOKUP", vbTextCompare)

If Vpos > 0 Then
FmlArr = Split(Mid(cell.Formula, Vpos, Len(cell.Formula) - Vpos + 1), ",", -1, vbTextCompare)
Shpos = InStr(1, FmlArr(1), "!", vbTextCompare)
If Shpos > 0 Then
ShArr = Split(FmlArr(1), "!", -1, vbTextCompare)
Set Sh = Worksheets(ShArr(0))
Set TgtRng = Sh.Range(ShArr(1))
Set Sh = ActiveSheet
Set TgtRng = Sh.Range(FmlArr(1))
End If

Set TgtRng = Intersect(TgtRng, Sh.Columns(TgtRng(, Val(FmlArr(2))).Column))

With TgtRng
    On Error Resume Next
    Set c = .Find(cell, LookIn:=xlValues)
        If Not c Is Nothing Then
        cell.PasteSpecial Paste:=xlPasteFormulas
        Application.CutCopyMode = False
        End If
    Error 0
End With
End If
End If
Next cell
End Sub 

Report •

Ask Question