Patchemus
asked on
Excel Vba. Using If function
Hi experts .
I a am trying to let a user of the worksheet choose 'build their own order'
so the sheet is set up column headings A through to F as the following
Category|Selection and or sub category Product|Quantity|Price|Tot
The row immediately has list validation using name range and a If function
Then there is a Marco button that will add another 'item' (line that will then create the functions in the first row but via VBA and allow the customer to choose different products to complete their order.
So Far I can get the code to validate the first available cell in Column A and move to column B butthis has an If function in the validation which I cannot get to work, the list validation shows the formula and not the dependent validated list that I am after. I am also struggling to refer to a cell based on finalrow and offset
The code is below
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
WSO.Range("A" & FinalRow).Select
ActiveCell.Offset(1, 0).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertSt
xlBetween, Formula1:="=categories"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
ActiveCell.Offset(0, 1).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertSt
xlBetween, Formula1:= _
"=IF(ActiveCell.offset(0,-
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
I a am trying to let a user of the worksheet choose 'build their own order'
so the sheet is set up column headings A through to F as the following
Category|Selection and or sub category Product|Quantity|Price|Tot
The row immediately has list validation using name range and a If function
Then there is a Marco button that will add another 'item' (line that will then create the functions in the first row but via VBA and allow the customer to choose different products to complete their order.
So Far I can get the code to validate the first available cell in Column A and move to column B butthis has an If function in the validation which I cannot get to work, the list validation shows the formula and not the dependent validated list that I am after. I am also struggling to refer to a cell based on finalrow and offset
The code is below
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
WSO.Range("A" & FinalRow).Select
ActiveCell.Offset(1, 0).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertSt
xlBetween, Formula1:="=categories"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
ActiveCell.Offset(0, 1).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertSt
xlBetween, Formula1:= _
"=IF(ActiveCell.offset(0,-
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
ASKER
Thank you for your reply The first line was created with excel, the subsequent added lines is handled by VBA.
Hi,
What i meant is "=IF(ActiveCell.offset(0,-
cannot work because you have to use an excel formula after formula1
Regards
What i meant is "=IF(ActiveCell.offset(0,-
cannot work because you have to use an excel formula after formula1
Regards
ASKER
Ok Sorry got your point. so. What can I use to get the desired result with VBA?
You would need to use RC format for the formula (if you wich to have the formula in the cell)
Or you could calculate in VBA and pop the value in the cell.
Record yourself putting the formula into the cell and this will give the VBA R1C1 verion to use in your code.
I am not sure if this will work with validation.
Or you could calculate in VBA and pop the value in the cell.
Record yourself putting the formula into the cell and this will give the VBA R1C1 verion to use in your code.
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))))))"I am not sure if this will work with validation.
ASKER
Thank you The_Barman. I think I am nearly there. In my validated list I am now getting "False" as the only selectable option instead of the contents of the named range. Also my standard formula at the end is giving a Runtime 424 error. Is is my full code.
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
WSO.Range("A" & FinalRow).Select
ActiveCell.Offset(1, 0).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertSt
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
WSO.Range("B" & FinalRow).Select
ActiveCell.Offset(1, 0).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertSt
xlBetween, Formula1:=FormulaR1C1 = _
"=IF(RC[-1]=""KIOSK"",QKio
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
'CREATE VALIDATION FIRST AVAILABLE ROW IN COLUMN B WITH IF STATEMENT LIST VALIDATION C
WSO.Range("C" & FinalRow).Select
ActiveCell.Offset(1, 0).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertSt
xlBetween, Formula1:=FormulaR1C1 = _
"=IF(RC[-1]=""Info kiosk only (No Peripherals)"",infKiosk,RC
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
'LIST THE UNIT PRICE
WSO.Range("E" & FinalRow).Select
ActiveCell.Offset(1, 0).Select.ActiveCell.Formu
'CALCULATE LINE TOTAL
WSO.Range("F" & FinalRow).Select
ActiveCell.Offset(1, 0).Select.ActiveCell.Formu
End Sub
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
WSO.Range("A" & FinalRow).Select
ActiveCell.Offset(1, 0).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertSt
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
WSO.Range("B" & FinalRow).Select
ActiveCell.Offset(1, 0).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertSt
xlBetween, Formula1:=FormulaR1C1 = _
"=IF(RC[-1]=""KIOSK"",QKio
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
'CREATE VALIDATION FIRST AVAILABLE ROW IN COLUMN B WITH IF STATEMENT LIST VALIDATION C
WSO.Range("C" & FinalRow).Select
ActiveCell.Offset(1, 0).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertSt
xlBetween, Formula1:=FormulaR1C1 = _
"=IF(RC[-1]=""Info kiosk only (No Peripherals)"",infKiosk,RC
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
'LIST THE UNIT PRICE
WSO.Range("E" & FinalRow).Select
ActiveCell.Offset(1, 0).Select.ActiveCell.Formu
'CALCULATE LINE TOTAL
WSO.Range("F" & FinalRow).Select
ActiveCell.Offset(1, 0).Select.ActiveCell.Formu
End Sub
the last bit is a tad off: the following may do it:
Although you should combine the two last lines:
As for creating the Validation list... this I am not 100% certain can be done in this manner.
Could you post a verion of the file so I can test getting it going. Ta.
ActiveCell.Offset(1, 0).FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[-1]),"",RC[-2]*RC[-1])"Although you should combine the two last lines:
WSO.Range("F" & FinalRow).Offset(1, 0).FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[-1]),"",RC[-2]*RC[-1])"As for creating the Validation list... this I am not 100% certain can be done in this manner.
Could you post a verion of the file so I can test getting it going. Ta.
ASKER
So close.. really.. here is the last line (note the quadrupal quotes)
I see the problem with the validation... not filling down as it should.
I am working on that now, but it seems to be a bit of a pain.
WSO.Range("F" & FinalRow).FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[-1]),"""",RC[-2]*RC[-1])"I see the problem with the validation... not filling down as it should.
I am working on that now, but it seems to be a bit of a pain.
OK, this has twisted my noodle... but it seems that there is something weird going on... so will just work around it :)
Try the folowing code which copies the validation down rather than entering it fresh.. it is not perfect but should get you back on track.
Try the folowing code which copies the validation down rather than entering it fresh.. it is not perfect but should get you back on track.
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 SubASKER
Dear Barman, Firstly Apologies for my delayed response. I appreciate all your help. My VBA is very Rusty and actually is limited anyway. I have tried to work with your sample code but seem to still developing errors. Do you think maybe there is a better approach in the first place that does not use offset?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
....and often the most simple answer is the best. Thank you you are 100% correct. By the way I have another open question you may be able to help me with." VBA Add picture to comment where the picture is on another worksheet in the same workbook"
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for Patchemus's comment #a38867372
for the following reason:
Simple Advice can be as useful as in depth solutions
Accepted answer: 0 points for Patchemus's comment #a38867372
for the following reason:
Simple Advice can be as useful as in depth solutions
Hmm
I feel that I have answered the original question... though this did not work out for Patchemus... who then asked for a "better approach"... which was provided.
Patchemus says in closing
So this sounds to me like a "100% correct answer" by definition and statement by Patchemus.
Patchemus says in closing
...and often the most simple answer is the best. Thank you you are 100% correct.
So this sounds to me like a "100% correct answer" by definition and statement by Patchemus.
ASKER
Agreed. Barman's solution was indeed the best for the application and goal for the project.
ASKER
I think the full points should be allocated to Barman
Maybe because you use a VBA Formula instead of an Excel Formula
ActiveCell doesn't work in Excel
Regards