VBA Run time error 13 type mismatch in Excel 2010 with Datavalidation & VBA

[Maatgie]

I am absolutely new to excel VBA.

I have a requirement to create an excel to maintain status of orders (there are about 6 / 7 order statuses (2 of which are OrderStatus(no status), Enquiry and taken as samples here ) and based on each status, a specific set of actions have to be performed. I have created the excel in which there are multiple columns & rows, some of the columns have data validations either from a reference sheet or entered as list in the Data validation part and some have formula references.

What little coding I could understand, I have done that based on my teeny weeny bit of knowledge + the ideas that I got from checking on various websites. As a result, I am not sure if I have a robust code, although, I have some piece of coding to perform certain actions based on values change in certain columns.

Following are the issues that I want help with:

When inserting / deleting row / rows, get error "Type Mismatch error 13"
The same error appears while certain columns are extended down to the cells.
Same error while cut copy pasting a row
Although the run time error occurs, the action is partially completed

Earliest response would be much appreciated.

My sample excel is attached

 

[Monty]

Maatgie

Welcome to chandoo forum.

It's a great piece of code you have written..

Please find attatched updated one with small modification.

try inserting rows or columns and let me know..Happy to help you.

 

[Maatgie]

Wow!!! that was quick and very helpful... Further to the already asked question,

Just want to know, would it be possible to insert the rows as per the existing data that is being copied from backend.

For example, if I select a particular value, I display and input box, get a value and based on that do some computation and once done, I insert a new line with the values as per my calculation + do some alteration in the existing values and the dropdown value is also changed.

Is it possible to do this VBA code?
I tried the following for that but was not successful n completing the code.

ElseIf Target.Value = "Partfulfilled" Then
  Application.EnableEvents = False
  If (Target.Offset(0, -1).Value = "" Or Target.Offset(0, 47).Value = "" Or Target.Offset(0, 1).Value = "" Or Target.Offset(0, 48).Value = "") Then
  MsgBox ("Please Check the Enquiry, Quotesent, Confirmed or Procured dates")
  Else
'  a = InputBox("Enter Quantity of Fulfilment")
'  b = InputBox("Please confirm to insert partfulfilment rows")
'  If UCase(b) = "Y" Then
'  If Target.Cells.Count > 1 Then Exit Sub
'  If Not Intersect(Target, nRng1) Is Nothing Then
'  'Application.EnableEvents = False
'
'  If Target.Count > 1 Then Exit Sub
'  Set Target = ActiveCell
'  Target.EntireRow.Insert
'  c = Target.Next
'  c.EntireRow.Select
'  Selection.Copy
'  Target.Offset(-1, 0).EntireRow.Select
'  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
'  :=False, Transpose:=False
'  Target.Offset(1, 13).Value = a
'  Target.Offset(0, 13).Value = Target.Offset(0, 13).Value - a
'  Target.Offset(0, 0).Value = "Fulfilled"
'  Target.Offset(-1, 0).Value = "Confirmed"
'  End If
'  'Application.EnableEvents = True
'  End If
  Target.Offset(0, 45) = Date
  Target.Offset(0, 26) = Date
  Target.Offset(0, 50) = "PF"  'Fulfilled
  Target.Offset(0, 51) = ""  'Invoiced
  Target.Offset(0, 52) = ""  'Dropped
  Target.Offset(0, 53) = ""  'Dropped Reason
  End If
  Application.EnableEvents = True
  ElseIf Target.Value = "Invoiced" Then

 

[Monty]

Yes ofcourse, you can Mr. Maatgie

Please let me know if you have challenges..Happy to help you.

 

[Maatgie]

Hello Monty, Thanks a ton!!!

your earlier code worked like a charm and it solved a big problem for me.

for the next query of mine, I have posted the code that I tried but I am a novice and hence unable to check where I may be going wrong. So for now, I have commented the code where I compute.

There are few more clarifications that I wanted to ask.
---> If I manually key in a value in a cell, is it possible to work out some calculations based on the entered value and then put the result in other cells? If so how can I do it.
For example, if I have to calculate P+I , user may enter either of P, N, R, A.
Based on the input, I will need to derive at the missing value.
Could this also happen when a drop down is selected without me having to extend the formula in Excel?

---> There are 3 fields in total, one is a tax field, the other 2 are amount fields, one with Tax and other without Tax. They are mutually exclusive but users may be comfortable entering either of them. I want to know how it can be handled in VBA.

---> I want to be able to handle all formula that I typed in Excel to be running behind the scenes in VBA; is that possible and how to achieve that?

Since I am new here, I dont know if I need to have the code before someone can help me with it. For the above, I dont have a code in place as I could not write it due to my ignorance.

If you can help it would be of great help and also a good learning for me.

 

[Monty]

Maatgie

Am glad that i could help you..
Could you upload a sample file with questions.

Happy to help you.

 

[Maatgie]

I have uploaded a sample file.
----> In this in orderstatus, if the value chosen is partfulfilled from the status procured, then I have to get the quantity that is being filled in an inputbox . After that, I need to copy the existing line and create a new line with the same values and then compute the QTY Field based on the input and update the fields.
----> There are some formulae in my excel sheet, I would like to convert them to the backend so that the users cannot change them at any cost
----> There are some fields that affect these formulae in succession, that is, two or three cell values together will make up the formula for another cell. I want to be able to capture the input event and the formula creation based on this input.
----> In the excel there are 3 fields, Tax,sales price with tax and sales price without tax. At present I have given the option of entering / choosing the Tax % and entering the without tax price. However, there may be cases where the user will know only the with tax price and the tax %. this creates double work for them as they have to compute the without tax price and then paste it in this excel. I want to avoid this double work and give the option for the users to enter price in either of the columns and then be able to compute based on which one is input.
----> Last query for now,
I tried Locking / hiding the formulae for certain fields; however, they dont take effect unless the excel sheet is protected and if I protect the sheet, then users are unable to edit / input their values where necessary. Is there a VBA workaround for this?

I know the list is long. I appreciate the fact that you are trying to help me.
And am great to have found this website. This has been the only place so far where I have got an answer so quickly for my earlier / first query. Thanks a lot again.

Let me know if any further information is required.

 

[Monty]

Try this!

Sorry could not try to understand mentioned questions as running out of time,, but tried to go through the code and fixed..Check if this is what you are looking for.

 

[Maatgie]

Hi Monty,

Thank you very much.

The sample file code does help to an extent. It helps in creating a new row and populating the values of the Orderstatus column alone for these 2 rows (the one copied and the one created).
However, I want to copy the entire row where the order status is chosen as Partfulfilled and create new row with the values and update certain values after that in these 2 rows.