global 'out of range' method error

[akaballa123]

Hi,

I wrote the following code to highlight cells in one workbook fi the corresponding cells in another workbook are highlighted. I get a global 'out of range' method error for the following line: Range(Cells(y, "E")).Select


Set rRange = Range(Cells(x, "N"), Cells(x, "AD"))

For Each rCell In rRange

If rCell.Value <> "" Then

rCell.Select
Selection.Copy

Windows("projectExcelSheet.xls").Activate

For y = 5 To 107

If Application.Workbooks("PM Assignments April 28 Q4 GROW discussions.xls").Worksheets("Sheet1").Cells(x, "N") = rCell.Value Then

Windows("projectExcelSheet.xls").Activate
Range(Cells(y, "E")).Select ' this is where I get the error

Selection.Paste

End If

Next y

End If

Next rCell
Next x

 

[SkipVought]

Hi,

Do not use the Activate and Select methods...

   Set rRange = Range(Cells(x, "N"), Cells(x, "AD"))

        For Each rCell In rRange

            If rCell.Value <> "" Then

              rCell.Copy

              For y = 5 To 107

                If Workbooks("PM Assignments April 28 Q4 GROW discussions.xls").Worksheets("Sheet1").Cells(x, "N") = rCell.Value Then

                    windows("projectExcelSheet.xls").Range(Cells(y, "E")).PasteSpecial

                End If

              Next y

            End If

        Next rCell
  Next x

I'd rather use Workbooks than Windows objects, and explicitly refrence the Worksheet.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[akaballa123]

how can I convert this piece of code to explicitly refer to the two different workbooks:

this is what I think:

Application.Workbooks("PM Assignments April 28 Q4 GROW discussions.xls").Worksheets("Sheet1").Active

Dim rRange As Range
Dim rCell As Range

For x = 4 To 106

' Set rRange = Range(xN, xAD)
' Set rRange = Range("N" & x & ":AD" & x)
Set rRange = Range(Cells(x, "N"), Cells(x, "AD"))

For Each rCell In rRange

If rCell.Value <> "" Then

rCell.Select
Selection.Copy



For y = 5 To 107

If Application.Workbooks("PM Assignments April 28 Q4 GROW discussions.xls").Worksheets("Sheet1").Cells(x, "N") = rCell.Value Then

Application.Workbooks("PM Assignments April 28 Q4 GROW discussions.xls"). _
Worksheets("Sheet1").Cells(y, "E")).Select
Selection.Paste

End If

Next y

End If

Next rCell
Next x



I am 99% sure that what this is still wrong

Thanks

 

[SkipVought]

What TWO workbooks? I only see ONE referenced.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[akaballa123]

Well I have two workbooks.

1)PM Assignments April 28 Q4 GROW discussions
2)projectexcelsheet

Initially im checking 1) then if the condition is met im pasting into 2)

 

[PHV]

A starting point:

Set ws1 = Workbooks.("projectExcelSheet.xls").Worksheets(1)
Set ws2 = Workbooks("PM Assignments April 28 Q4 GROW discussions.xls").Worksheets(1)
For x = 4 To 106
  For Each rCell In Range(ws2.Cells(x, "N"), ws2.Cells(x, "AD"))
    If rCell.Value <> "" Then
      rCell.Copy
      For y = 5 To 107
        If ws2.Cells(x, "N") = rCell.Value Then
          ws1.Cells(y, "E")).Paste
        End If
      Next y
    End If
  Next rCell
Next x

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

NO SELECT!!!

For instance...

    Dim wsPM As Worksheet, wsPROJ As Worksheet
    
    Set wsPM = Workbooks("PM Assignments April 28 Q4 GROW discussions.xls").Worksheets("Sheet1")
    Set wsPROJ = Workbooks("projectExcelSheet.xls").Worksheets(1)
 
    Dim rRange As Range
    Dim rCell As Range
     
    For x = 4 To 106

        ' Set rRange = Range(xN, xAD)
        ' Set rRange = Range("N" & x & ":AD" & x)
        Set rRange = wsPM.Range(Cells(x, "N"), Cells(x, "AD"))

        For Each rCell In rRange

            If rCell.Value <> "" Then

                rCell.Copy
    
                If wsPM.Cells(x, "N") = rCell.Value Then
    
                    wsPROJ.Range(Cells(5, "E"), Cells(107, "E")).PasteSpecial
    
                End If

            End If

        Next rCell
  Next x
  Set wsPM = Nothing
  Set wsPROJ = Nothing

you also do not need the inner loop.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[akaballa123]

hi,

thx for fixing the error. However, Im having several logical errors now, so im trying to debug them. First off, ( im not very good with the syntax) I want to direct my cell range to the next cell in a for each loop if the current cell is empty Apart from the gneral loop i created). This is how i tried doing it:

Dim rCell As Range
Set ws1 = Workbooks("projectExcelSheet.xls").Worksheets(1)
Set ws2 = Workbooks("PM Assignments April 28 Q4 GROW discussions.xls").Worksheets(1)
For x = 4 To 7
For Each rCell In Range(ws2.Cells(x, "N"), ws2.Cells(x, "AD"))
If rCell = "" Then

Next rCell 'this is where I want the loop to go to the next cell

ElseIf rCell <> "" Then
rCell.Copy
For y = 5 To 8
If ws2.Cells(x, "N").Value = rCell.Value Then
ws1.Cells(y, "E").PasteSpecial


I know that It is wrong because if I place the Next keyword then the compiler expects a For too.

Please let me know thanks

 

[SkipVought]

You cannot make rCell go the then next cell. Only the LOOP can do that.

   For Each rCell In Range(ws2.Cells(x, "N"), ws2.Cells(x, "AD"))
    If rCell <> "" Then
      rCell.Copy
      For y = 5 To 8
        If ws2.Cells(x, "N").Value = rCell.Value Then
          ws1.Cells(y, "E").PasteSpecial
        end if
      next
    end if
   next

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}