prabhadixit
06-13-2008, 09:14 AM
Hi
I have a piece of code that populates values in various cells in a form (Excel Worksheet) based on value of C5 of same worksheet. The code also contains statements to hide a certain row if the values in this row is 0. However, I find that I need to run this macro manually each time. I want the macro to run automatically everytime the value in cell C5 changes. Someone suggested that I use Worksheet_Change with Target cell specified as C5. But now this gives a run time error. This is my code.
Sub Master()
Call Worksheet_Change(C5)
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$5" Then
'Range("C5").Select
'C1 = ActiveCell.Value
' Do
MsgBox ("Am I here")
Rows("22:22").Select
Selection.EntireRow.Hidden = False
MsgBox ("Yea, I am!")
Range("C4").Select
ActiveCell.FormulaR1C1 = "= VLOOKUP(R[1]C,'[COMERCIAL_VII1(1)_test version.xls]dif de comp con ajuste de sueld'!RC[-1]:R[114]C[120],6,FALSE)"
Range("C7").Select
ActiveCell.FormulaR1C1 = "= VLOOKUP(R[-2]C,'[COMERCIAL_VII1(1)_test version.xls]dif de comp con ajuste de sueld'!R[-3]C[-1]:R[114]C[120],11,FALSE)"
Range("C8").Select
ActiveCell.FormulaR1C1 = "= VLOOKUP(R[-3]C,'[COMERCIAL_VII1(1)_test version.xls]dif de comp con ajuste de sueld'!R[-4]C[-1]:R[115]C[121],50,FALSE)"
Range("C6").Select
ActiveCell.FormulaR1C1 = "= VLOOKUP(R[-1]C,'[COMERCIAL_VII1(1)_test version.xls]dif de comp con ajuste de sueld'!R[-2]C[-1]:R[113]C[119],15,FALSE)"
Range("D15").Select
ActiveCell.FormulaR1C1 = "= VLOOKUP(R[-10]C[-1],'[COMERCIAL_VII1(1)_test version.xls]dif de comp con ajuste de sueld'!R[-11]C[-2]:R[104]C[118],14,FALSE)"
Range("D22").Select
j = ActiveCell.Value
MsgBox ("Value first is" & j)
ActiveCell.FormulaR1C1 = "= VLOOKUP(R[-17]C[-1],'[COMERCIAL_VII1(1)_test version.xls]dif de comp con ajuste de sueld'!R[-18]C[-2]:R[97]C[118],37,FALSE)"
j = ActiveCell.Value
MsgBox ("Value second is" & j)
If j = 0 Then
MsgBox ("Im inside the loop!")
Rows("22:22").Select
Selection.EntireRow.Hidden = True
MsgBox ("Im leaving the loop!")
End If
'Range("C5").Select
'C2 = ActiveCell.Value
'Loop While C1 <> C2
End If
End Sub
I have a piece of code that populates values in various cells in a form (Excel Worksheet) based on value of C5 of same worksheet. The code also contains statements to hide a certain row if the values in this row is 0. However, I find that I need to run this macro manually each time. I want the macro to run automatically everytime the value in cell C5 changes. Someone suggested that I use Worksheet_Change with Target cell specified as C5. But now this gives a run time error. This is my code.
Sub Master()
Call Worksheet_Change(C5)
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$5" Then
'Range("C5").Select
'C1 = ActiveCell.Value
' Do
MsgBox ("Am I here")
Rows("22:22").Select
Selection.EntireRow.Hidden = False
MsgBox ("Yea, I am!")
Range("C4").Select
ActiveCell.FormulaR1C1 = "= VLOOKUP(R[1]C,'[COMERCIAL_VII1(1)_test version.xls]dif de comp con ajuste de sueld'!RC[-1]:R[114]C[120],6,FALSE)"
Range("C7").Select
ActiveCell.FormulaR1C1 = "= VLOOKUP(R[-2]C,'[COMERCIAL_VII1(1)_test version.xls]dif de comp con ajuste de sueld'!R[-3]C[-1]:R[114]C[120],11,FALSE)"
Range("C8").Select
ActiveCell.FormulaR1C1 = "= VLOOKUP(R[-3]C,'[COMERCIAL_VII1(1)_test version.xls]dif de comp con ajuste de sueld'!R[-4]C[-1]:R[115]C[121],50,FALSE)"
Range("C6").Select
ActiveCell.FormulaR1C1 = "= VLOOKUP(R[-1]C,'[COMERCIAL_VII1(1)_test version.xls]dif de comp con ajuste de sueld'!R[-2]C[-1]:R[113]C[119],15,FALSE)"
Range("D15").Select
ActiveCell.FormulaR1C1 = "= VLOOKUP(R[-10]C[-1],'[COMERCIAL_VII1(1)_test version.xls]dif de comp con ajuste de sueld'!R[-11]C[-2]:R[104]C[118],14,FALSE)"
Range("D22").Select
j = ActiveCell.Value
MsgBox ("Value first is" & j)
ActiveCell.FormulaR1C1 = "= VLOOKUP(R[-17]C[-1],'[COMERCIAL_VII1(1)_test version.xls]dif de comp con ajuste de sueld'!R[-18]C[-2]:R[97]C[118],37,FALSE)"
j = ActiveCell.Value
MsgBox ("Value second is" & j)
If j = 0 Then
MsgBox ("Im inside the loop!")
Rows("22:22").Select
Selection.EntireRow.Hidden = True
MsgBox ("Im leaving the loop!")
End If
'Range("C5").Select
'C2 = ActiveCell.Value
'Loop While C1 <> C2
End If
End Sub