Want to make references consistent

[theta]

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

 

[jasonb75]

As far as I can see this does the same as your original

Sub MESSAGEBOX()
Dim Contract As Variant
ar = ActiveCell.Row
Contract = Application.InputBox("Please enter Contract no", "CONTRACT")
ActiveCell.Value = Contract
Cells(ar, 2).FormulaR1C1 = _
"=IFERROR(LOOKUP(2,1/((R10C1:R" & ar - 1 & "C1=RC1)/(R10C4:R" & ar & "C4=RC4)/(R10C7:R" _
& ar - 1 & "C7=RC7)),R10C2:R" & ar - 1 & "C2)+1,1)"
Cells(ActiveCell.Row, 3).FormulaR1C1 = _
"=VLOOKUP(RC1,R10C1:INDIRECT(""I""&ROW()-1),COLUMN(),0)"
Cells(ar, 4) = "LIFT"
With Range(Cells(ar, 5), Cells(ar, 9))
.Formula = "=VLOOKUP(RC1,R10C1:R" & ar - 1 & "C9,COLUMN(),0)"
.Value = .Value
End With
End Sub

 

[theta]

Thanks

Im learning! slowly