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]

Welcome to MrExcel.

What are TargetCell and LastCell? What sort of data is in the range that you are looping through? Why do you need 2 loops?

 

[jrepko11]

Thank you for a quick response!

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

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

I have the sheet set up so it will act as an interface for people to enter time spent on different categories. Down column A are different categories they can track (which can be added to, hence the "LastCell" definition). Column B-H contain dates (this Sunday through this Saturday) and for each category they will enter hours spent on that particular day.

I want the nested "For each" to look at each day (cell) in each category (row). I wanted to loop all these to avoid writing a ton of code for each individual category that will be in column A.

As a side note: I am using the least amount of "hard coding" as possible since the sheet could be changed. I.E. no Range = "A4:A7" but rather as an offset of ActiveCell or TargetCell.

Thank you!!!

 

[Andrew Poulsom]

OK, so the loops should work. What's actually in the cells in columns B:H? It must be a number to be not equal to zero.

 

[jrepko11]

There are numbers in the cells currently (formated as "number" as well) so I don't know why it's not recognizing them. Would they have to be numbers all the time? For convenience sake I would like users to be able to leave cells blank and have it act like it = 0.

Any ideas to why it's not working now? I don't think it's recognizing the numbers, but could it be my following code with the copy and pasting?

 

[Andrew Poulsom]

Are you sure that they are numbers? Use the ISNUMBER function to check.

 

[jrepko11]

Yes, the ISNUMBER function answers true for all the cells in the range (cat.Offset(0,1), cat.Offset(0,7)). That is, of course, if it is defined correctly. I think it is....

 

[Andrew Poulsom]

So the test should work. How do you know that it isn't?

 

[jrepko11]

When testing the code, it highlights the two "For each" lines. When it gets to "If c.Value <> 0 Then", I step into the next line, and instead of going to my next line of code ("Range(NameCell).Select"), it skips the whole section and goes straight to "End If" at the bottom. This is correct if the number in the cell = 0, but none of them do.

I'm assuming that some part of my "copy/paste" part of the code is incorrect (since I'm a beginner), but it doesn't even test it out so I can see--it just skips it entirely like the cell = 0.

 

[jrepko11]

I think the problem is with the range/cell definition. I did this to test the code:

Dim cat As Range

For Each cat In Range(TargetCell, LastCell)
For Each c In Range(cat.Offset(0, 1), cat.Offset(0, 7))
MsgBox (c.Value)

'If c.Value > 0 Then

That showed me the LastCell highlighted and a message box with no value in it. I want it to loop through every cell in that range (across the row) and determine if the value is greater than 0.

Is it possible to determine the value of a single cell in a range where the cell is defined as a range?

Any ideas?????