VBA Long String

[GreenWizard]

Hello, I'm new to VBA and I'm using the Excel Macro Recorder to create the code for me. I realize this isn't best practice but it works most the time. That being said, I'm trying to create some code that incorporates a long Excel formula (VSLOOKUP x15+). My problem is when I record a long excel forumla, the VBA code gets stripped forming two separate VBA code lines. See below. This causes an error. How can I fix this so it doesn't happen again? Much appreciated!!! Thanks.

EXCEL FORMULA:

IF(A1="B",VLOOKUP(A1,Sheet2!$A:$M,5,0),IF(A1="C",VLOOKUP(A1,Sheet2!$A:$M,6,0),IF(A1="D",VLOOKUP(A1,Sheet2!$A:$M,7,0),IF(A1="E",VLOOKUP(A1,Sheet2!$A:$M,8,0),IF(A1="F",VLOOKUP(A1,Sheet2!$A:$M,9,0),IF(A1="G",VLOOKUP(A1,Sheet2!$A:$M,10,0),IF(A1="H",VLOOKUP(A1,Sheet2!$A:$M,11,0),IF(A1="I",VLOOKUP(A1,Sheet2!$A:$M,12,0),IF(A1="J",VLOOKUP(A1,Sheet2!$A:$M,13,0),IF(A1="K",VLOOKUP(A1,Sheet2!$A:$M,14,0),IF(A1="L",VLOOKUP(A1,Sheet2!$A:$M,15,0),IF(A1="M",VLOOKUP(A1,Sheet2!$A:$M,16,0),IF(A1="N",VLOOKUP(A1,Sheet2!$A:$M,17,0),IF(A1="O",VLOOKUP(A1,Sheet2!$A:$M,18,0),)))))))))))))))))

MACRO RECORDER VBA CODE:

Range("B1").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=""B"",VLOOKUP(RC[-1],Sheet2!C1:C13,5,0),IF(RC[-1]=""C"",VLOOKUP(RC[-1],Sheet2!C1:C13,6,0),IF(RC[-1]=""D"",VLOOKUP(RC[-1],Sheet2!C1:C13,7,0),IF(RC[-1]=""E"",VLOOKUP(RC[-1],Sheet2!C1:C13,8,0),IF(RC[-1]=""F"",VLOOKUP(RC[-1],Sheet2!C1:C13,9,0),IF(RC[-1]=""G"",VLOOKUP(RC[-1],Sheet2!C1:C13,10,0),IF(RC[-1]=""H"",VLOOKUP(RC[-1],Sheet2!C1:C13,11,0),IF(RC[-1]=""I""" & _
"(RC[-1],Sheet2!C1:C13,12,0),IF(RC[-1]=""J"",VLOOKUP(RC[-1],Sheet2!C1:C13,13,0),IF(RC[-1]=""K"",VLOOKUP(RC[-1],Sheet2!C1:C13,14,0),IF(RC[-1]=""L"",VLOOKUP(RC[-1],Sheet2!C1:C13,15,0),IF(RC[-1]=""M"",VLOOKUP(RC[-1],Sheet2!C1:C13,16,0),IF(RC[-1]=""N"",VLOOKUP(RC[-1],Sheet2!C1:C13,17,0),IF(RC[-1]=""O"",VLOOKUP(RC[-1],Sheet2!C1:C13,18,0),))))))))))))))"

 

[FryGirl]

I would consider first paring your formula down to eliminate all of the IF's

Also, if you go out 18 columns from column A it goes past column M

Here is an example to work with formula wise before working with VBA

=VLOOKUP(A1,Sheet2!$A:$M,LOOKUP(A1,{"B","C","D"},{5,6,7}),0)

 

[RoryA]

I agree with FryGirl but the actual reason you have the problem is not that the code has been split into two lines, but rather that the recorder has missed out part of the formula string and what is left is an invalid formula.

 

[GreenWizard]

I'm sure your way is 100% better, but I'm having issues with it. When I apply the formula I get an N/A.

For this exercise how can I streamline the Vlookup? In this example what would the new Vlookup formula be? Thank you again!!!

Example:

Excel Vlookup Formula

=If(A2="A",Vlookup(A2,Sheet2!A$E$,2,0),If(A2="C",Vlookup(A2,Sheet2!A$E$,3,0),If(A2="F",Vlookup(A2,Sheet2!A$E$,4,0),If(A2="J",Vlookup(A2,Sheet2!A$E$,5,0),"")))))

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Desired Result[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]=vlookup formula[/TD]
[TD]=4[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]=vlookup formula[/TD]
[TD]=2[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]=vlookup formula[/TD]
[TD]=6[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]=vlookup formula[/TD]
[TD]=5[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Column3[/TD]
[TD]Column4[/TD]
[TD]Column5[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]6[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

 

[FryGirl]

Try

      A B ------------------------------------C------------------------------------
  1   A 4 B1:=VLOOKUP(A1,Sheet2!$A$1:$E$4,LOOKUP(A1,{"A","C","F","J"},{2,3,4,5}),0)
  2   C 2 B2:=VLOOKUP(A2,Sheet2!$A$1:$E$4,LOOKUP(A2,{"A","C","F","J"},{2,3,4,5}),0)
  3   F 6 B3:=VLOOKUP(A3,Sheet2!$A$1:$E$4,LOOKUP(A3,{"A","C","F","J"},{2,3,4,5}),0)
  4   J 5 B4:=VLOOKUP(A4,Sheet2!$A$1:$E$4,LOOKUP(A4,{"A","C","F","J"},{2,3,4,5}),0)