EdLB
asked on
How to convert a formula to VB
I am trying to convert the following double lookup formula to VB with no success.
=if(A?="","",vlookup(A?,Pr odData,Mat ch(E?,Proc essTimes!B 1:FQ1,0),T RUE))
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&",P rocessTime s,MATCH(E" &Cntr2"&,P rocessTime s!B1:FQ1,0 ),TRUE))"
Next Cntr2
=if(A?="","",vlookup(A?,Pr
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&"="""","""",
Next Cntr2
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What formula goes it give, say in G2?
ASKER
It won't execute in VB. I get a compile error highlighting the following section
"="""","""",VLOOKUP(A"
and the statement: Expected: end of statement
"="""","""",VLOOKUP(A"
and the statement: Expected: end of statement
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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.
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.
ASKER
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.
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
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))"
does exactly the same as the loopFor 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
ASKER
Ejgil, I think that is the last piece of the puzzle for me. Thanks very much, that is a huge help.
ASKER
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(xl LastCell)) .Select
ActiveWorkbook.Names.Add Name:="VistaData", RefersToR1C1:= _
"=ProcessTimes!R1C2:R735C1 74"
End Sub
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(xl
ActiveWorkbook.Names.Add Name:="VistaData", RefersToR1C1:= _
"=ProcessTimes!R1C2:R735C1
End Sub
ASKER
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(xlToLef t).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.
ActiveWorkbook.Names.Add Name:="ProdData", RefersToR1C1:= _ "=ProcessTimes!R1C2:" & "R" & Cells(Rows.Count, "B").End(xlUp).row & "C" & Cells(1, Columns.Count).End(xlToLef
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.
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.
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.
ASKER