Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=""KIOSK"",QKiosks,IF(RC[-1]=""Freestanding Portrait"",FSP,IF(RC[-1]=""Wall Mount"",WallMt,IF(RC[-1]=""Freestanding Landscape"",FSL,IF(RC[-1]=""Way Finding"",WayF,IF(RC[-1]=""End Bank"",EndB,0))))))"
ActiveCell.Offset(1, 0).FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[-1]),"",RC[-2]*RC[-1])"
WSO.Range("F" & FinalRow).Offset(1, 0).FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[-1]),"",RC[-2]*RC[-1])"
WSO.Range("F" & FinalRow).FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[-1]),"""",RC[-2]*RC[-1])"
Sub AddAnotherLine()
Dim WBO As Workbook 'original work book
Dim WBN As Workbook 'new workbook'
Dim WSO As Worksheet ' original worksheet
Dim WSN As Worksheet 'new work sheet
Set WBO = ActiveWorkbook
Set WSO = ActiveSheet
FinalRow = WSO.Cells(Rows.Count, 1).End(xlUp).Row
'CREATE VALIDATED DROPDOWN LIST IN FIRST AVAILABLE ROW IN COLUMN A
With WSO.Range("A" & FinalRow + 1).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=categories"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
'CREATE VALIDATION FIRST AVAILABLE ROW IN COLUMN B WITH IF STATEMENT LIST VALIDATION
If FinalRow = 3 Then
With WSO.Range("B" & FinalRow + 1).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=FormulaR1C1 = _
"=IF(RC[-1]="""",RC[-1],OFFSET(INPUTS!R53C1,1,MATCH(RC1,INPUTS!R54C1:R71C1,0),COUNTA(OFFSET(INPUTS!R53C1,1,MATCH(RC1,INPUTS!R54C1:R71C1,0),18,1)),1))"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Else
WSO.Range("B4").Copy
WSO.Range("B" & FinalRow + 1).PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End If
'CREATE VALIDATION FIRST AVAILABLE ROW IN COLUMN B WITH IF STATEMENT LIST VALIDATION C
With WSO.Range("C" & FinalRow +1).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=FormulaR1C1 = _
"=IF(RC[-1]=""Info kiosk only (No Peripherals)"",infKiosk,RC[-1])"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
'LIST THE UNIT PRICE
WSO.Range("E" & FinalRow +1).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-2],PhatDS,3,FALSE)),"",VLOOKUP(RC[-2],PhatDS,3,FALSE))"
'CALCULATE LINE TOTAL
WSO.Range("F" & FinalRow +1 ).FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[-1]),"""",RC[-2]*RC[-1])"
End Sub
...and often the most simple answer is the best. Thank you you are 100% correct.
Maybe because you use a VBA Formula instead of an Excel Formula
ActiveCell doesn't work in Excel
Regards