Help converting formula to VBA

[Frank3923]

I need assistance in converting a formula over to vba, I have inserted the formula, as well as my attempt to change it. Being that I am not a programmer, I would appreciate any assistance in showing me, the proper way to list the formula into the mace

formula needed to be inserted

=LEFT(G8,FIND("(",G8)-1)

This is my attempt to create it, in VBA

Range("E" & i + 1).Formula = "=Left(Range(G" & i + 1 & Find(" ""("", Range("G" & i)-1 & ")"

 

[jmthompson]

Try

Range("E" & i + 1).FormulaR1C1 = "=Left(RC[2], Find(""("", RC[2])-1)

 

[Frank3923]

Thank you for the quick response.

I inserted the formula, and it, is returning a "False" value.
It is returning the False response in the cell, that I want the value in.

Do you have any further suggestions?

Thank you

 

[Rick Rothstein]

How about this?

Range("E" & i).Formula = "=LEFT(G" & i & ",FIND(""("",G" & i & ")-1)"

Note I removed the +1 from you i+1 because I'm not sure why it is there (just run your loop one lower I would think).

 

[Frank3923]

Thank you for the response.

I added your line in, and when I go to run it, I am getting a compile error.

"Next without for",

I have enclosed the code below. you can see that the 3rd line is:

For i = 1 To LR

So I am a little confused, as to why I am getting the error message


Sub Get_TValue()
Dim LR As Long: LR = Range("G" & Rows.Count).End(xlUp).Row


For i = 1 To LR
If Trim(Range("G" & i).Value) = "Final" Then
Range("E" & i + 1).Formula = Range("E" & i).Formula = "=LEFT(G" & i & ",FIND(""("",G" & i & ")-1)"
Next i

End Sub

 

[Rick Rothstein]

I added your line in, and when I go to run it, I am getting a compile error.

"Next without for",

Sub Get_TValue()
Dim LR As Long: LR = Range("G" & Rows.Count).End(xlUp).Row


For i = 1 To LR
If Trim(Range("G" & i).Value) = "Final" Then
Range("E" & i + 1).Formula = Range("E" & i).Formula = "=LEFT(G" & i & ",FIND(""("",G" & i & ")-1)"
Next i

End Sub

Either you need to put the assignment statement on the same line as the If..Then statement like this...

Sub Get_TValue()
  Dim LR As Long: LR = Range("G" & Rows.Count).End(xlUp).Row
  For i = 1 To LR
    If Trim(Range("G" & i).Value) = "Final" Then Range("E" & i + 1).Formula = Range("E" & i).Formula = "=LEFT(G" & i & ",FIND(""("",G" & i & ")-1)"
  Next i
End Sub

or you need to add an End If statement like this...

Sub Get_TValue()
  Dim LR As Long: LR = Range("G" & Rows.Count).End(xlUp).Row
  For i = 1 To LR
    If Trim(Range("G" & i).Value) = "Final" Then
      Range("E" & i + 1).Formula = Range("E" & i).Formula = "=LEFT(G" & i & ",FIND(""("",G" & i & ")-1)"
    End If
  Next i
End Sub

depending on if you want a single line If..Then statement or an If..Then statement block.

 

[Frank3923]

The End If, cured the error message.

But, I still received the same results "False", and there is no formula showing in the cell.

any ideas?

Thank you in advance.

 

[jmthompson]

Is there a ( in the cell in G?

 

[Frank3923]

Yes, Jen. There is a ( in the cell.

I did manually type in this formula, into a cell, and it returns the correct results.

In both yours and Ricks methods, it returns the same results, "FALSE", and there is no reflection of a formula, being in that cell.

this is the formula, that I manually entered.
=LEFT(G6,FIND("(",G6)-1)

Thank you

 

[jmthompson]

Will you post your full code?