How to insert concatenate formula (that includes VLOOKUP) into next available cell with VBA

0
votes

I've created a "form" (with data validation, if statements, and VLOOKUP) that a user can choose different options and generate a part number and price. I found a VBA module that allows me to copy the part number and price into another sheet ('List'), specifically in the next empty row. A user can now create and copy different parts into a list.

I want to add a description of the part they configured. I can generate the description by using If statements and VLOOKUP on the part number that was generated. Currently I've added these formulas to the 'List' sheet but now the print preview is showing three pages because I dragged the formula down 50 cells.

I want to add on to my current "Copy to List" VBA module to insert the product description into the next available row in the column that I specified (like I did for the price and part number).

Here is the VBA module

Sub Copyi510IP20()
'Prevent Computer Screen from running
  Application.ScreenUpdating = False

'Insert your macro code here......

Sheets("Builder").Range("C19").Copy
Sheets("List").Range("D" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Sheets("Builder").Range("C21").Copy
Sheets("List").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets("Builder").Activate

'Allow Computer Screen to refresh (not necessary in most cases)
  Application.ScreenUpdating = True

End Sub

I would like to add this formula to it.


="Product: "&IF(MID(Builder!C19,3,1)="1","i510 protec","i550 protec")&CHAR(10)&"Phase: "&VLOOKUP(MID(Builder!C19,9,1),Tables!$B$2:$D$7,3,FALSE)&CHAR(10)&"Power: "&VLOOKUP(MID(Builder!C19,6,3),Tables!$B$10:$D$30,3,FALSE)&CHAR(10)&"Fieldbus: "&VLOOKUP(MID(Builder!C19,16,3),Tables!$B$57:$D$64,3,FALSE)

Any idea on how I can add this formula into Column A on 'List'?

1
excelvbavlookup
Use Sheets("List").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).FormulaLocal = "=Product.... Note the leading = sign. And I would first find the target row and write all values directly there instead of findind the first empty cell in each column, – AcsErno
I tried doing this but get errors at the first quotes – Steven Erickson

1 Answers

1
votes

The concept is to compile a string that is a syntactically correct formula, like Cells(10, 1).FormulaLocal = "=A4*2". If the formula contains strings (between quotation marks), you have to use some trick like this:

 Const DQUOTE = """""
 Range("A20").FormulaLocal = "=" & DQUOTE & "Product: " & DQUOTE & "&IF(MID(Builder!C19,3,1)=" & DQUOTE & "1" & DQUOTE & ...

so wherever you want to see quotes within the final formula you'll have to use DQUOTE. It takes some time to prepare such a complex formula but possible.