?
Solved

Type Selection Mismatch when selecting a range

Posted on 2011-10-26
7
Medium Priority
?
275 Views
Last Modified: 2012-05-12
Hello,

In the attached code, I am trying to select a block of data from a worksheet in one workbook and append it to a worksheet in another workbook.  I'm getting mismatch type error on the following line.
 rng(Selection, Selection.End(xlDown)).Select

Sub AppendReceiptRawData()

    Dim strSourceFilePath As String
    
    strSourceFilePath = GetFile
    
    Dim xlapp As Excel.Application
    Dim xlbook As Excel.Workbook

    Dim s1 As Worksheet
    Dim s2 As Worksheet
    Dim s3 As Worksheet

    Set xlapp = CreateObject("excel.application")
    Set xlbook = xlapp.Workbooks.Open(strSourceFilePath)
    xlapp.Visible = True
    Set s1 = xlbook.Sheets("New Error Transactions Recieved")
    Set s2 = xlbook.Sheets("Summary")
    Set s3 = xlbook.Sheets("Error Count by Message ID")
  
    s1.Activate
    
    s1.Rows("1:3").Select
    With Selection
        .MergeCells = False
    End With
    
    Dim rng As Range
    Set rng = s1.Range("B5")

  'ERROR OCCURS HERE
    rng(Selection, Selection.End(xlDown)).Select
    rng(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    
    Application.WindowState = xlMinimized
    Windows("HIP INVENTORY TRACKING.xls").Activate
    Range("A24034").Select
    ActiveSheet.Paste
    
        
    With Selection
        .MergeCells = False
    End With
    
        
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    
End Sub

Open in new window

0
Comment
Question by:chtullu135
  • 4
  • 3
7 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 37034184
Try this.
Set rng = Range(rng, rng.End(xlDown))

Set rng = Range(rng, rng.End(xlToRight))

rng.Copy

Open in new window

0
 

Author Comment

by:chtullu135
ID: 37034229
I'm now getting the same error at
Set rng = Range(rng, rng.End(xlDown))
I wonder if this is being caused by the fact that I am using late binding.  
0
 
LVL 35

Expert Comment

by:Norie
ID: 37034320
Actually didn't realise this code wasn't in Excel, sorry.

You don't seem to be using late-binding thoguh., if you where tou would get a compile error on Excel.Application etc.

Try preceding Range with s1.
Set rng = s1.Range(rng, rng.End(xlDown))
Set rng = s1.Range(rng, rng.End(xlToRight))
rng.Copy

Open in new window


If that still doesn't work it's probably a good idea to try some other approach.

Assuming you want to copy from B5 down to the last row in column B and across row 5 you could try something like this.
Set rng = s1.Range("B5")

LastRow = s1.Cells(s1.Rows.Count, rng.Column).End(xlUp).Row

LastCol = s1.Cells(rng.Row, s1.Columns.Count).End(xlToLeft).Column

Set rng = rng.Resize(LastRow-rng.Row+1, LastCol-rng.Column+1)

rng.Copy Workbooks("HIP INVENTORY TRACKING.xls").ActiveSheet.Range("A24034")

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:chtullu135
ID: 37034542
When I specified the application object it worked.  
xlapp.Range(rng, rng.End(xlDown)).Select
   xlapp.Range(xlapp.Selection, xlapp.Selection.End(xlToRight)).Select

BTW - you are correct in that early binding was being used
Dim strSourceFilePath As String
    
    strSourceFilePath = GetFile
    
    Dim xlapp As Excel.Application
    Dim xlbook As Excel.Workbook

    Dim s1 As Worksheet
    Dim s2 As Worksheet
    Dim s3 As Worksheet

    Set xlapp = CreateObject("excel.application")
    Set xlbook = xlapp.Workbooks.Open(strSourceFilePath)
    xlapp.Visible = True
    Set s1 = xlbook.Sheets("New Error Transactions Recieved")
    Set s2 = xlbook.Sheets("Summary")
    Set s3 = xlbook.Sheets("Error Count by Message ID")
  
    s1.Activate
    
    s1.Rows("1:3").Select
    With Selection
        .MergeCells = False
    End With
    
    Dim rng As Range
    Set rng = s1.Range("B5")

   xlapp.Range(rng, rng.End(xlDown)).Select
   xlapp.Range(xlapp.Selection, xlapp.Selection.End(xlToRight)).Select
     xlapp.CutCopyMode = xlCopy
   
    xlapp.Selection.Copy

Open in new window

0
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 37034784
Glad it worked.

However you really shouldn't use Select or Selection.

If this code was in Excel you wouldn't use them and since you appear to be automating Excel you definitely shouldn't use them.
0
 

Author Comment

by:chtullu135
ID: 37041667
I get your point about avoiding the use of select or selection where possible and keep that in mind for the future.
0
 

Author Closing Comment

by:chtullu135
ID: 37041677
Thanks for the tip about the use of selection
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

839 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