Hi
I am using a simple VBA macro to place formula into given cells on the current row.
It is very long and could be alot faster. Also there is no consistency, I use Cells(ActiveRow,1) throughout but then at the end I could only use the Range() to get desired result of pasting all previous items as values. Any way to make this alot simpler, smaller and consistent (I prefer the cells approach)
Sub MESSAGEBOX()
Dim Contract As Variant
Contract = Application.InputBox("Please enter Contract no", "CONTRACT")
'ActiveCell.FormulaR1C1 = _
"=""" + Contract + """&""-""&RC[1]"
ActiveCell.FormulaR1C1 = _
"=""" + Contract + """"
Cells(ActiveCell.Row, 2).FormulaR1C1 = _
"=IFERROR(LOOKUP(2,1/((R10C1:INDIRECT(""A""&ROW()-1)=RC1)/(R10C4:INDIRECT(""D""&ROW()-1)=RC4)/(R10C7:INDIRECT(""G""&ROW()-1)=RC7)),R10C2:INDIRECT(""B""&ROW()-1))+1,1)"
Cells(ActiveCell.Row, 3).FormulaR1C1 = _
"=VLOOKUP(RC1,R10C1:INDIRECT(""I""&ROW()-1),COLUMN(),0)"
With Cells(ActiveCell.Row, 4).VALIDATION
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Cells(ActiveCell.Row, 4).FormulaR1C1 = _
"LIFT"
Cells(ActiveCell.Row, 5).FormulaR1C1 = _
"=VLOOKUP(RC1,R10C1:INDIRECT(""I""&ROW()-1),COLUMN(),0)"
Cells(ActiveCell.Row, 6).FormulaR1C1 = _
"=VLOOKUP(RC1,R10C1:INDIRECT(""I""&ROW()-1),COLUMN(),0)"
Cells(ActiveCell.Row, 7).FormulaR1C1 = _
"=VLOOKUP(RC1,R10C1:INDIRECT(""I""&ROW()-1),COLUMN(),0)"
Cells(ActiveCell.Row, 8).FormulaR1C1 = _
"=VLOOKUP(RC1,R10C1:INDIRECT(""I""&ROW()-1),COLUMN(),0)"
Cells(ActiveCell.Row, 9).FormulaR1C1 = _
"=VLOOKUP(RC1,R10C1:INDIRECT(""I""&ROW()-1),COLUMN(),0)"
With Range("C" & ActiveCell.Row & ":I" & ActiveCell.Row)
.Value = .Value
End With
End Sub
I am using a simple VBA macro to place formula into given cells on the current row.
It is very long and could be alot faster. Also there is no consistency, I use Cells(ActiveRow,1) throughout but then at the end I could only use the Range() to get desired result of pasting all previous items as values. Any way to make this alot simpler, smaller and consistent (I prefer the cells approach)
Sub MESSAGEBOX()
Dim Contract As Variant
Contract = Application.InputBox("Please enter Contract no", "CONTRACT")
'ActiveCell.FormulaR1C1 = _
"=""" + Contract + """&""-""&RC[1]"
ActiveCell.FormulaR1C1 = _
"=""" + Contract + """"
Cells(ActiveCell.Row, 2).FormulaR1C1 = _
"=IFERROR(LOOKUP(2,1/((R10C1:INDIRECT(""A""&ROW()-1)=RC1)/(R10C4:INDIRECT(""D""&ROW()-1)=RC4)/(R10C7:INDIRECT(""G""&ROW()-1)=RC7)),R10C2:INDIRECT(""B""&ROW()-1))+1,1)"
Cells(ActiveCell.Row, 3).FormulaR1C1 = _
"=VLOOKUP(RC1,R10C1:INDIRECT(""I""&ROW()-1),COLUMN(),0)"
With Cells(ActiveCell.Row, 4).VALIDATION
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Cells(ActiveCell.Row, 4).FormulaR1C1 = _
"LIFT"
Cells(ActiveCell.Row, 5).FormulaR1C1 = _
"=VLOOKUP(RC1,R10C1:INDIRECT(""I""&ROW()-1),COLUMN(),0)"
Cells(ActiveCell.Row, 6).FormulaR1C1 = _
"=VLOOKUP(RC1,R10C1:INDIRECT(""I""&ROW()-1),COLUMN(),0)"
Cells(ActiveCell.Row, 7).FormulaR1C1 = _
"=VLOOKUP(RC1,R10C1:INDIRECT(""I""&ROW()-1),COLUMN(),0)"
Cells(ActiveCell.Row, 8).FormulaR1C1 = _
"=VLOOKUP(RC1,R10C1:INDIRECT(""I""&ROW()-1),COLUMN(),0)"
Cells(ActiveCell.Row, 9).FormulaR1C1 = _
"=VLOOKUP(RC1,R10C1:INDIRECT(""I""&ROW()-1),COLUMN(),0)"
With Range("C" & ActiveCell.Row & ":I" & ActiveCell.Row)
.Value = .Value
End With
End Sub