?
Solved

Excel Vba. Using If function

Posted on 2013-01-17
18
Medium Priority
?
530 Views
Last Modified: 2013-02-09
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
Comment
Question by:Patchemus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 2
18 Comments
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 38791898
Hi,

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

ActiveCell doesn't work in Excel

Regards
0
 

Author Comment

by:Patchemus
ID: 38791931
Thank you for your reply The first line was created with excel, the subsequent added lines is handled by VBA.
0
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 38791953
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 Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Patchemus
ID: 38792180
Ok Sorry got your point. so. What can I use to get the desired result with VBA?
0
 
LVL 24

Expert Comment

by:Steve
ID: 38792588
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
 

Author Comment

by:Patchemus
ID: 38800735
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
 
LVL 24

Expert Comment

by:Steve
ID: 38802295
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
 

Author Comment

by:Patchemus
ID: 38805207
Dear The_Barman,

Please see attached workbook. still very stuck
PhatVisionSelfCostSheetexpEx.xlsm
0
 
LVL 24

Expert Comment

by:Steve
ID: 38805265
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
 
LVL 24

Expert Comment

by:Steve
ID: 38805570
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
 

Author Comment

by:Patchemus
ID: 38848016
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
 
LVL 24

Accepted Solution

by:
Steve earned 1500 total points
ID: 38867140
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
 

Author Comment

by:Patchemus
ID: 38867372
....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
 

Author Comment

by:Patchemus
ID: 38867854
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
 
LVL 24

Expert Comment

by:Steve
ID: 38867696
Hmm
0
 
LVL 24

Expert Comment

by:Steve
ID: 38867855
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
 

Author Comment

by:Patchemus
ID: 38872092
Agreed. Barman's solution was indeed the best for the application and goal for the project.
0
 

Author Comment

by:Patchemus
ID: 38872093
I think the full points should be allocated to Barman
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question