0

I need help with the col_index_num, sometimes it can appear on column 4 and other times on 8, differs from time to time. I need to return the number of column of the active cell after finding its value. Most likely the whole code it's wrong. But on the VLookup function you can get an idea of what im trying to archive.

    Dim pn As Range
    Dim ws As Worksheet
    Dim cn As Long

Worksheets("Entities").Activate
Cells.Find(What:="Part Number", After:=ActiveCell, LookIn:=xlFormulas2, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=True, SearchFormat:=False).Activate

    Set pn = ActiveCell
    Set cn = Active.column

Worksheets("Relationships").Activate
Range("P3").Formula = "=VLOOKUP(C2,Entities!$D:PN,cn,0)"

On the

Francisco Salcido
  • 11
  • 4
  • Variables belong outside the quotes and need concatenated in with `&`. See the linked duplicate. Plus `cn` is the column number relative to the entire sheet, not the column number in the range `Entities!$D:PN`. I.e., cell `D1` is column four on the sheet, but column *one* in the range `D:P`. – BigBen Oct 15 '20 at 20:34
  • That said, you can use INDEX,MATCH,MATCH here, no need for VBA: =`INDEX(Entities!$D:PN,MATCH(C2,Entities!D:D,0),MATCH("Part Number",Entities!D1:PN1,0))`, assuming `Part Number` is a column header. – BigBen Oct 15 '20 at 20:35
  • Yeah but PN means the part number that I had to find and set the value to. I.E. The part number it's the column that may be either on #4 or #8 . – Francisco Salcido Oct 15 '20 at 20:48
  • But is it always in row 1? – BigBen Oct 15 '20 at 20:49
  • @BigBen row 2 always, only changes columns – Francisco Salcido Oct 15 '20 at 20:54
  • Then use the `INDEX/MATCH/MATCH` formula from above but change `Entities!D1:PN1` to `Entities!D2:PN2`. No need for VBA. – BigBen Oct 15 '20 at 20:55
  • @BigBen I may not made myself clear, PN it's not the colmumn it's a variable I tried to set. By PN i meant part number, i need to find the column number and return it to use it on the vlookup. – Francisco Salcido Oct 15 '20 at 20:58
  • 1
    @FranciscoSalcido as long as your data does not go beyond column PN then it will still work. – Scott Craner Oct 15 '20 at 21:00
  • 1
    you could also do: `=VLOOKUP(C2,Entities!$D:$ZZ,MATCH("Part Number",Entities!$D$2:$ZZ$2,0),FALSE)` – Scott Craner Oct 15 '20 at 21:02
  • ^^^^ That too ^^^^ – BigBen Oct 15 '20 at 21:02
  • @BigBen `.xlsx` only goes to `XFD` – Scott Craner Oct 15 '20 at 21:02

0 Answers0