VBA how to set value of cell equal to next cell in column on another sheet

Question

I have a value in A38 that I would like a button that when clicked will do the equavalent of an Hlookup on my sheet 'Item Database' and return the next value in the column after the value in A38.

Basically a "Next Item" button.

I'm not sure how to tell it to take the value of the cell, do the look up and insert the value of the look up (for the next value down in the column).

Currently i have it pointed at A36 which was a way for me to test the hlookup. when I set it to A38 I dont get an error, but it the cell is blank(after deleting whatever value was in there).

Any advise is appreciated!

Private Sub CommandButton1_Click()
With Range("A38")
.Value = "=HLOOKUP(A36,'Item Database'!A2:A100000, 2,FALSE)"
.Value = .Value
With Range("B38")
.Value = "=VLOOKUP(A38,Item_ID,5,FALSE)"
.Value = .Value
End With
End With
End Sub

Peter Albert · Accepted Answer · 2013-11-21 22:02:23Z

2

Try this code

Private Sub CommandButton1_Click()
    Range("A38").Value = [HLOOKUP(A38,'Item Database'!A2:A100000,2,FALSE)]
    Range("B38").Value = [VLOOKUP(A38,Item_ID,5,FALSE)]
End Sub

answered Nov 21, 2013 at 22:02

Peter Albert

17.6k6 gold badges69 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Astr0zombi3s Over a year ago

Perfect, much better way!

Peter Albert Over a year ago

@SiddharthRout: I'm sure, you'd have taken the even shorter approach [A38] = [HLOOKUP(A38,'Item Database'!A:A,2,0)]! ;-)

Collectives™ on Stack Overflow

VBA how to set value of cell equal to next cell in column on another sheet

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related