CODEWITHSUNDEEP
excelvba
Ravi Vasi
Ravi Vasi

Reputation: 51

Selecting Data between sheets to Write a Vlookup function

I Have a data in sheet(Sales Data) form Cells B7:B207 and I'm trying to write a vba code for using data to generate Vlookup function in sheet(Salesmen Info). But I failed to generate the VBA Code. Please advise how would I generate the VLookup in (Salesmen Info).

Upvotes: 0

Views: 664

Answers (2)

Rich Tier
Rich Tier

Reputation: 9441

I like applciation.vlookup:

arr = application.vlookup(A1,"Sales Data"!$B$7:$B$207,1,false)

then, this should work (typing without excel handy so it might not!):

for i = 0 to ubound(arr,0)
    for j = 0 to ubound(arr,1)
        debug.print arr(i,j)
    next
next

(Press Ctrl+J to see the immediate window and see your output)

or maybe you want it in a sheet?

dim rng as range
set rng = range("A1")

for i = 0 to ubound(arr,0)
    for j = 0 to ubound(arr,1)
        rng.offset(i,j).value = arr(i,j)
    next
next

Upvotes: 0

Raystafarian
Raystafarian

Reputation: 3022

you could just vlookup outside of VBA with a standard function

=vlookup(A1,"Sales Data"!$B$7:$B$207,1,false)

If your VBA is failing, make sure you reference the sheet "sales data" in quotation marks whenever you call it. What's your current VBA code?

Additionally, why are you trying to vlookup a single column (B). You need your range to be at least 2 columns so it can find the data in B and return the data from C

Upvotes: 1

Related Questions