VBA For Each naming variable problem

[jrepko11]

Hello,

I am a VBA beginner but I do have a computer programmer helping me with this macro (so I believe the problem is complex). I am having an issue with nested "for each" and what I believe is a problem with naming the variables in it. I am not going to paste the WHOLE macro because it's pretty extensive. All of the specific names (i.e. "NameCell", Workbooks and Worksheets) are defined in earlier lines. The problem is that the internal For Each is not recognizing cells that <> 0 as instructed. It skips all of the copy and paste code and moves to the next cell. I use a "For Each" earlier in the macro and it recognizes the cells just fine. Can I use another "For Each" with the same variable "c"? Here is the code: (sorry for the indent issue)

Dim cat As Range

For Each cat In Range(TargetCell, LastCell)
For Each c In Range(cat.Offset(0, 1), cat.Offset(0, 7))
If c.Value <> 0 Then

'This is all copy and paste code, which I don't think is an issue but I'm not sure becuase it hasn't run it yet.

Range(NameCell).Select
Application.CutCopyMode = False
Selection.Copy
Workbooks.Open DestBook
Sheets(DestSheet).Range("A:A").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(SrvGrpCell).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(DestSheet).Range("B:B").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(TargetCell).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(DestSheet).Range("C:C").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(c.Offset(-1, 0)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(DestSheet).Range("D:D").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(c.Value).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(DestSheet).Range("E:E").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End If
Next c
Next cat

Sorry if this is confusing. Any help is appreciated. Please ask questions if this isn't clear (or if I really should paste the whole macro)

Thanks!

 

[Andrew Poulsom]

This worked for me:

Sub Test()
    Dim TargetCell As Range
    Dim LastCell As Range
    Dim cat As Range
    Dim c As Range
    Set TargetCell = Range("A6")
    Set LastCell = Range("A" & Rows.Count).End(xlUp)
    For Each cat In Range(TargetCell, LastCell)
        For Each c In Range(cat.Offset(0, 1), cat.Offset(0, 7))
            MsgBox c.Value
        Next c
    Next cat
End Sub

Note that I changed the assignment of LastCell.

 

[jrepko11]

I figured out the problem. I did not have the range defined correctly.

Dim TargetCell As Range
Set TargetCell = Range("A6")

Dim LastCell As Range
Set LastCell = Range("A65536").End(xlUp).Offset(-1, 0)

For Each cat In Range(TargetCell, LastCell)
For Each c In Range(cat.Offset(0, 1), cat.Offset(0, 7))
If c.Value > 0 Then
MsgBox (c.Value)
Else
MsgBox ("No data")
End If
Next c
Next cat

The code is now looking at a dymanic list (Targetcell, LastCell) and looping through each cell to the right of it for 6 columns. It currently only displays the value of the cell, but you can put anything after the "If"

I was also having an issue because earlier in the macro I had another workbook opening, which was changing my activesheet. I took that part out...

 

[jrepko11]

This worked for me:

Sub Test()
    Dim TargetCell As Range
    Dim LastCell As Range
    Dim cat As Range
    Dim c As Range
    Set TargetCell = Range("A6")
    Set LastCell = Range("A" & Rows.Count).End(xlUp)
    For Each cat In Range(TargetCell, LastCell)
        For Each c In Range(cat.Offset(0, 1), cat.Offset(0, 7))
            MsgBox c.Value
        Next c
    Next cat
End Sub

Note that I changed the assignment of LastCell.

Sorry, I didn't see this before I posted...but it looks like we were both on the same path...thanks!!

 

[Norie]

What range are you trying to loop through?

 

[Norie]

This might not be relevant but you've got a lot of unqualified range references in the code.

That's not usually a good idea - an unqualified range will refer to what VBA regards as the active sheet.

Combine that with opening the same workbook every time c.Value<>0 and that might just be causing one or two problems.