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.

You'll have to explain a bit more. Why are you "losing" formulas when you are simply checking the data?

MIKE

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. Example:

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.

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:

http://spreadsheetpage.com/index.ph...

MIKE

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").FormulaSince 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).FormulaIf 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.

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)) Else 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 c.Copy cell.PasteSpecial Paste:=xlPasteFormulas Application.CutCopyMode = False End If Error 0 End With End If End If Next cell End Sub

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History