?
Solved

How to convert a formula to VB

Posted on 2015-02-06
15
Medium Priority
?
142 Views
Last Modified: 2016-02-11
I am trying to convert the following double lookup formula to VB with no success.
=if(A?="","",vlookup(A?,ProdData,Match(E?,ProcessTimes!B1:FQ1,0),TRUE))

The formula works if I put it in the cells in column G but I can't get VB to do so.

The wildcard ? is a variable row number. ProdData is a named range in the worksheet ProcessTimes. Here is the code I was trying to use but have tried many different variations with no success:
    For Cntr2 = 2 To lastRow
           
Range("G" & Cntr2).Formula = "=IF(A"&Cntr2&"="""","""",VLOOKUP(A"&Cntr2&",ProcessTimes,MATCH(E"&Cntr2"&,ProcessTimes!B1:FQ1,0),TRUE))"
   
   Next Cntr2
0
Comment
Question by:EdLB
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 2
  • +1
15 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 100 total points
ID: 40593447
Just checking you haven't got a typo. There's an & in the wrong place.

You have:

Range("G" & Cntr2).Formula = "=IF(A"&Cntr2&"="""","""",VLOOKUP(A"&Cntr2&",ProcessTimes,MATCH(E"&Cntr2"&,ProcessTimes!B1:FQ1,0),TRUE))"

It should be

Range("G" & Cntr2).Formula = "=IF(A"&Cntr2&"="""","""",VLOOKUP(A"&Cntr2&",ProcessTimes,MATCH(E"&Cntr2&",ProcessTimes!B1:FQ1,0),TRUE))"
0
 

Author Comment

by:EdLB
ID: 40593529
Thanks for the catch on the typo.  I corrected that but the code still doesn't work.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40593533
What formula goes it give, say in G2?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:EdLB
ID: 40593556
It won't execute in VB. I get a compile error highlighting the following section

"="""","""",VLOOKUP(A"

and the statement: Expected: end of statement
0
 
LVL 23

Accepted Solution

by:
Ejgil Hedegaard earned 1500 total points
ID: 40594842
You must make a space before and after each &, to tell VBA that & is not part of the text.
Another error (does not prevent compiling) is that the first ProcessTimes must be replaced with ProdData to match the original formula.
ProcessTimes is a sheet, not a named range.

Range("G" & Cntr2).Formula = "=IF(A" & Cntr2 & "="""","""",VLOOKUP(A" & Cntr2 & ",ProdData,MATCH(E" & Cntr2 & ",ProcessTimes!B1:FQ1,0),TRUE))"

Inserting the formula row by row can take a lot of time.
It is more efficient to us R1C1 notation and insert all formulas in one statement, like this.

Range("G2:G" & lastrow).FormulaR1C1 = "=IF(RC[-6]="""","""",VLOOKUP(RC[-6],Proddata,MATCH(RC[-2],ProcessTimes!R1C2:R1C173,0),TRUE))"

It does the same as the For..Next loop.
0
 

Author Comment

by:EdLB
ID: 40594902
That is great. However, one significant question remains. How do I handle the situation where the number of rows could be different each time I run the macro?
0
 
LVL 31

Assisted Solution

by:gowflow
gowflow earned 400 total points
ID: 40595365
to answer your last question
lastrow should always have to maximum rows in the worksheet. to do this you need to have lastrow updated in the beginning of the sub or after you add rows. You choose a column that have the most data downward so you pickup the maximum row (sometimes it is not always Col A that have all the data downward sometimes it is G or F ... whatever) for the example I will use G as I see your past formula use G so it is safe to use G

So just before the Fro to Loop I would have it this way:
lastrow = Range("G" & Rows.count).End(XlUp).Row
For Cntr2 = 2 To lastRow

...
Next Cntr2

This should make sure your lastrow is always updated with the maximum rows in the worksheet all the time.

AS a Side NOTE
I would recommend when using VBA to always recourse to explicit variable naming and try to avoid as much as possible implicit variables (using Range("A1") = "abc" is implicit as it assumes that Range("A1") of the activesheet, whereas Explicit would be:
Dim WS as worksheet

Set WS = Sheets("Sheet2")
WS.Range("A1")="abc"
this way you are always sure regardless where the activesheet is that A1 will be updated with abc string in sheet2 whereas in the first example you and your luck when the code hits Range("A1") if the activesheet is Sheet10 then A1 of sheet10 will hold value of "abc"

for this case is no big deal but extend this to variable like the one you have and you may be messing up your whole workbook simply due to a sheet getting focus by accident !!

so finally your formula should look like:
WS.Range("G" & Cntr2).Formula = "=IF(A" & Cntr2 & "="""","""",VLOOKUP(A" & Cntr2 & ",ProdData,MATCH(E" & Cntr2 & ",ProcessTimes!B1:FQ1,0),TRUE))"

gowflow
0
 

Author Comment

by:EdLB
ID: 40595578
Fantastic. Thanks. I do have the lastrow calc and assign done as you describe. I was hoping to use the lastrow with the R1C1 notation as it is more efficient but I take it that is not doable?
0
 
LVL 23

Expert Comment

by:Ejgil Hedegaard
ID: 40595593
Use the suggestion from gowflow to find the last row.
Guessed you had the value for lastrow already, since the For..Next loop used it.
Just use the formula with R1C1 notation to insert all formulas at once, it will work.

R1C1 is how Excel works with formulas, references are relative, so RC[-6] means same row, and 6 columns to the left. In column G (column 7), 6 rows to the left is column A.
In the last part of the formula, ProcessTimes!R1C2:R1C173, means fixed range $B$1:$FQ$1.
R1C1 notation is probably not as easy to read, but is quite flexible.
0
 

Author Comment

by:EdLB
ID: 40595615
Thanks Ejgil. I'm not completely sure of the RC notation but your explanation is helpful. However, the real challenge for me will be creating a string or whatever is required to make the RC notation work when the last row might vary every time I run the macro. In working my way through VB, getting the mechanics of the formulas or loops or logical structure to work has not been a big problem. Where I spend hours of time trying to get one line to work is in putting a variable in a formula and getting the code to execute. If there are suggestions on that for the RC format for my double lookup formula, they would be most appreciated.
0
 
LVL 23

Expert Comment

by:Ejgil Hedegaard
ID: 40595633
You don't have to change the string, as it works for all rows, and you don't have to loop.
The first part of the statement Range("G2:G" & lastrow) defines where to insert the formulas, from G2 down to the row defined by lastrow.
You can't use column G to find the last row, since the cells are probably empty, but since column A is used in the formula, the statement
lastrow = Range("A" & Rows.count).End(XlUp).Row
will find the last row.

The statement
Range("G2:G" & lastrow).FormulaR1C1 = "=IF(RC[-6]="""","""",VLOOKUP(RC[-6],Proddata,MATCH(RC[-2],ProcessTimes!R1C2:R1C173,0),TRUE))"

Open in new window

does exactly the same as the loop
For Cntr2=2 to lastrow
Range("G" & Cntr2).Formula = "=IF(A" & Cntr2 & "="""","""",VLOOKUP(A" & Cntr2 & ",ProdData,MATCH(E" & Cntr2 & ",ProcessTimes!B1:FQ1,0),TRUE))"
Next Cntr2

Open in new window

0
 

Author Comment

by:EdLB
ID: 40595746
Ejgil, I think that is the last piece of the puzzle for me. Thanks very much, that is a huge help.
0
 

Author Comment

by:EdLB
ID: 40596063
Whoops. Not quite.

So the code generates the formula perfectly. However, I get a #REF error in the cells with the formula. I find that if I go to the ProcessTimes worksheet, delete the named range ProdData and then recreate it exactly as before, the formulas all work.

Should I delete the named range ProdData and recreate it every time I run the macro? That seems crude.

Also, I recorded a Macro to see how the Range naming code works and got the code below. However, it does not give me the code for a situation where the ProdData table may be a different size every time I run the macro.

Sub NameRange()
'
' NameRange Macro

'
    Range("B1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ActiveWorkbook.Names.Add Name:="VistaData", RefersToR1C1:= _
        "=ProcessTimes!R1C2:R735C174"
End Sub
0
 

Author Comment

by:EdLB
ID: 40596139
Update.  I found the problem. I tried to define ProdData named range with the following code:

    ActiveWorkbook.Names.Add Name:="ProdData", RefersToR1C1:= _  "=ProcessTimes!R1C2:" & "R" & Cells(Rows.Count, "B").End(xlUp).row & "C" & Cells(1, Columns.Count).End(xlToLeft).Column

I found that the RC code was ignoring the "Process Times" location definition and was looking in the active worksheet, which was not ProcessTimes. When I added a line to make ProcessTimes the active worksheet, it worked like a charm.
0
 
LVL 23

Expert Comment

by:Ejgil Hedegaard
ID: 40596205
Inserting a formula with VBA is the same as typing.
Ref error means that the formula can't find the column for the lookup, because the range does not have enough columns.

The definition of ProdData indicate that the match function looks in the first line of ProdData, so I think you should change
ProcessTimes!R1C2:R1C173 (= B1:FQ1) to Index(ProdData,1,).
Then it will update when ProdData is updated to the new range.
When the Index function only has row specification (1), and not a column specification, it returns the entire first row of ProdData, not a specific cell.

If the ProdData table is imported from a csv or txt file, it should not be needed to redefine the range at every update, Excel should do that automatically.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question