Rottimad

New Member
Joined
Mar 2, 2015
Messages
19
Hi folks

I've managed to put together my first Userform from advice on here and put together my macro again with information from this site but I have hit a brick wall.

Running the below brings up my form, allows me to enter values (e.g. ExtV = 10 and IntV = 10) but the error message

"Run-time error '13' Type mismatch" appears.

Code:
Sub Enter_Click()
    Dim IntV As Integer
    Dim ExtV As Integer
    Dim RowE As Long
    Dim RowI As Long
    
    Worksheets("VOs").Activate
    
    'Determine the next row dependent on type
    RowE = Sheets("VOs").Application.WorksheetFunction.Match("DECKING", Range("A1:A2000"), 0)
    RowI = Sheets("VOs").Application.WorksheetFunction.CountA(Range("A:A")) + 1
    
    'Select range of rows to insert new rows
    Rows("RowE:RowE+ExtV").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("RowI:RowI+IntV").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    On Error GoTo 0
     
Unload VOrow
VOrow.Hide
End Sub

The highlighted error row is
Rows("RowE:RowE+ExtV").Select
(and I'd assume this will be next)
Rows("RowI:RowI+IntV").Select

Basically I want the value entered in the form for "ExtV" to insert a rows after the word "Decking" appears in column A.
I then want to insert "IntV" number of rows in the next unused cells in the column.

Hence in both instances, needing to know RowI and RowE.

Any help would be greatly appreciated.
 
Last edited:

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Re: Object doesn't support this property or method

Try
RowE = Application.Match("DECKING", Sheets("VOs").Range("A1:A2000"), 0)
 

Rottimad

New Member
Joined
Mar 2, 2015
Messages
19
Re: Object doesn't support this property or method

Hi Jonmo1

Thanks for the prompt reply, its typical sods law that the moment I pressed save on this thread the idea dawned on me it may have been an Application issue.

I have since altered my OP given the next issue I've faced!

Thanks again
 

Rottimad

New Member
Joined
Mar 2, 2015
Messages
19
Re: Object doesn't support this property or method

Anyone have any suggestions?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Re: Object doesn't support this property or method

Perhaps.
Code:
    Range(RowE & ":" & (RowE+ExtV().Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    Range(RowI & ":" & (RowI+IntV)).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 

Rottimad

New Member
Joined
Mar 2, 2015
Messages
19
Re: Object doesn't support this property or method

This debugged the Macro allowing a full completion but it took away the purpose which was to input a certain number of Rows determined by the Userform.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
Re: Object doesn't support this property or method

Which sheet do you want to insert rows in?
 

Rottimad

New Member
Joined
Mar 2, 2015
Messages
19
Re: Object doesn't support this property or method

The sheet is called "VOs", which is also the active sheet when the Userform is used.

Instead of attempting to find and select the row I need, select EntV or IntV quantity of rows after it to THEN insert a number of new rows (which as I can gather, the above is trying to do) is there a simpler way to simply tell the Macro to insert a specific number of rows determined by the user's input in the Userform?

Once I have the sheet inserting a number of rows determined by the user into a consistent location predetermined by me, I can add the remaining formatting/functions into the macro.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,128
Messages
5,599,882
Members
414,343
Latest member
JennyGarcia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top