Great Macro -

[ahamed]

hi

If an ID no is given between E6:E25, then i want macro to run on cells between F6:F25 (cells) & G6:G:25 (cells) & to give bellow formula on each cells

Condition
Example - if E6 is given an ID no, then assign below formula to cell no F6 & G6 respectively.

formula for F6 (if E6 is given a no) -
=IF(ISERROR(VLOOKUP($E6,Report2!$G$14:$AP$678,F$3,0)),"",(VLOOKUP($E6,Report2!$G$14:$AP$678,F$3,0)))

formula for G6 (if E6 is given a no) -
=IF(ISERROR(VLOOKUP($E6,Report2!$G$14:$AP$678,G$3,0)),"",(VLOOKUP($E6,Report2!$G$14:$AP$678,G$3,0)))

Red- (highlighted once are) may change between the range of (E6:E25)

Green - will not change

Pls help me on this

Why I put great macro is , i have never tried on this kind of scenario & i have no Idea whether macro could serve my purpose. If I can success, this would be my turning point on learning macro

I hope there are plenty of macro gurus

Thanx in advance ..

 

[John Davis]

hi

If an ID no is given between E6:E25, then i want macro to run on cells between F6:F25 (cells) & G6:G:25 (cells) & to give bellow formula on each cells

Condition
Example - if E6 is given an ID no, then assign below formula to cell no F6 & G6 respectively.

formula for F6 (if E6 is given a no) -
=IF(ISERROR(VLOOKUP($E6,Report2!$G$14:$AP$678,F$3,0)),"",(VLOOKUP($E6,Report2!$G$14:$AP$678,F$3,0)))

formula for G6 (if E6 is given a no) -
=IF(ISERROR(VLOOKUP($E6,Report2!$G$14:$AP$678,G$3,0)),"",(VLOOKUP($E6,Report2!$G$14:$AP$678,G$3,0)))

Red- (highlighted once are) may change between the range of (E6:E25)

Green - will not change

Pls help me on this

Why I put great macro is , i have never tried on this kind of scenario & i have no Idea whether macro could serve my purpose. If I can success, this would be my turning point on learning macro

I hope there are plenty of macro gurus

Thanx in advance ..

Untested, but perhaps?

Sub ahamed()
Dim i As Long

    For i = 6 To 25 Step -1
    
        If Range("E" & i) <> "" Then
        
            Range("F" & i).Formula = "=IF(ISERROR(VLOOKUP($E6,Report2!$G$14:$AP$678,F$3,0)),"",(VLOOKUP($E6,Report2!$G$14:$AP$678,F$3,0)))"
            Range("G" & i).Formula = "=IF(ISERROR(VLOOKUP($E6,Report2!$G$14:$AP$678,G$3,0)),"",(VLOOKUP($E6,Report2!$G$14:$AP$678,G$3,0)))"

        End If
        
    Next i

End Sub

 

[ahamed]

great ... Thanx

I hope your macro doesnt state on which sheet this is to be run [ex- Sheets("sheet1").Select]
Further, this vlookup formula goes beyond one sheet. Do I need to state that (those sheet's names) in the macro or otherwise the can vlookup itself accommodate this.

any way I will try on this.

thanx again

 

[John Davis]

great ... Thanx

I hope your macro doesnt state on which sheet this is to be run [ex- Sheets("sheet1").Select]
Further, this vlookup formula goes beyond one sheet. Do I need to state that (those sheet's names) in the macro or otherwise the can vlookup itself accommodate this.

any way I will try on this.

thanx again

The macro will run on the activesheet, however your VLOOKUP's should indicate the Sheet Names, and Reports2 should be open when the macro is run.