'If" "and" option in VB function

[JayakrishnanJ]

Hello friends,

The following function i tried to use it in excel cell, but its too long, so i want to do it in VB, please help me in doing it in VB, I am bit new to this .

IF(C4="Aluminum Electrolytic Capacitor","CAPAE",
IF(AND(C4="Chip Array",E4="NP"),"CAPC",
IF(AND(C4="SIP",E4="NP"),"SIP",
IF(AND(C4="Chip",E4="NP"),"CAPC",
IF(AND(C4="Chip",E4="P"),"CAPCP",
IF(AND(C4="Axial Lead",E4="NP"),"CAPAD",
IF(AND(C4="Axial Lead",E4="P"),"CAPADP",
IF(AND(C4="Radial Lead",E4="NP"),CAPRD,
IF(AND(C4="Radial Lead",E4="P"),"CAPRDP",
IF(AND(C4="Molded Body",E4="NP"),"CAPM","CAPMP"))))))

Thanks,
Jayakrishnan J.

 

[Andrew Poulsom]

You can use VBA to insert the formula. What is the formula and where do you want it- ie a starting cell and something that determines the last row for a fill down.

 

[JayakrishnanJ]

Thanks Andrew,

But problem is i dont know how to use VBA in Excel, can you please help me, I will develop it from their using your concept

 

[AlphaFrog]

I think what Andrew was hoping you would provide was some specific details.

What is the formula and where do you want it- ie a starting cell and something that determines the last row for a fill down.

What worksheet and column do you want the results in? Starting cell? How can the ending cell be determined based on the last used cell in what column?

What was the formula you ended up using so it could be replicated in VBA?

Where is the lookup table located (worksheet and cell range)? Do you want to keep the lookup table on a worksheet so the user could edit it or do you want to hard code it in VBA?

The only details given so far is you have a value in C4 and a lookup table located somewhere. Not enough to write VBA code around.

Apologies to Andrew for chiming in but my guess is that's what he was asking for.

 

[JayakrishnanJ]

Sorry Alphafrog and Andrew,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I mistakenly answered for another persons question, its was for rpmitchell

 

[rpmitchell]

Hello, I'm sorry I haven't responded earlier. We are in fiscal year end close and I have been swamped at work. Anyway, the formula that I am currently using is located in column J and reads: =INDEX(Table,MATCH(H2,Reserve_List,0),MATCH(D2,Aging_List,0)).

Row 1 are column headers. Column H is the reserve category, (ie Self-Pay, Managed Care, Medicare, etc). Column D is the aging category, (ie 0-30, 31-60, etc). The tables are located on a separate sheet and named reserve list and aging list respectively. The formula pulls in the appropriate % used to calculate a bad debt reserve on the account balance, which happens to be located in column F.

I would like VBA to automaticaly insert the formula starting at row 1 and going down to the final row, wherever that might be, it vill vary from month to month.


I am playing with this in a separate workbook, so the columns are a little different. I have manually inserted the formula and used the macro recorder to generate the following code:

Range("M2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],FCtable!R1C1:R74C2,2,FALSE)"
Selection.AutoFill Destination:=Range("M2:M16731")
Columns("M:M").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
Columns("M:M").AutoFit

However, I don't like the hard coated range in column M. I want to stop at FinalRow. I also didn't name the tables here, they are simply located on a sheet named FCtable!. Additionally, I converted the formulas to hard coated values, because I think the vlookups slow down the overall worksheet processing. I get the "calculating" status on the bottom of the status bar, if there are over 100,000 rows with vlookups.

 

[rpmitchell]

Sorry, I just realized that the recorded formula that I pasted in is not the one I'm attempting to duplicate in VBA. That one is just pulling in the Payor and aging category using a simple vlookup. The next step is to match the payor and aging with the reserve table, which is what the manual formula is doing.

 

[Andrew Poulsom]

Try this:

Sub Test()
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    With Range("M2:M" & LR)
        .FormulaR1C1 = "=VLOOKUP(RC[-7],FCtable!R1C1:R74C2,2,FALSE)"
        .Value = .Value
        .EntireColumn.AutoFit
    End With
End Sub