0
votes

I don't think this is a duplicate, however I apologize if it is.

I am very new to VBA, so I was hoping to have someone look over my code and help me troubleshoot the application-defined or object-defined error I am getting on the line below:

Set aCell = ActiveSheet.Range("P10:" & Cells(i, 0).Address) 'Trying to select rows based on value in i starting with P10

Here is the full macro:

Sub freeze2()

Dim ws      As Worksheet
Dim aCell   As Range
Dim strg()  As String
Dim count   As Integer
Dim i       As Integer
Dim size As Integer

size = ActiveSheet.Range("B9:B79").Find(What:=Worksheets("Email Template").Range("B5").Value) 'Looks for current fiscal week

Debug.Print size
count = 0

For i = 1 To size 'should represent current fiscal week back to first week of year, so for this week the range would be 1 to 40
Next i
ReDim numbers(size)

' Get each RED sheet
For Each ws In Worksheets
If ws.Tab.Color = 255 Then                      ' Find only RED tabs
    Debug.Print "-----------------------------------------------------------------------"
    Debug.Print "Name of Red Sheet: '" & ws.Name & "'"        ' Debug...

    Sheets(ws.Name).Select
    Set aCell = ActiveSheet.Range("P10:" & Cells(i, 0).Address) 'Trying to         select rows based on value in i starting with P10
    If Not aCell Is Nothing Then
        Set aCell = Range("H10") 'Taking acell and hopefully copying as values to H10
        'Selection.copy
        'Range("H10").Select
        'Selection.PasteSpecial xlPasteValues
    Else

    End If
    Application.CutCopyMode = False         ' Unselect cell
End If

Next ws

End Sub

All I am trying to do is select a range of cells (always one column however row size will change each week) and copy those values into another column. This range is in the same place on all red sheets.

1
Your reference isn't valid. Cell indexing starts at 1, not 0. - Ryan Wildry
there is no 0 column. Column A is 1 and so on. Change the ActiveSheet.Range("P10:" & Cells(i, 0).Address) to ActiveSheet.Range("P10", ActiveSheet.Cells(i,16)) - Scott Craner
wow, the more I read the less I understand. Your for loop does nothing. the i you reference in the broken line will always be size +1. - Scott Craner
Thanks Ryan, I feel silly. I appreciate the help! - HeatherRW
@Scott Craner was this really necessary? wow, the more I read the less I understand. Not super helpful. - HeatherRW

1 Answers

0
votes

I wanted to update this to show Ryan's edit

Set aCell = ActiveSheet.Range("P10:" & Cells(i, 1).Address) 'Trying to select rows based on value in i starting with P10