CODEWITHSUNDEEP
vbaexcelexcel-2007
grozhd
grozhd

Reputation: 501

VBA Select method errors

I am new to VBA and basically trying to write my first macro. It is meant to copy entries from one workbook to another omitting repeating values. I have the following code:

Dim s As String

Do While IsEmpty(ActiveCell) = False

If ActiveCell.Value <> s Then
    s = ActiveCell.Value
    ActiveCell.Offset(1, 0).Select
    Windows("Main.xlsm").ActiveCell.Value = s
    Windows("Main.xlsm").ActiveCell.Offset(1, 1).Select


Else
    ActiveCell.Offset(1, 0).Select

End If
Loop

It gives me Runtime error 1004 for this line: 

Windows("Main.xlsm").ActiveCell.Offset(1, 1).Select

Which means that method Select didn't work properly. (I have "Main.xlsm" opened but not active and a cell is selected.) I searched similar questions and found that it may be because I haven't selected a sheet. But somehow this line works:

  Windows("Main.xlsm").ActiveCell.Value = s

And I can see that cell changed in workbook "Main.xlsm". So, I assume that Windows("Main.xlsm").ActiveCell does create a Range object.

Then why does the error happen? And what is the correct way to write this?

Also I tried changing my code to:

    Windows("Main.xlsm").ActiveSheet.ActiveCell.Value = s
    Windows("Main.xlsm").ActiveSheet.ActiveCell.Offset(1, 1).Select

Which gave me: "Runtime Error 438 Object doesn't support this property or method". Why is that? What's incorrect here?

And to:

 Windows("Main.xlsm").Sheets("Name").ActiveCell.Value = s
 Windows("Main.xlsm").Sheets("Name").ActiveCell.Offset(1, 1).Select

Which resulted the same way. And I still don't understand why two last don't work.

This is my first post here so if my editing or anything else is wrong please correct me. Thanks!

Upvotes: 1

Views: 4110

Answers (1)

chris neilsen
chris neilsen

Reputation: 53137

Using ActiveCell, Select etc is not a good idea. Browse through SO Excel tag and you will find many answers advocating against it, many with good explanations why. For one of mine see this question

Instead, declare Range and Worksheet variables and assign these to your ranges and sheets

Dim wbSource as WorkBook
Dim wsSource as WorkSheet

Set wbSource = Application.Workbooks("Main.xlsm")
Set wsSource = wbSource.Worksheets("Name")

To use the workbook the VBA is in, use the ThisWorkbook object

If you want to start with the active sheet, it usuallu OK to use

Dim sh as Worksheet
Set sh = ActiveSheet

To work with a range, use something like

Dim rng as Range

' Set to a specific range
Set rng = Range sh.Range("A1:D10")
' or
With sh
    Set rng = .Range(.Cells(1,1), .Cells(10,4))
End With

' Set to all used cells in a column
With sh
    Set rng = .Range(.Cells(1,1), .Cells(.Rows.Count,1).End(xlUp))
End With

' Adjust rng
Set rng = rng.Offset(1,0)
Set rng = rng.Resize(10,1)

So, to refactor your code fragment 

Dim s As String
Dim rng as Range, cl as Range
Dim shSource as Worksheet
Dim rngDestination as Range

Set shSource = Application.Workbooks("Main.xlsm").Worksheets("SheetName")
Set rngDestination = shSource.Range("A1") ' Change to whatever you need here

Set rng = ActiveCell ' if you must!

' Just in case only one cell entry exists, avoid selecting down to bottom of sheet
If Not IsEmpty(rng.Offset(1,0)) Then
    Set rng = Range(rng, rng.End(xlDown)) ' unqualified Range (ie no sh. part) refers to the active sheet
End IF

For Each cl In rng
    If cl.Value <> s Then
        s = cl.Value
        rngDestination.Value = s
        Set rngDestination = rngDestination.Offset(1, 1) ' should this be .Offset(1, 0) ?
    End If
Next

Upvotes: 1

Related Questions