I am trying to automate the process for using a vlookup formula on a range of data that changes month on
The code I have so far is as follows
y = Application.WorksheetFunction.CountA(Columns("A:A"))
With Sheets("Lot 2 Data").Range("A:A")
Set found = .Find(What:="PMB", after:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,
Application.Goto found, True
b = ActiveCell.Row
For x = b To y
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],R2C4:R[" & b - b - 1 & "]C,9,0)"
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The number of rows relating to the "PMB" and "COBRA" is likely to change month on month. The purpose of the vlookup is
to look for a number from the rows with "PMB" in column A in the rows with "COBRA" in Column A and if found return the value
with column L (C12). The data is arranged alphabtecially so that all of the COBRA labelled values are displayed above and the
PMB labelled values are below.
When using the formula above I get the formula I am expecting in the first cell,
however, I want the table array to remain the exact same for each cell down.
Also when I use b - 1 within the
array value it doubles the value of b and bizarrely of I put b - b it returns the correct value of b. Is there a reason that
it is doing this?
If I need to provide you with any extra explanation please let me know, any help would greatly