VBA Code for Formula

[OilEconomist]

Thanks in advance for any suggestions for which I will provide feedback.

I am getting an error on the following formula as it's when i added, the "N/A" part for the IFERROR.

Cells(i, 4).Formula = "= IFERROR( MATCH(TRUE,INDEX(Sheet1!E" & i & ":" & LastColumnLtr & i & "<>0,),0)," & ""N/A" )"

An example of the output will be:
= IFERROR( MATCH(TRUE,INDEX(Sheet1!E5:I5<>0,),0), N/A)

 

[JoeMo]

Add another close quote mrk after the A in N/A (""N/A"")

 

[OilEconomist]

Thanks JoeMo! I still get an error which is as follows: "Compile error: Syntax error:"

 

[Joe4]

Try this:

Cells(i, 4).Formula = "= IFERROR( MATCH(TRUE,INDEX(Sheet1!E" & i & ":" & LastColumnLtr & i & "<>0,),0)," & Chr(34) & "N/A" & Chr(34) & ")"

 

[OilEconomist]

Thanks JoeMo! That worked perfectly!

So I guess whenever you need something in quotes like that you should separate it with "& Chr(34) &"?

 

[Joe4]

I am Joe4. The original response was from JoeMo.

You *can* use multiple sets of double-quotes (2 or 3 sets) when wanting to include literal quotes in a string or formula. Personally, I find it a but clunky and not intuitive (I don't really like the way it looks), and I can never remember exactly how many sets that I need. So I usually elect to use Chr(34), which is the ASCII code for double-quotes.

 

[OilEconomist]

Hi Joe4, and once again thanks for your help last time.

I'm trying to use you recommendation and I'm getting error message "Run-time error '438:': Object doesn't support this property or method"

Thanks in advance to any assistance as I will provide feedback.

Sheets("Files").Cells(i, 3).Formula "= CONCATENATE( INDEX( Attributes!$B:$B, MATCH( Files!$C," & i & ", Attributes!$A:$A, 0), 1 ), " & Chr(34) & "."& Chr(34) & ", TEXT(E" & i & "," & Chr(34) & "YYYY.MM.DD" & Chr(34) & "))"

I also tried the following:

Sheets("Files").Cells(i, 3).Formula "= CONCATENATE( INDEX( Attributes!$B:$B, MATCH( Files!$C," & i & ", Attributes!$A:$A, 0), 1 ), " & """.""" & ", TEXT(E" & i & "," & """YYYY.MM.DD""" & "))"

I have a loop where I'm trying to get the following to occur in a column. Although it's not VBA Code, I put into that format so it's easier to view. Please note that i is for the integer I use to loop.



Next

For i = 1 to LastRow

= CONCATENATE( INDEX( Attributes!$B:$B, MATCH( Files!$C3, Attributes!$A:$A, 0), 1 ), ".", TEXT(E3, "YYYY.MM.DD"))
= CONCATENATE( INDEX( Attributes!$B:$B, MATCH( Files!$C4, Attributes!$A:$A, 0), 1 ), ".", TEXT(E4, "YYYY.MM.DD"))
.......
.......
= CONCATENATE( INDEX( Attributes!$B:$B, MATCH( Files!$C7, Attributes!$A:$A, 0), 1 ), ".", TEXT(E7, "YYYY.MM.DD"))

Next i

 

[shg]

Whatever else is going on, this is wrong:

... MATCH( Files!$C[B][COLOR="#FF0000"],[/COLOR][/B]" & i ...

 

[shg]

When I do that, I assign the formula to a string and then look at it in the Immediate window. You can copy from there and paste into a cell to see if it works.

  Dim sFrm As String
  
  sFrm = Replace("= CONCATENATE(INDEX(Attributes!$B:$B, MATCH(Files!$C@, Attributes!$A:$A, 0), 1 ), ""."", TEXT(E@, ""YYYY.MM.DD""))", "@", i)
  Debug.Print frm
  Stop ' and look; is it correct?
  Sheets("Files").Cells(i, 3).Formula = sFrm

 

[OilEconomist]

Thanks so much Shg! That worked perfectly!

I'm also from and in the Great State of Texas!