CODEWITHSUNDEEP
excelvbaformula
Max078
Max078

Reputation: 37

Add formula to copied range

I'm trying to insert Vlookup Formula in my invoice VBA. The copied range go to the column "A" and I want to insert my formula into the Column"B". Here is where I am right now.

Here is a link to my file: https://drive.google.com/file/d/0By_oZy042nKWSGRNcU14T3g4SXc/edit?usp=sharing

Private Sub CommandButton1_Click()
    Dim wsInvoice As Worksheet, wsRange As Worksheet

    With ThisWorkbook
        Set wsInvoice = .Worksheets("Invoice")
        Set wsRange = .Worksheets("Range")
        nr = wsInvoice.Cells(Rows.Count, 1).End(xlUp).Row + 1
    End With

    Select Case Me.ComboBox1
        Case "Paper"
            wsRange.Range("Paper").Copy wsInvoice.Cells(nr, 1)
        Case "Pen"
            wsRange.Range("Pen").Copy wsInvoice.Cells(nr, 1)
        Case "Sticker"
            wsRange.Range("Sticker").Copy wsInvoice.Cells(nr, 1)
    End Select
End Sub

Upvotes: 0

Views: 121

Answers (3)

user1934049
user1934049

Reputation: 7

excelWbk.Sheets(Sheet1).Select
lrow = ActiveSheet.Range("A65536").End(xlUp).Row

excelWbk.Sheets(Sheet11).Select
lrow_sheet2 = ActiveSheet.Range("A65536").End(xlUp).Row


excelWbk.Sheets(Sheet1).Select
With excelWbk.ActiveSheet
For i = 2 To lrow

.Cells(i, 3).Formula = "=VLOOKUP(A" & i & ",Sheet11!$A$1:$B$" & lrow_sheet2 & ",2,0)"

        Next i
End With


With excelWbk.ActiveSheet
    .Range("C" & lrow).Select
Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  End With

Upvotes: 0

PermaNoob
PermaNoob

Reputation: 869

Right now you have this:

    Select Case Me.ComboBox1
        Case "Paper"
            wsRange.Range("Paper").Copy wsInvoice.Cells(nr, 1)
        Case "Pen"
            wsRange.Range("Pen").Copy wsInvoice.Cells(nr, 1)
        Case "Sticker"
            wsRange.Range("Sticker").Copy wsInvoice.Cells(nr, 1)
    End Select

To add a Vlookup to it just add worksheetfunction.vlookup like this:

Select Case Me.ComboBox1
    Case "Paper"
        wsRange.Range("Paper").Copy wsInvoice.Cells(nr, 1)
        wsInvoice.Cells(nr, 2) = Application.WorksheetFunction.VLookup(Cells(nr, 1), wsPrice.Range("A:B"), 2, 0)
    Case "Pen"
        wsRange.Range("Pen").Copy wsInvoice.Cells(nr, 1)
        wsInvoice.Cells(nr, 2) = Application.WorksheetFunction.VLookup(Cells(nr, 1), wsPrice.Range("A:B"), 2, 0)
    Case "Sticker"
        wsRange.Range("Sticker").Copy wsInvoice.Cells(nr, 1)
        wsInvoice.Cells(nr, 2) = Application.WorksheetFunction.VLookup(Cells(nr, 1), wsPrice.Range("A:B"), 2, 0)
End Select

I named the arrays, "paperprice", "penprice",etc... and assumed that the prices were in the column directly to the right of the item. It uses the same formula as the regular Vlookup, so it shouldn't be too hard for you to modify to fit to your needs.

Also, add this line where you are setting all the worksheets:

Set wsPrice = .worksheet("Price")

EDIT: Thanks for linking to a copy of your workbook.

Try this:

Private Sub CommandButton1_Click()
Dim wsInvoice As Worksheet, wsRange As Worksheet, wsPrice As Worksheet
Dim nr As Integer, lr As Integer
With ThisWorkbook
    Set wsInvoice = .Worksheets("Invoice")
    Set wsRange = .Worksheets("Range")
    Set wsPrice = .Worksheets("Price")
End With
nr = wsInvoice.Cells(Rows.Count, 1).End(xlUp).Row + 1
Select Case Me.ComboBox1
    Case "Paper"
        wsRange.Range("Paper").Copy wsInvoice.Cells(nr, 1)
        lr = wsInvoice.Cells(Rows.Count, 1).End(xlUp).Row
        For i = nr To lr
            wsInvoice.Cells(i, 2) = Application.VLookup(Cells(i, 1), wsPrice.Range("A:B"), 2, 0)
        Next i
    Case "Pen"
        wsRange.Range("B2:B100").Copy wsInvoice.Cells(nr, 1)
        lr = wsInvoice.Cells(Rows.Count, 1).End(xlUp).Row
        For i = nr To lr
            wsInvoice.Cells(i, 2) = Application.VLookup(Cells(i, 1), wsPrice.Range("A:B"), 2, 0)
        Next i
    Case "Sticker"
        wsRange.Range("C2:c100").Copy wsInvoice.Cells(nr, 1)
        lr = wsInvoice.Cells(Rows.Count, 1).End(xlUp).Row
        For i = nr To lr
            wsInvoice.Cells(i, 2) = Application.VLookup(Cells(i, 1), wsPrice.Range("A:B"), 2, 0)
        Next i
End Select
End Sub

NOTE: This will put a #N/A error in the cell if you don't have it in the Price sheet.

Upvotes: 1

ttaaoossuu
ttaaoossuu

Reputation: 7884

Let's say you've just copied some value into a cell MyCell. Then if you want to put a formula into the next cell to the right you should use this code:

MyCell.Offset(0, 1).Formula = "=VLOOKUP(" & MyCell.Address & ",$C:$D,2,false)"

Upvotes: 0

Related Questions