• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 543
  • Last Modified:

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|Total
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:=xlValidAlertStop, Operator:= _
        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:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= _
        "=IF(ActiveCell.offset(0,-1).select=""KIOSK"",QKiosks,IF(ActiveCell.offset(0,-1).select=""Freestanding Portrait"",FSP,IF(ActiveCell.offset(0,-1).select=""Wall Mount"",WallMt,IF(ActiveCell.offset(0,-1).select=""Freestanding Landscape"",FSL,IF(ActiveCell.offset(0,-1).select=""Way Finding"",WayF,IF(ActiveCell.offset(0,-1).select=""End Bank"",EndB,0))))))"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub
0
Patchemus
Asked:
Patchemus
  • 9
  • 7
  • 2
1 Solution
 
Rgonzo1971Commented:
Hi,

Maybe because you use a  VBA Formula instead of an Excel Formula

ActiveCell doesn't work in Excel

Regards
0
 
PatchemusAuthor Commented:
Thank you for your reply The first line was created with excel, the subsequent added lines is handled by VBA.
0
 
Rgonzo1971Commented:
Hi,

What i meant is "=IF(ActiveCell.offset(0,-1).select=""KIOSK"",QKiosks,IF(ActiveCell.offset(0,-1).select=""Freestanding Portrait"",FSP,IF(ActiveCell.offset(0,-1).select=""Wall Mount"",WallMt,IF(ActiveCell.offset(0,-1).select=""Freestanding Landscape"",FSL,IF(ActiveCell.offset(0,-1).select=""Way Finding"",WayF,IF(ActiveCell.offset(0,-1).select=""End Bank"",EndB,0))))))"
cannot work because you have to use an excel formula after formula1

Regards
0
Concerto's Cloud Advisory Services

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.

 
PatchemusAuthor Commented:
Ok Sorry got your point. so. What can I use to get the desired result with VBA?
0
 
SteveCommented:
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.

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))))))"

Open in new window


I am not sure if this will work with validation.
0
 
PatchemusAuthor Commented:
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:=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

WSO.Range("B" & FinalRow).Select
ActiveCell.Offset(1, 0).Select

With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=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))))))"
        .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:=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).Select
ActiveCell.Offset(1, 0).Select.ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-2],PhatDS,3,FALSE)),"",VLOOKUP(RC[-2],PhatDS,3,FALSE))"
 

 
'CALCULATE LINE TOTAL
 
  WSO.Range("F" & FinalRow).Select
  ActiveCell.Offset(1, 0).Select.ActiveCell.FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[-1]),"",RC[-2]*RC[-1])"


   
         
   
End Sub
0
 
SteveCommented:
the last bit is a tad off: the following may do it:

ActiveCell.Offset(1, 0).FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[-1]),"",RC[-2]*RC[-1])"

Open in new window


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])"

Open in new window


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.
0
 
PatchemusAuthor Commented:
Dear The_Barman,

Please see attached workbook. still very stuck
PhatVisionSelfCostSheetexpEx.xlsm
0
 
SteveCommented:
So close.. really.. here is the last line (note the quadrupal quotes)

WSO.Range("F" & FinalRow).FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[-1]),"""",RC[-2]*RC[-1])"

Open in new window


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.
0
 
SteveCommented:
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.

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

Open in new window

0
 
PatchemusAuthor Commented:
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?
0
 
SteveCommented:
I do not think that Offset it your issue, but rather the complexity of the validation lists being created...
have you considered a single static line at the top of the page for selection...
then a button to add that to the list...
then clear the selection...
then select a new item to add to the list...

This will eliminate the need for complex validation in the selected items.

Or have the existing line copy up one... then that line would loose validation... but the validation could move down as you go adding more lines.

these would be simpler from the view of VBA.

Do one of these sound like a way forward?
0
 
PatchemusAuthor Commented:
....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"
0
 
PatchemusAuthor Commented:
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
0
 
SteveCommented:
Hmm
0
 
SteveCommented:
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
...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.
0
 
PatchemusAuthor Commented:
Agreed. Barman's solution was indeed the best for the application and goal for the project.
0
 
PatchemusAuthor Commented:
I think the full points should be allocated to Barman
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now