How to convert IF nested function in Macro

[galileockx]

Dear Experts, this is my first question, i am a newbie in macros, trying to learn by myself, i have several days trying to convert a If nested function in a automate macro, the problem is that i have a lot of formulas, and the machine becomes slow, so i have to apply this formula to "column G"
=IF(LEFT(F2;1)="J";"C";IF(LEFT(F2;1)="g";"C";(IF(ISERROR(VLOOKUP(F2;rifs;1;FALSE));"NC";"C"))))
I would appreciate if someone can help me with this problem, please
Thanks

 

[Norie]

Sorry but it's still not clear exactly what you want to do, and that's why I've not posted any code.

Alexander's code would place a formula in cell G2.

John's code will return a value to whatever cell the (user defined) function is placed in but the result will only ever relate to the value in F2 on the active sheet.

Neither will do anything automatically, which it appears is what you want to do.

Please explain further.

 

[xenou]

If you are on different worksheets be careful:

You may need to tell Excel what sheet:

Worksheets("Sheet1").Range("G2").FormulaR1C1 = strMiFormula

End Sub

I tried t to change some parameters but it doesn't work.

What did you change?

 

[galileockx]

Dear Alexander, thanks a lot for your patience and help, it worked!!! , i hope this can increases the speed of procesing all data, only one more question, in this worksheet i have a lot of formulas to do many things to all the data in several worksheets, so is ther a way to apply the code to the complete column until last ocupied cell?
Thanks a lot for you help, i really appreciate it.

 

[galileockx]

"Neither will do anything automatically, which it appears is what you want to do."

Dear Norie, you are right! Those codes are for only G2,
i am trying to get the results automatically after the cells in column F have chaged, so i suppose, that i need something like that in workbook in the change event, i am trying to do it, but i can not do it.
If you have an idea i'll appreciate a lot.
Thanks a lot

 

[xenou]

Good. Now:

Sub Test()
Dim strMiFormula As String
Dim x As Long
Dim URow As Long 'Ultima Row

URow = Worksheets("Sheet2").Cells(Rows.Count, "G").End(xlUp).Row

strMiFormula = "=IF(LEFT(RC[-1],1)=""J"",""C"",IF(LEFT(RC[-1],1)=""G"",""C"",(IF(IsError(Vlookup(RC[-1],rifs,1,false)),""NC"",""C""))))"

Debug.Print strMiFormula

For x = 2 To URow
    Worksheets("Sheet2").Cells(x, "G").FormulaR1C1 = strMiFormula
Next x

End Sub

Como se dice en espanol "Row"?

Note: change "Sheet2" to the name of the correct worksheet.

 

[galileockx]

End Sub[/code]Como se dice en espanol "Row"?

Dear Alexander, in spanish is "Fila"
Thanks a lot for your help, i am going to test it and later i'll tell you.
Thanks again my friend

 

[galileockx]

Dear Alexander, i modified a little your code changing the Urow value, and it worked!!!
Thanks for your help.
This is the final code with your idea:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim strMiFormula As String
Dim X As Long
Dim URow As Long 'Ultima Row
Application.ScreenUpdating = False
URow = Worksheets("Hoja1").Cells(65536, "F").End(xlUp).Row

For X = 2 To URow
strMiFormula = "=IF(LEFT(RC[-1],1)=""J"",""C"",IF(LEFT(RC[-1],1)=""G"",""C"",(IF(IsError(Vlookup(RC[-1],rifs,1,false)),""NC"",""C""))))"

Debug.Print strMiFormula


Worksheets("Hoja1").Cells(X, "G").FormulaR1C1 = strMiFormula

Next X
Application.ScreenUpdating = True
End Sub

One more time thank you all.