Second Attempt - VLookup VBA

[Orthos]

Hello - I had an earlier thread which I think I placed too much code thus making it difficult for someone to help. Here is a short version. The Vlookup Line generates an error . . would appreciate any help . . .can't seem to find the problem . . .

Dim MySh As WorksheetSet MySh = Sheets("Detail_R11")
Dim MyRange As Range, c As Range
Dim MyCol As Integer, MyRow As Integer


If MsgBox("Would you like to run your code?", vbYesNoCancel + vbQuestion, "GSA: NATIONAL CAPITAL REGION") = vbYes Then


Dim LastR As Integer


MySh.Activate


LastR = ActiveSheet.Range("b" & Rows.Count).End(xlUp).Row
MySh.Cells(1, 1).Select


'FIND COLUMN WITH "ORGANIZATION" HEADING
        Do
        ActiveCell.Offset(0, 1).Select
        Loop Until ActiveCell.Value = "Organization"
        MyCol = ActiveCell.Column
        MyRow = 1
        
'COLUMN FOR "ORGANIZATION"
Set MyRange = Range(Cells(MyRow, MyCol), Cells(LastR, MyCol))


'LOOP THOUGHT AND BEGIN TO MAKE CHANGES TO "ORGANIZATION" COLUMN
    For Each c In MyRange
        If c.Value = "P11B0001" And c.Offset(0, 1) <> "" Then _
        c.Value = Application.WorksheetFunction.VLookup(c.Offset(0, 1).Value, ThisWorkbook.Names("MyNamedRange").RefersToRange, 2)
    Next c


End If

 

[Kamolga]

I avoid the worsheet functions in VBA because I do not know them -> I name a range and do an offset instead...but your "end if" should be before "next c"

 

[Orthos]

Kamolga - I am not familiar with that method - would you share an example, please?

 

[Orthos]

I tried moving the End if - - now I get a End if w/out block if error . .

Dim MySh As WorksheetSet MySh = Sheets("Detail_R11")
Dim MyRange As Range, c As Range
Dim MyCol As Integer, MyRow As Integer




Dim LastR As Integer


MySh.Activate


LastR = ActiveSheet.Range("b" & Rows.Count).End(xlUp).Row
MySh.Cells(1, 1).Select


'FIND COLUMN WITH "ORGANIZATION" HEADING
        Do
        ActiveCell.Offset(0, 1).Select
        Loop Until ActiveCell.Value = "Organization"
        MyCol = ActiveCell.Column
        MyRow = 1
        
'COLUMN FOR "ORGANIZATION"
Set MyRange = Range(Cells(MyRow, MyCol), Cells(LastR, MyCol))


'LOOP THOUGHT AND BEGIN TO MAKE CHANGES TO "ORGANIZATION" COLUMN
    For Each c In MyRange
        If c.Value = "P11B0001" And c.Offset(0, 1) <> "" Then _
        c.Value = Application.WorksheetFunction.VLookup(c.Offset(0, 1).Value, Worksheets(4).Range("MyNamedRange"), 2)
        End If
    Next c

 

[JoeMo]

I would avoid the RefersToRange approach. Assuming MyNamedRange has workbook-level scope, change the Vlookup line to this:

c.Value = Application.VLookup(c.Offset(0, 1).Value, Range("MyNamedRange"), 2)

 

[Kamolga]

As mentioned I am not familiar with worksheetfunctions but maybe adding .value at the end could do the trick

Assuming Worksheets(4).Range("MyNamedRange" is a range with more than 1 column, I would rather have something like

Dim d as range
Dim [COLOR=#FF0000][FONT=Verdana]MyNamedRangecolumn1 as Range
[/FONT][/COLOR]Set [COLOR=#FF0000][FONT=Verdana]MyNamedRangecolumn1=[/FONT][/COLOR][FONT=Verdana]Worksheets(4).Range("[COLOR=#ff0000]A:A[/COLOR]")[/FONT][COLOR=#FF0000][FONT=Verdana]
[/FONT][/COLOR]
For Each c In MyRange
Set d= [FONT=Verdana]c.Offset(0, 1)[/FONT]

        If c.Value = "P11B0001" And d <> "" Then _
       
For each d in [FONT=Verdana]Worksheets(4).Range("[COLOR=#ff0000]MyNamedRangecolumn1[/COLOR]")[/FONT]


c.value=d.ofsset(0,1).value

Next d
        End If
    Next c

 

[Orthos]

JoeMo - I had this fear it would be something very simple - that seemed to resolve it - thought I don't know why . . referstorange seemed harmless . .

 

[Orthos]

Thank You, Kamolga - will try that as well . .