Need VBA Vlookup help

Mahantesh · Sep 14, 2016

Hi All,

I have file (attached here), i would like to Vlookup Sheet("Rawdata) into Sheet("Data").

I am getting an error for #N/A,, whici i would like to replace them with 0's.

If item is not found replace with zero. I have attched is the file.

A,B, C, D blocks from RAWATA sheet, A B C D(Columns B, C, D, E) headers in the "data" sheet.

Please help!

Regards,
Mahantesh

Chihiro · Sep 14, 2016

Didn't Yessarkalil and I help you on pretty much the same question?
http://chandoo.org/forum/threads/how-to-show-value-zero-if-vba-vlookup-shows-n-a.31175/

You never did reply if the issue was resolved or not in original thread. Nor did you upload file with changed structure there.

Also read link, please.
http://chandoo.org/forum/threads/new-users-please-read.294/

Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out.

Hui · Sep 15, 2016

Some of the rules of these Forums

Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here.

Please respond to peoples responses. They are donating there time to help you.

Please refer: http://forum.chandoo.org/link-forums/new-users-please-read.17/

Mahantesh · Sep 15, 2016

Hui said:
Some of the rules of these Forums

Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here.

Please respond to peoples responses. They are donating there time to help you.

Please refer: http://forum.chandoo.org/link-forums/new-users-please-read.17/

Hello mate,
I am so sorry for not responding. I do agree, i should have replied.

Thank you for all replies.
I am waiting for the solution which would work in the attached file.

Thank you for your understanding! Please dont mind. :-(

Regards,
Mahantesh

Mahantesh · Sep 15, 2016

Chihiro said:
Didn't Yessarkalil and I help you on pretty much the same question?
http://chandoo.org/forum/threads/how-to-show-value-zero-if-vba-vlookup-shows-n-a.31175/

You never did reply if the issue was resolved or not in original thread. Nor did you upload file with changed structure there.

Also read link, please.
http://chandoo.org/forum/threads/new-users-please-read.294/

so sorry, i will not upload any question with these terms again in future.

Thank you for highlighting.

Chihiro · Sep 15, 2016

Here you go. Note that I removed one blank column from RawData as there was extra column just between C & D data set.

Mahantesh · Sep 15, 2016

Mate,

First 3 columns ran fine. But last column i am getting the same old error.
I am not sure why this error again as i did not change anything?

But your code ran fine. and mine not.

:-(

Can u help?

Regards,
Mahantesh

Chihiro · Sep 15, 2016

Upload the workbook with your code. And I'll take a look at it.

Mahantesh · Sep 16, 2016

Chihiro said:
Upload the workbook with your code. And I'll take a look at it.

Sorry i did not time to work on it. I will upload today.

Thank you for your patience!

Mahantesh · Sep 16, 2016

Chihiro said:
Upload the workbook with your code. And I'll take a look at it.

----------------------
Hello mate,

this worked perfectly fine!
val = Application.VLookup(Sheets("Final Data").Range("A" & i).Value, Sheets("Rawdata").Range("K3:L100").Value, 2, 0)
If IsError(val) Then
Sheets("Final Data").Range("E" & i).Value = 0
Else
Sheets("Final Data").Range("E" & i).Value = val
End If

Check at your end! let me know.
Regards,
Mahantesh

Chihiro · Sep 16, 2016

Yes, that would work as it's not using WorksheetFunction.

If you use WorksheetFunction, when it evaluates to error. It will not work.

Do note that my code was set to look for all range (for set A to D) and loop. Not just fixed range. See code modified without WorksheetFunction.

Code:

Sub Test()
Dim lastrow As Long
Dim calc As Variant
Dim dWs As Worksheet, rWs As Worksheet
Set dWs = Worksheets("Data")
Set rWs = Worksheets("RawData")

lastrow = dWs.Cells(Rows.Count, 1).End(xlUp).Row
For x = 0 To 3
    For i = 2 To lastrow
        calc = Application.VLookup(dWs.Range("A" & i).Value, _
              rWs.Range("A1:B48").Offset(, x * 3).Value, 2, 0)
  
    If IsError(calc) Then
        dWs.Range("B" & i).Offset(, x).Value = 0
    Else
        dWs.Range("B" & i).Offset(, x).Value = calc
    End If
    Next
Next

End Sub

Mahantesh · Sep 17, 2016

Chihiro said:
Yes, that would work as it's not using WorksheetFunction.

If you use WorksheetFunction, when it evaluates to error. It will not work.

Do note that my code was set to look for all range (for set A to D) and loop. Not just fixed range. See code modified without WorksheetFunction.

Code:

Sub Test() Dim lastrow As Long Dim calc As Variant Dim dWs As Worksheet, rWs As Worksheet Set dWs = Worksheets("Data") Set rWs = Worksheets("RawData") lastrow = dWs.Cells(Rows.Count, 1).End(xlUp).Row For x = 0 To 3 For i = 2 To lastrow calc = Application.VLookup(dWs.Range("A" & i).Value, _ rWs.Range("A1:B48").Offset(, x * 3).Value, 2, 0) If IsError(calc) Then dWs.Range("B" & i).Offset(, x).Value = 0 Else dWs.Range("B" & i).Offset(, x).Value = calc End If Next Next End Sub

Hi chihiro,

You are right.

Your code is universal that works for any range! Thank you very much.
I am using your code and i will come to you if i face any issues!

I am newbie to VBA so struggling hard to make it!

Regards,
Mahantesh

Marc L · Sep 17, 2016

Hi !

• First my review for Chihiro :
if you wanna use object variables, better is to free them
before leaving procedure as well to declare i & x as numeric :

Code:

Sub Test1()
    Dim dWs As Worksheet, lastrow&, i&, x&
    Set dWs = Worksheets("Data")
    lastrow = dWs.Cells(Rows.Count, 1).End(xlUp).Row
For x = 0 To 3
    For i = 2 To lastrow
        Calc = Application.VLookup(dWs.Range("A" & i).Value, _
               Range("Rawdata!A4:B47").Offset(, x * 3).Value, 2, 0)
        dWs.Range("B" & i).Offset(, x).Value = IIf(IsError(Calc), 0, Calc)
    Next
Next
    Set dWs = Nothing
End Sub

Or without any object variable (case of static address) :

Code:

Sub Test2()
    Dim i&, x&
For x = 0 To 3
    For i = 2 To Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
        Calc = Application.VLookup(Range("Data!A" & i).Value, _
               Range("Rawdata!A4:B47").Offset(, x * 3).Value, 2, 0)
        Range("Data!B" & i).Offset(, x).Value = IIf(IsError(Calc), 0, Calc)
    Next
Next
End Sub

Or using With statement instead of an object variable
(as declaring an object consumes time) :

Code:

Sub Demo0()
With Worksheets("Rawdata")
    AD$ = .Range("A4", .Cells(.Rows.Count, 1).End(xlUp)(-1)).Resize(, 2).Address(External:=True)
End With
    Application.ScreenUpdating = False
With Worksheets("Data")
    For R& = 2 To .UsedRange.Rows.Count
        For C& = 0 To 3
            V = Application.VLookup(.Cells(R, 1).Value, Range(AD).Offset(, C * 3).Value, 2, 0)
            If IsNumeric(V) Then .Cells(R, 2 + C).Value = V
        Next
    Next
End With
    Application.ScreenUpdating = True
End Sub

• Second point for everyone :
previous codes using nested loops may be a bit difficult
to write by VBA beginners …

So respecting TEBV rule (1), a VBA beginner (as well any advanced)
must think before for an Excel way :
the expected result can be reached using directly an Excel formula ?

And here the answer is Y E S without a single VBA codeline ‼
Formula in Data!B2 cell :

Code:

=IFERROR(VLOOKUP($A2,OFFSET(Rawdata!$A$4:$B$47,0,(COLUMN()-2)*3),2,FALSE),0)

Copy it right and down, that's all folks ‼

If really a VBA code is needed, for example to have only values
without keeping any formula in cells, the formula way
makes the code easier and faster, without any loop !

Code:

Sub Demo1()
With Worksheets(2)
    AD$ = .Range("A4", .Cells(.Rows.Count, 1).End(xlUp)(-1)).Resize(, 2).Address(External:=True)
End With
With Worksheets(1).Cells(1).CurrentRegion
    With .Range("B2", .Cells(.Count))
         .Formula = "=IFERROR(VLOOKUP($A2,OFFSET(" & AD & ",0,(COLUMN()-2)*3),2,FALSE),0)"
         .Formula = .Value
    End With
End With
End Sub

(1) Think Excel Before VBA !

Chihiro · Sep 19, 2016

Thanks for the tips I normally don't use VLookup in my coding, so this is helpful. As for formula, I tend to avoid volatile function whenever possible.

In OP's case, I'd use PowerQuery to merge tables (if given choice and PQ is available).

Marc L · Sep 19, 2016

Formula is volatile but my Demo1 code crushes it by values !

Yes PowerQuery since 2013 version (+ SQL ?) if I'm right
but I don't know for a beginner if it's easier than a formula …

Chihiro · Sep 19, 2016

Yep, it's quite easy to use and for the most part only requires getting used to UI.

For the most part, you don't need to get into advanced M etc unless you need dynamic parameters to be set or calculation steps which is outside of normal Joins and Unions.

Lately it's been my favourite feature in Excel. I've used it to produce dashboard at work (PowerQuery, DataModel, PivotTable and small use of VBA to update charts and Conditional Formats).

By not loading table to Excel, it reduced size and processing time significantly and it pulls relevant data directly from my Reporting Server to report on most recent data set automatically.

Mahantesh · Sep 20, 2016

Chihiro said:
Yep, it's quite easy to use and for the most part only requires getting used to UI.

Hi Chihiro,

Thank you for all your help dude. It worked for me and have learnt a lot from you!

i have a doubt on how to surpass Data link Properties in opening last modified workbook from a folder.
Below is my code which worked perfectly until which started giving me error which is attached in this post.

this is my code:
----------------------
mydir = "N:\L'Oreal\Kiehls\Metrics Report\2016\"

Set FileSys = New FileSystemObject
Set myFolder = FileSys.GetFolder(mydir)
dteFile = DateSerial(1900, 1, 1)
For Each objFile In myFolder.Files
If objFile.DateLastModified > dteFile Then
dteFile = objFile.DateLastModified
strFilename = objFile.Name
End If
Next objFile
Workbooks.Open mydir & strFilename, UpdateLinks:=3
-------------------------------------------------------------------
strFilename is showing an "Thumbs.db" file. why?

I am not opening an thumbs files.
Please help!

Regards,
Mahantesh

Chihiro · Sep 20, 2016

Start a new thread. Your question is unrelated to your original question.

Need VBA Vlookup help

Member

Attachments

Excel Ninja

Excel Ninja

Member

Attachments

Member

Excel Ninja

Attachments

Member

Excel Ninja

Member

Member

Excel Ninja

Member

Excel Ninja

Excel Ninja

Attachments

Excel Ninja

Excel Ninja

Member

Attachments

Excel Ninja