Solved

Excel Vba. Using If function

Posted on 2013-01-17
18
488 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
  • 9
  • 7
  • 2
18 Comments
 
LVL 48

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 48

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
 

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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 500 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

705 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now