"Object doesn't support this property or method"

Ashutosh2809

New Member
Joined
Jun 26, 2019
Messages
5
My Code is this. I want to check each of 32 Cells in Sheet2 one by one and start copying in sheet3 if they have any value.

Please help

Code:
Dim X As Integer
Dim Y As Integer


Y = 0
X = 32


Do While X > 1
    If ActiveWorkbook.Sheets("Sheet2").Cell(3, X).Value <> "" Then
       ActiveWorkbook.Sheets("Sheet3").Range(1, X).Value = ActiveWorkbook.Sheets("Sheet2").Range(1, X).Value
   Y = Y + 1
End If
   
If Y = 6 Then
Exit Do
End If


X = X - 1


Loop
 
Last edited by a moderator:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
re: "Object doesn't support this property or method"

Hi & welcome to MrExcel.
Change bot instances of
Code:
Range(1,X)
to
Code:
Cells(1,X)
 
Upvote 0
re: "Object doesn't support this property or method"

Hi some code for you to try - test and a copy of your work and add the code to a standard module

Regards
jiuk
Code:
Sub myCOPY()
Dim X As Long
Dim Y As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet

Set ws2 = ThisWorkbook.Worksheets(2)
Set ws3 = ThisWorkbook.Worksheets(3)

Y = 0
X = 32

Do While X > 1
    If ws2.Cells(3, X).Value <> "" Then
       ws3.Cells(1, X).Value = ws2.Cells(1, X).Value
        Y = Y + 1
    End If
   
        If Y = 6 Then
            Exit Do
    End If

    X = X - 1
Loop

theEND:
Set sw2 = Nothing
Set ws3 = Nothing
Exit Sub


End Sub
 
Upvote 0
re: "Object doesn't support this property or method"

What is the complete error code & message, and what line is highlighted if you click "Debug"?
 
Upvote 0
re: "Object doesn't support this property or method"

This line:

Rich (BB code):
If ActiveWorkbook.Sheets("Sheet2").Cell(3, X).Value <> "" Then[/code

should be:


	
	
	
	
	
	


Rich (BB code):
If ActiveWorkbook.Sheets("Sheet2").Cells(3, X).Value <> "" Then
like the previous suggestions.
 
Upvote 0
re: "Object doesn't support this property or method"

I tried with "Cells" too but seems like the loop is not getting initiated. This is my complete code. Its working till the Bold Line.
Code:
Sub Busy_Hour_Last6_Days()
'
' Busy_Hour_Last6_Days Macro
'


'
    Range("D1:ABT30").Select
       Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("J1").Select
    Selection.End(xlDown).Select
    Range("H469").Select
    Selection.End(xlDown).Select
    Range("I551").Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Columns("J:M").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Cells.Select
    Selection.AutoFilter
    Range("C6").Select
    ActiveSheet.Range("$A$1:$AD$745").AutoFilter Field:=2, Criteria1:="20"
    Range("B1:Z742").Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "1st"
    Range("B1").Select
    Selection.AutoFill Destination:=Range("B1:AF1"), Type:=xlFillDefault
    Columns("A:A").EntireColumn.AutoFit
    Columns("A:A").Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Range("B2:H2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Range("B2").Select
    ActiveSheet.Paste
[B]    Range("H1").Select[/B]


Dim X As Integer
Dim Y As Integer


Y = 0
X = 32




Do While X > 1
    If ActiveWorkbook.Sheets("Sheet2").Cells(3, X).Value <> "" Then
       ActiveWorkbook.Sheets("Sheet3").Cells(1, X).Value = ActiveWorkbook.Sheets("Sheet2").Cells(1, X).Value
   Y = Y + 1
End If
   
If Y = 6 Then
Exit Do
End If




X = X - 1
Loop


End Sub
 
Last edited by a moderator:
Upvote 0
re: "Object doesn't support this property or method"

Can you please answer my question from post#5?
 
Upvote 0
There is no error now. Now the code gets compiled and executed. But the objective of writing the loop is not achieved.
Seems like code is not entering the loop.
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

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
Back
Top