CODEWITHSUNDEEP
vbaexcel
Don Desrosiers
Don Desrosiers

Reputation: 143

VBA vlookup, cut & paste

I'm starting to think that I'm a mile off with this code. I want to find the value in "r15" and find a match in the lookup table. Then, in that row and 2 columns to the right, I want to paste the value from e15. Here's what I have:

Sub CopyX()
    MsgBox "FlightPlan for Profits PRO never actually DELETES an employee, It just marks an employee as inactive.  If the Employee were actually deleted from the database, archival records would not include a deleted employee (of course) and would therefore become inaccurate", vbInformation, "FlightPlan for Profits PRO"
    ActiveSheet.Range("e15").Select
    Selection.Copy
    vlookupVBA = Application.WorksheetFunction.vlookup(Range("r15"), Range("c24:c274"), 1, False)
        Range.Offset(0, 2).Select
    ActiveCell.PasteSpecial Paste:=xlPasteValues
End Sub

Upvotes: 0

Views: 748

Answers (2)

Davesexcel
Davesexcel

Reputation: 6984

Find will work for this as well.

Sub Button1_Click()
    Dim msg As String
    Dim lstRw As Long, rng As Range, c As Range

    lstRw = Cells(Rows.Count, "C").End(xlUp).Row
    Set rng = Range("C24:C" & lstRw)

    Set c = rng.Find(what:=Range("R15").Value, lookat:=xlWhole)

    msg = "FlightPlan for Profits PRO never actually DELETES an employee, " & vbNewLine
    msg = msg & "It just marks an employee as inactive. " & vbNewLine
    msg = msg & "If the Employee were actually deleted from the database, " & vbNewLine
    msg = msg & "archival records would not include a deleted employee (of course) " & vbNewLine
    msg = msg & " and would therefore become inaccurate"
    MsgBox msg, vbInformation, "FlightPlan for Profits PRO"

    If Not c Is Nothing Then
        Cells(c.Row, c.Column + 2).Value = Range("E15").Value
    Else: MsgBox "Not Found"
        Exit Sub
    End If


End Sub

Upvotes: 0

user4039065
user4039065

Reputation:

You were using the VLOOKUP function incorrectly. In fact, a MATCH function would return the row number which was what you seemed to be looking for.

Sub CopyX()

    MsgBox "FlightPlan for Profits PRO never actually DELETES an employee." & Chr(10) & _
           "It just marks an employee as inactive. " & _
           "If the Employee were actually deleted from the database, archival records " & _
           "would not include a deleted employee (of course) and would therefore become inaccurate", _
           vbInformation, "FlightPlan for Profits PRO"

    With Worksheets("Sheet1")
        'first check if anything is there
        If CBool(Application.CountIf(.Range("c24:c274"), .Range("r15").Value)) Then
            'there is a row number to find; get it and set the value
            .Cells(Application.Match(.Range("r15").Value, .Range("c24:c274"), 0) + 23, "E") = _
                .Range("e15").Value
        End If
    End With

End Sub

With MATCH returning the position within C24:C274, 23 is added and that provides the correct row_number parameter for the Range.Cells property to receive the value from E15.

I've added a line feed and broken your msgbox text into a couple of lines to keep it visible without right-scrolling.

Upvotes: 1

Related Questions