Results 1 to 4 of 4
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    why not work... vlooukp (2000 sr 1)

    error 1004!

    Sub CERCA()
    ActiveSheet.Select
    With ActiveSheet
    ActiveSheet.Range("A10").Value = Application.WorksheetFunction.VLookup(Range("B1"), _
    Worksheets("RIEPILOGO").Range(Worksheets("RIEPILOG O").Range("A3"), Worksheets("RIEPILOGO").Range("BQ65536").End(xlUp) ), 6, False)
    End With
    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: why not work... vlooukp (2000 sr 1)

    Since BQ is blank from row 65536 to row 2 the range you are Vlookup-ing in is A3:BQ2.

    This range does not have find an exact match for the value in B3 and you get an error.

    Perhaps you should use:
    Worksheets("RIEPILOGO").Range(Worksheets("RIEPILOG O").Range("BQ3"), Worksheets("RIEPILOGO").Range("A65536").End(xlUp)) , 6, False)

    as the range. this will expand the range based on all data length of col A, not BQ:

    Sub CERCA()
    With Worksheets("RIEPILOGO")
    ActiveSheet.Range("A10").Value = Application.WorksheetFunction.VLookup(activesheet. Range("B1"), _
    .Range(.Range("BQ3"), .Range("A65536").End(xlUp)), 6, False)
    End With
    End Sub



    It will still give an error if a match is not found.

    If that will that be a problem see example code in <post#=505,324>post 505,324</post#> for a way to ignore/detect the error...

    Steve

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: why not work... vlooukp (2000 sr 1)

    assuming, now i have into sheet 4546-015377 a value into column A, is possible to make the vlookup do until the cell into sheet (range A10:A19) are blank or contain a numeric value>0...
    I have modified theh code but this find a vlaue only into cell A10, the DB list is into CA:CB of RIEPILOGO...
    aaaahhhh... tks for code and exaplin

    Sub CERCA()
    With Worksheets("RIEPILOGO")
    ActiveSheet.Range("D10").Value = Application.WorksheetFunction.VLookup(ActiveSheet. Range("A10"), _
    .Range(.Range("CA3"), .Range("CB65536").End(xlUp)), 2, False)
    End With
    End Sub

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: why not work... vlooukp (2000 sr 1)

    Something like this?
    <pre>Sub CERCA()
    Dim rCell As Range
    Dim rLookup As Range
    Dim bDoMe As Boolean
    Dim AWF As WorksheetFunction
    Set AWF = Application.WorksheetFunction
    With Worksheets("RIEPILOGO")
    Set rLookup = .Range(.Range("CA3"), _
    .Range("CB65536").End(xlUp))
    End With
    For Each rCell In Range("A10:A19")
    With rCell
    bDoMe = True
    If IsNumeric(.Value) Then
    If .Value > 0 Then bDoMe = False
    End If
    If AWF.IsText(.Value) Then bDoMe = True
    If .Value = "" Then bDoMe = False
    If bDoMe Then
    .Offset(0, 3).Value = CVErr(xlErrNA)
    On Error Resume Next
    .Offset(0, 3).Value = AWF.VLookup(rCell, _
    rLookup, 2, False)
    On Error GoTo 0
    End If
    End With
    Next
    Set AWF = Nothing
    End Sub</pre>


    If that is all you are after, Why don't you just use a fomula in D10:
    <pre>=IF(A10="","",VLOOKUP(A10,RIEPILOGO!CA:CB,2,0 ))</pre>


    Copy this from D1119

    This is faster and has less problems in coding.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •