StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMulti-step VLookup, Substitute, and then Evaluate Process
text
Body
copied!<p>I have a three-step process that should end up giving me Boolean values of True/False and also an occasional #N/A or #VALUE (which I actually want to keep as errors). I am using a workbook with multiple named worksheets and am pulling cell values from one tab through a VLookup, replacing a string in those values, and then making those values into a formula to be evaluated. Here's what I have so far; I've included comments in the code explaining where I'm stuck.</p> <pre><code>Public Sub DetermineRowsToExamine() 'Define what our Rows are for the calculations Dim NumRecords As Long NumRecords = Workbooks("POVA Daily Reporter.xlsm").Worksheets("Paste Daily Data").Range("B" & Rows.Count).End(xlUp).Row Dim CellsForFormula As Range Set CellsForFormula = Workbooks("POVA Daily Reporter.xlsm").Worksheets("Paste Daily Data").Range("g2", "G" & NumRecords) 'Now I Insert the VLookup Dim WSLogic As Worksheet Dim WSData As Worksheet Set WSData = Workbooks("POVA Daily Reporter.xlsm").Worksheets("Paste Daily Data") Set WSLogic = Workbooks("POVA Daily Reporter.xlsm").Worksheets("Logic Statements") CellsForFormula.Value = Application.WorksheetFunction.VLookup(WSData.Range("B2"), WSLogic.Range("A:D"), 4, False) 'This works in principle, but the problem is the "B2" in the VLookup - I need the "B2" to change to "B3" related 'to each row, just as it would if I pasted the rows down the columns as an cell formula 'Now I want to take that value and perform a replacement: CellsForFormula.Value = Application.WorksheetFunction.Substitute(Range("g2"), "ZZZ", "C2") 'Again, this works great, but I need it to replace "G2" or "G3" or whatever cell it's in. 'Finally, I then want to evaluate that cell as if it were a formula. When the above calculations are working, 'I end up with: AND(LEN(C2)=10,OR(LEFT(C2,2)="57",LEFT(C2,2)="13")) 'I want to evaluate this as a formula, basically making it =AND(LEN(C2)=10,OR(LEFT(C2,2)="57",LEFT(C2,2)="13")) End Sub </code></pre> <p>I think what I'm just not understanding is how to get the Cell references in the VLookup and Substitute functions to relate to whatever row I'm in.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload