Vlookup Formula in Excel VBA

0 votes

I have an Excel sheet with locked cells. Some cells are unlocked to input values. There are many values to input therefore I'm trying to write a Macro that will: select the unlocked cells in the Active Sheet and fill the selected cells with a Vlookup formula that looks up the input values based on the first column and relevant rows in the same sheet from a table on a separate sheet. I have tried the below:

Sub SelectUnlockedCells()
'Update 20130830
Dim WorkRng As Range
Dim OutRng As Range
Dim Rng As Range
On Error Resume Next
Set WorkRng = Application.ActiveSheet.UsedRange
Application.ScreenUpdating = False
For Each Rng In WorkRng
    If Rng.Locked = False Then
        If OutRng.Count = 0 Then
            Set OutRng = Rng
        Else
            Set OutRng = Union(OutRng, Rng)
        End If
    End If
Next
If OutRng.Count > 0 Then OutRng = Application.WorksheetFunction.VLookup("A" & ActiveRow.Value, Worksheets(2).Columns("A:C").Select, Worksheets(2).Columns(3).Select, False)
Application.ScreenUpdating = True
End Sub

I know my problem occurs in the last 4 lines in the Vlookup Worksheet Function, because if I say:

If OutRng.Count > 0 Then OutRng = 1 + 1

The unlocked input cells are imputed correctly as 2. Therefore I suspect my Vlookup object selection is not correct.

Any help would be great, thanks!

Apr 6 in Database by Edureka
• 9,320 points
24 views

1 answer to this question.

0 votes

The VLOOKUP function can be used in VBA code as follows:
Application.
student id = 11004.... WorksheetFunction.vlookup(lookup value, table array, col index num, range lookup)
Set myrange to Range("B4:D8") and application to Marks.
VLookup(student id, myrange, 3, False) WorksheetFunction.VLookup(student id, myrange, 3, False)

VLOOKUP in Excel VBA | How to Write VLOOKUP Code in VBA?

answered Apr 11 by Edureka
• 8,820 points

Related Questions In Database

0 votes
1 answer
0 votes
1 answer

Convert numbers to words in Excel (VBA)

In the cell where you wish to ...READ MORE

answered Mar 25 in Database by Edureka
• 8,820 points
36 views
0 votes
1 answer

How to Calculate DATEDIF formula in Excel?

Simply divide one date by the other. ...READ MORE

answered Mar 25 in Database by Edureka
• 8,820 points
38 views
0 votes
1 answer

Insert a value to a cell in excel using formula in another cell

Select the cell where the formula should ...READ MORE

answered Mar 25 in Database by Edureka
• 8,820 points
49 views
0 votes
1 answer

Remove time from date field in Excel formula

Use the Find And Replace function to ...READ MORE

answered Mar 30 in Database by Edureka
• 8,820 points
19 views
0 votes
1 answer

Excel formula to remove space between words in a cell

There are three fast techniques to get ...READ MORE

answered Mar 30 in Database by Edureka
• 8,820 points
29 views
0 votes
0 answers

How to loop in excel without VBA or macros?

Is it possible to iterate (loop) a ...READ MORE

Mar 30 in Database by Edureka
• 9,320 points
55 views
0 votes
1 answer

Prevent cell numbers from incrementing in a formula in Excel

In Excel, you can use a feature ...READ MORE

answered Mar 15 in Database by Edureka
• 8,820 points
27 views
0 votes
1 answer

[Excel][VBA] How to draw a line in a graph?

Sub MakeChart() Dim x(20) ...READ MORE

answered Mar 24 in Database by Edureka
• 8,820 points
26 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP