Help on a VBA Code

[hrayani]

Hello Friends,

I am using the below vba Code which is working just perfect applying formulas to range B5:B100

What I want in the vba code is that the formula to pick the second named range "fri1_actual_date" from cell A1

Is it Possible ??

Sub ApplyFormulaToPendingTargets()

Dim ws As Worksheet  
Set ws = Sheets("PENDING TARGETS")

ws.Range("B5:B1000").ClearContents

ws.Range("B5:B100").Formula = "=IFERROR(AGGREGATE(15,6,orders_ref/(((orders_design_receipt<>"""")*(fri1_actual_date=""""))*ISNA(MATCH(orders_ref,B4:B$4,0))),1),"""")"

End Sub

 

[Peter_SSs]

pick the second named range "fri1_actual_date" from cell A1

You have not told us which sheet this particular A1 is on, but see if this is what you mean.

ws.Range("B5:B100").Formula = "=IFERROR(AGGREGATE(15,6,orders_ref/(((orders_design_receipt<>"""")*(" & Range("A1").Value & "=""""))*ISNA(MATCH(orders_ref,B4:B$4,0))),1),"""")"

 

[hrayani]

Hi Peter,

Thanks a lot - It is working just perfect - Exactly what I wanted

Sorry for not informing that on which sheet the cell A1 is on
Well, it's on the current active sheet


Shal I continue with it or do we have to mention the sheet name ??

 

[Peter_SSs]

do we have to mention the sheet name ??

No, if it is always going to be on the current active sheet, the above code should suffice.

 

[hrayani]

Thanks a lot

 

[Peter_SSs]

You're welcome.