VBA Statement for nested if-Fomula

[_Google]

Col-A Col-B Col-C Col-D Col-E Col-F Col-G Col-H Col-I Col-J Col-K Col-H Col-I Col-J Col-K Col-L Col-M Col-N Col-O
IF(F="",C&D&O,IF(H="",C&F&O,IF(J="",C&F&G&O,"!")))

Hi team,

I am novice to VBA , and tried to create procedure for above formula without success. Pl help to give me procedure .. not function to run the formula output as desired . Alphabet in the formula refer to the excel columns based on which i want output to come in column A cells.<!-- google_ad_section_end -->

 

[Andrew Poulsom]

Welcome to MrExcel.

Turn on the macro recorder and enter the formula. That will give you the VBA code you need.

 

[_Google]

Hi Andrew,

1stly thanks for quick reply . Had tried some thing like that before..it limits to my selection of macro recording , please find code below .

Second problem is that , I want the sub Marcro [e.g Sub Macro2()] to start from the 1st row of my data set and end at last row of the data set .. say for column "A" whenever i run the Macro.
Can you help me to modify it a bit "Range("A1").Select "... should be start of data and Range("A2").Select... should be end of data in excel file.

--------------------------------------------------------------------------------------Sub Macro2()
'
' Macro2 Macro
'
'
Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[5]="""",RC[2]&RC[3]&RC[14],IF(RC[7]="""",RC[2]&RC[5]&RC[14],IF(RC[9]="""",RC[2]&RC[5]&RC[6]&RC[14],""!"")))"
Range("A2").Select
End Sub

 

[Andrew Poulsom]

Which column can be used to determine the number of the last row of your data set?

 

[_Google]

It will be column C . it 1st column of data set

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim LastRow As Long
    LastRow = Range("C" & Rows.Count).End(xlUp).Row
    Range("A1:A" & LastRow).FormulaR1C1 = _
        "=IF(RC[5]="""",RC[2]&RC[3]&RC[14],IF(RC[7]="""",RC[2]&RC[5]&RC[14],IF(RC[9]="""",RC[2]&RC[5]&RC[6]&RC[14],""!"")))"
End Sub

 

[_Google]

Dear Andrew,

Thanks, its working by picking 1st and last row of data set.

I do not wanted the user to see the formula reference in output.,i.e col-A , so trying to modify it... will look forward to see your comment also to compare my code.

Thanks again

 

[Andrew Poulsom]

If you want to convert the formulas to values:

Sub Test()
    Dim LastRow As Long
    LastRow = Range("C" & Rows.Count).End(xlUp).Row
    With Range("A1:A" & LastRow)
        .FormulaR1C1 = _
            "=IF(RC[5]="""",RC[2]&RC[3]&RC[14],IF(RC[7]="""",RC[2]&RC[5]&RC[14],IF(RC[9]="""",RC[2]&RC[5]&RC[6]&RC[14],""!"")))"
        .Value = .Value
    End With
End Sub

 

[_Google]

Managed by putting .value = .value

1 more ? ... How do i make variable range as below to get last column of data ( column to extend as data grows).

- I am making mess of it.. so seek support

Dim LastRow As Long
LastRow = Range("C" & Rows.Count).End(xlUp).Row
Range("A1:A" & LastRow).

 

[Andrew Poulsom]

Which row can be used to determine the last column? If it's row1:

    Dim LastCol As Long
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Range(Cells(1, 1), Cells(1, LastCol)).Select