How to convert IF nested function in Macro

G

galileockx

New Member
Joined
Feb 10, 2008
Messages
12
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:confused:
Thanks:)
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
What is it you actually want to automate?

The insertion of the formula?

Or do you want to convert the formula to VBA?
 
Upvote 0
Dear friend, Thanks a lot for your interest in this post, in first place i would like to convert that function in a macro, and after apply it to the Column G, so each time that i insert a value in the column F, the column G updates automatically. But the most important thing now is learn how to transfer the function to macro.
thanks a lot again:)
 
Upvote 0
You need to use the R1C1 formula style for this purpose:

Code: 
Sub Test()
Dim strMiFormula As String
strMiFormula = "=SI(IZQUIERDA(RC[-1];1)=""J"";""C"";SI(IZQUIERDA(RC[-1];1)=""G"";""C"";(SI(ESERROR(BUSCARV(RC[-1];rifs;1;FALSO));""NC"";""C""))))"
Range("G2").FormulaR1C1 = strMiFormula

End Sub

With R1C1 Formulas, formulas are "relative" - RC[-1] Means one cell to the left, in the same row. Relative to G2, this is F2.

Hope this helps.
 
Upvote 0
As a VBA function (place in a Module):
Code: 
Public Function IfTest()

    Dim F2 As String
    
    F2 = ActiveSheet.Range("F2").Value
    If Left(F2, 1) = "J" Then
        IfTest = "C"
    ElseIf Left(F2, 1) = "g" Then
        IfTest = "C"
    ElseIf IsError(Application.VLookup(F2, Range("rifs"), 1, False)) Then
        IfTest = "NC"
    Else
        IfTest = "C"
    End If
    
End Function
 
Upvote 0
Dear Alexander, Thanks for your help, i did your instructions in the workbook - Sheetchange section, but appears a message that it has produced 1004 error, and pointing to the:
Range("G2").FormulaR1C1 = strMiFormula
I tried t to change some parameters but it doesn't work.
Can you tell me my mistake, and sorry for bothering you again.
Thanks a lot
:)
 
Upvote 0
What language settings are you using?

It appears to be Spanish.

VBA doesn't generally like using any other language than American English.

It also doesn't like the different seperators, for example the semi-colon ; .

Also why do you have a VLOOKUP formula that apparently only looks in the first column of a range.:confused:
 
Upvote 0
Dear Norie , i use lookup because it is in another worksheet that contains other columns.
I like the solution that Jhon W explained because it transforms the formula in VBA function but it doesn't work. There is something wrong and i cannot see it.
Thanks you all:)
 
Last edited:
Upvote 0
Yes, check my "spanish" version of the formula = does it come out right for your Excel?

This is what I get in English version when I run my code:
Code: 
=IF(LEFT(F2,1)="J","C",IF(LEFT(F2,1)="G","C",(IF(ISERROR(VLOOKUP(F2,rifs,1,FALSE)),"NC","C"))))

and my macro looks like this:
Code: 
Sub Test()
Dim strMiFormula As String
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

Range("G2").FormulaR1C1 = strMiFormula

End Sub

Note that I have added a statement to the code:
Debug.Print strMiFormula

This helps = you can see the formula in the immediate window before it goes on the sheet. Run the macro by hitting F8 in the code window to "step through" line by line.
 
Upvote 0
Note:
Nories formula above uses VBA to get the result desired, whereas my formula simply uses VBA to insert a normal Excel formula into the cell.

Edit: OOPS -- John's code :)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Converting numbers stored as text to regular numbers query (VBA)
Replies
4
Views
1K
Alex Blakenburg
A
V
  • Solved
Show duplicates from across multiple ranges in one cell with a single nested formula?
Replies
9
Views
176
VikingLink
V
H
  • Question Question
Macro: Convert pdf into Excel and extract certain data to put together with file name
Replies
1
Views
3K
HeinrichPaul
H
Guna13
  • Question Question
Help to Optimize Macro Code for Faster Performance
Replies
0
Views
1K
Guna13
Guna13
M
  • Question Question
Macro or Power Automate to Identify and Move "Washes" from the "Master" Sheet to the "Washes" Sheet
Replies
0
Views
184
Madam_Xavier
M

Forum statistics

Threads
1,223,977
Messages
6,175,753
Members
452,667
Latest member
vanessavalentino83

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top