Macro for formula need to change based on cell value

[maheshrk81]

Hi All,

I need a macro where the formula should get updated based on cell value.
I have a refresh button, when i select the refresh button, my Cell T1, U1, V1 and W1 should get updated as below.

If my Cell "A1" is "All" then Cell T1 "All" else "Vlookup(formula)".
If my Cell "B1" is "All" then Cell U1 "All" else "Vlookup(formula)".
If my Cell "C1" is "All" then Cell V1 "All" else "Vlookup(formula)".
If my Cell "D1" is "All" then Cell W1 "All" else "Vlookup(formula)".

Thanks in Advance.
Maheshrk

 

[Peter_SSs]

Re: Need Macro for formula need to change based on cell value

I don't understand why you need a macro. Why don't you just put this directly in T1 with similar formulas in U1:W1?

=IF(A1="All",A1,vlookup_formula)

If you do need a macro, please ..
a) explain why, and
b) clarify exactly what the VLOOKUP formula should be in each column T:W

 

[maheshrk81]

Re: Need Macro for formula need to change based on cell value

Hi Peter,

Thanks for your reply.
I need this in Macro as i will callout this macro in another macro.
Can you please give me as a Code.

Thanks,
Maheshrk

 

[Peter_SSs]

Re: Need Macro for formula need to change based on cell value

I need this in Macro ...

..

If you do need a macro, please ..

b) clarify exactly what the VLOOKUP formula should be in each column T:W

 

[maheshrk81]

Re: Need Macro for formula need to change based on cell value

I Need below formulas.

If A1 = "All" then in Cell T1 it should be
VLOOKUP(K1,N7:R500,2,0)
Else VLOOKUP(K1,S7:W500,2,0)

If B1 = "All" then in Cell U1 it should be
VLOOKUP(K2,N7:R500,3,0)
Else VLOOKUP(K2,S7:W500,3,0)

If C1 = "All" then in Cell V1 it should be
VLOOKUP(K3,N7:R500,4,0)
Else VLOOKUP(K3,S7:W500,4,0)

If D1 = "All" then in Cell W1 it should be
VLOOKUP(K4,N7:R500,5,0)
Else VLOOKUP(K4,S7:W500,5,0)

Please help me with the code

 

[Peter_SSs]

Re: Need Macro for formula need to change based on cell value

I Need below formulas.

That seems different to what you wrote at the beginning but this should put those formulas in T1:W1

Sub Insert_Formulas()
  Range("T1").Formula = "=IF(A1=""All"",VLOOKUP(K1,N7:R500,2,0),VLOOKUP(K1,S7:W500,2,0))"
  Range("U1").Formula = "=IF(B1=""All"",VLOOKUP(K2,N7:R500,3,0),VLOOKUP(K2,S7:W500,3,0))"
  Range("V1").Formula = "=IF(C1=""All"",VLOOKUP(K3,N7:R500,4,0),VLOOKUP(K3,S7:W500,4,0))"
  Range("W1").Formula = "=IF(D1=""All"",VLOOKUP(K4,N7:R500,5,0),VLOOKUP(K4,S7:W500,5,0))"
End Sub

 

[maheshrk81]

Thanks Peter.
But I need only one VLOOKUP formula to show there. If it shows complete formula then i can use the IF Formula there which will not fulfill my requirement.

I need the Macro to read Cell "A1" and based on that in "T1" the formula should get updated. Same for other Cells.

Thanks,
Maheshrk

 

[Peter_SSs]

I don't understand. As far as I can see my suggestion does exactly what is described in post 5.
Can you spell out in more detail and with specific example(s) what you need?

 

[wideboydixon]

I think the OP is looking for this which will only show "one VLOOKUP formula"

Sub Insert_Formulas()
  Range("T1").Formula = IIf(Range("A1") = "All", "VLOOKUP(K1,N7:R500,2,0)", "VLOOKUP(K1,S7:W500,2,0))")
  Range("U1").Formula = IIf(Range("B1") = "All", "VLOOKUP(K2,N7:R500,3,0)", "VLOOKUP(K2,S7:W500,3,0))")
  Range("V1").Formula = IIf(Range("C1") = "All", "VLOOKUP(K3,N7:R500,4,0)", "VLOOKUP(K3,S7:W500,4,0))")
  Range("W1").Formula = IIf(Range("D1") = "All", "VLOOKUP(K4,N7:R500,5,0)", "VLOOKUP(K4,S7:W500,5,0))")
End Sub

WBD

 

[Peter_SSs]

I think the OP is looking for this which will only show "one VLOOKUP formula"

Sub Insert_Formulas()
  Range("T1").Formula = IIf(Range("A1") = "All", "VLOOKUP(K1,N7:R500,2,0)", "VLOOKUP(K1,S7:W500,2,0))")
  Range("U1").Formula = IIf(Range("B1") = "All", "VLOOKUP(K2,N7:R500,3,0)", "VLOOKUP(K2,S7:W500,3,0))")
  Range("V1").Formula = IIf(Range("C1") = "All", "VLOOKUP(K3,N7:R500,4,0)", "VLOOKUP(K3,S7:W500,4,0))")
  Range("W1").Formula = IIf(Range("D1") = "All", "VLOOKUP(K4,N7:R500,5,0)", "VLOOKUP(K4,S7:W500,5,0))")
End Sub

WBD

Ah WBD, you may be right, although the cell results will be the same won't they?

If you are correct, then those formulas would each need an "=" sign and the second of each pair would need one less closing ")"

Sub Insert_Formulas2()
  Range("T1").Formula = IIf(Range("A1") = "All", "=VLOOKUP(K1,N7:R500,2,0)", "=VLOOKUP(K1,S7:W500,2,0)")
  Range("U1").Formula = IIf(Range("B1") = "All", "=VLOOKUP(K2,N7:R500,3,0)", "=VLOOKUP(K2,S7:W500,3,0)")
  Range("V1").Formula = IIf(Range("C1") = "All", "=VLOOKUP(K3,N7:R500,4,0)", "=VLOOKUP(K3,S7:W500,4,0)")
  Range("W1").Formula = IIf(Range("D1") = "All", "=VLOOKUP(K4,N7:R500,5,0)", "=VLOOKUP(K4,S7:W500,5,0)")
End Sub