I have a macro that loops from column A to column IV(the last column in
Excel). Once the last column is read how can I cause my macro to restart at
the column A again? Can I set my macro to return to column A after it reads
column IV. What would be the macro syntax for that? Thanks.

Maybe you could just loop between column 1 and the maximum column:

dim cCtr as long
for cctr = 1 to activesheet.columns.count
'do something
next cctr

Say you want to loop through rows looking through columns:

dim cCtr as long
dim rCtr as long

for rctr = 1 to 10
for cctr = 1 to activesheet.columns.count
msgbox activesheet.cells(rctr,cctr).value
next cctr
next rctr

=====
If this doesn't help (and I'd be kind of surprised if it did!), you may want to
add some more detail to your question.

BR wrote:

I have a macro that loops from column A to column IV(the last column in
Excel). Once the last column is read how can I cause my macro to restart at
the column A again? Can I set my macro to return to column A after it reads
column IV. What would be the macro syntax for that? Thanks.

--

Dave Peterson

Thanks. I am just trying to get my cursor back to the beginning of my
worksheet after my macro has moved it to the last column, by way of the macro
loop, without having to manually move the cursor. Where would I place your
code?

"Dave Peterson" wrote:

Maybe you could just loop between column 1 and the maximum column:

dim cCtr as long
for cctr = 1 to activesheet.columns.count
'do something
next cctr

Say you want to loop through rows looking through columns:

dim cCtr as long
dim rCtr as long

for rctr = 1 to 10
for cctr = 1 to activesheet.columns.count
msgbox activesheet.cells(rctr,cctr).value
next cctr
next rctr

=====
If this doesn't help (and I'd be kind of surprised if it did!), you may want to
add some more detail to your question.

BR wrote:

I have a macro that loops from column A to column IV(the last column in
Excel). Once the last column is read how can I cause my macro to restart at
the column A again? Can I set my macro to return to column A after it reads
column IV. What would be the macro syntax for that? Thanks.

--

Dave Peterson

I don't quite understand what you're doing, but it sounds like you're selecting
cells. It's not usually necessary to select cells to work on them.

But you could use

if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if



BR wrote:

Thanks. I am just trying to get my cursor back to the beginning of my
worksheet after my macro has moved it to the last column, by way of the macro
loop, without having to manually move the cursor. Where would I place your
code?

"Dave Peterson" wrote:

Maybe you could just loop between column 1 and the maximum column:

dim cCtr as long
for cctr = 1 to activesheet.columns.count
'do something
next cctr

Say you want to loop through rows looking through columns:

dim cCtr as long
dim rCtr as long

for rctr = 1 to 10
for cctr = 1 to activesheet.columns.count
msgbox activesheet.cells(rctr,cctr).value
next cctr
next rctr

=====
If this doesn't help (and I'd be kind of surprised if it did!), you may want to
add some more detail to your question.

BR wrote:

I have a macro that loops from column A to column IV(the last column in
Excel). Once the last column is read how can I cause my macro to restart at
the column A again? Can I set my macro to return to column A after it reads
column IV. What would be the macro syntax for that? Thanks.

--

Dave Peterson


--

Dave Peterson

The following is a copy of my macro. Where would I place your code to make my
cursor automatically loop back to the first column of my spreadsheet after
the mac has read the very last column in the spreadsheet?

Function FunctNextCookie(Optional blnHide As Boolean)
Sheets("C_Data").Select
Cells(3, ActiveCell.Column).Select
' Moves cursor to row 3.
ActiveCell.Offset(0, 1).Range("A1").Select
' Selects next SKU.
Selection.Copy
Sheets("Cookie").Select
Range("C5").PasteSpecial Paste:=xlValues
' Does not HideZeroUsage if called by ReportAll.
If blnHide = True Then HideZeroUsage

End Function

"Dave Peterson" wrote:

I don't quite understand what you're doing, but it sounds like you're selecting
cells. It's not usually necessary to select cells to work on them.

But you could use

if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if



BR wrote:

Thanks. I am just trying to get my cursor back to the beginning of my
worksheet after my macro has moved it to the last column, by way of the macro
loop, without having to manually move the cursor. Where would I place your
code?

"Dave Peterson" wrote:

Maybe you could just loop between column 1 and the maximum column:

dim cCtr as long
for cctr = 1 to activesheet.columns.count
'do something
next cctr

Say you want to loop through rows looking through columns:

dim cCtr as long
dim rCtr as long

for rctr = 1 to 10
for cctr = 1 to activesheet.columns.count
msgbox activesheet.cells(rctr,cctr).value
next cctr
next rctr

=====
If this doesn't help (and I'd be kind of surprised if it did!), you may want to
add some more detail to your question.

BR wrote:

I have a macro that loops from column A to column IV(the last column in
Excel). Once the last column is read how can I cause my macro to restart at
the column A again? Can I set my macro to return to column A after it reads
column IV. What would be the macro syntax for that? Thanks.

--

Dave Peterson


--

Dave Peterson

Is there a simply way to have the cursor in an Excel spreadsheet, once it
reaches a certain cell due to macro movement, to automatically jump back to
Column A.

"Dave Peterson" wrote:

I don't quite understand what you're doing, but it sounds like you're selecting
cells. It's not usually necessary to select cells to work on them.

But you could use

if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if



BR wrote:

Thanks. I am just trying to get my cursor back to the beginning of my
worksheet after my macro has moved it to the last column, by way of the macro
loop, without having to manually move the cursor. Where would I place your
code?

"Dave Peterson" wrote:

Maybe you could just loop between column 1 and the maximum column:

dim cCtr as long
for cctr = 1 to activesheet.columns.count
'do something
next cctr

Say you want to loop through rows looking through columns:

dim cCtr as long
dim rCtr as long

for rctr = 1 to 10
for cctr = 1 to activesheet.columns.count
msgbox activesheet.cells(rctr,cctr).value
next cctr
next rctr

=====
If this doesn't help (and I'd be kind of surprised if it did!), you may want to
add some more detail to your question.

BR wrote:

I have a macro that loops from column A to column IV(the last column in
Excel). Once the last column is read how can I cause my macro to restart at
the column A again? Can I set my macro to return to column A after it reads
column IV. What would be the macro syntax for that? Thanks.

--

Dave Peterson


--

Dave Peterson

Maybe...

Option Explicit

Function FunctNextCookie(Optional blnHide As Boolean)
Sheets("C_Data").Select
Cells(3, ActiveCell.Column).Select
' Moves cursor to row 3.
If ActiveCell.Column = ActiveSheet.Columns.Count Then
ActiveSheet.Cells(ActiveCell.Row + 1, 1).Select
Else
ActiveCell.Offset(0, 1).Range("A1").Select
End If
' Selects next SKU.
Selection.Copy
Sheets("Cookie").Select
Range("C5").PasteSpecial Paste:=xlValues
' Does not HideZeroUsage if called by ReportAll.
If blnHide = True Then HideZeroUsage

End Function



BR wrote:

The following is a copy of my macro. Where would I place your code to make my
cursor automatically loop back to the first column of my spreadsheet after
the mac has read the very last column in the spreadsheet?

Function FunctNextCookie(Optional blnHide As Boolean)
Sheets("C_Data").Select
Cells(3, ActiveCell.Column).Select
' Moves cursor to row 3.
ActiveCell.Offset(0, 1).Range("A1").Select
' Selects next SKU.
Selection.Copy
Sheets("Cookie").Select
Range("C5").PasteSpecial Paste:=xlValues
' Does not HideZeroUsage if called by ReportAll.
If blnHide = True Then HideZeroUsage

End Function

"Dave Peterson" wrote:

I don't quite understand what you're doing, but it sounds like you're selecting
cells. It's not usually necessary to select cells to work on them.

But you could use

if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if



BR wrote:

Thanks. I am just trying to get my cursor back to the beginning of my
worksheet after my macro has moved it to the last column, by way of the macro
loop, without having to manually move the cursor. Where would I place your
code?

"Dave Peterson" wrote:

Maybe you could just loop between column 1 and the maximum column:

dim cCtr as long
for cctr = 1 to activesheet.columns.count
'do something
next cctr

Say you want to loop through rows looking through columns:

dim cCtr as long
dim rCtr as long

for rctr = 1 to 10
for cctr = 1 to activesheet.columns.count
msgbox activesheet.cells(rctr,cctr).value
next cctr
next rctr

=====
If this doesn't help (and I'd be kind of surprised if it did!), you may want to
add some more detail to your question.

BR wrote:

I have a macro that loops from column A to column IV(the last column in
Excel). Once the last column is read how can I cause my macro to restart at
the column A again? Can I set my macro to return to column A after it reads
column IV. What would be the macro syntax for that? Thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

You might be able to use a worksheet_change event.

But I think that most people would shy away from selecting cells in their code.



BR wrote:

Is there a simply way to have the cursor in an Excel spreadsheet, once it
reaches a certain cell due to macro movement, to automatically jump back to
Column A.

"Dave Peterson" wrote:

I don't quite understand what you're doing, but it sounds like you're selecting
cells. It's not usually necessary to select cells to work on them.

But you could use

if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if



BR wrote:

Thanks. I am just trying to get my cursor back to the beginning of my
worksheet after my macro has moved it to the last column, by way of the macro
loop, without having to manually move the cursor. Where would I place your
code?

"Dave Peterson" wrote:

Maybe you could just loop between column 1 and the maximum column:

dim cCtr as long
for cctr = 1 to activesheet.columns.count
'do something
next cctr

Say you want to loop through rows looking through columns:

dim cCtr as long
dim rCtr as long

for rctr = 1 to 10
for cctr = 1 to activesheet.columns.count
msgbox activesheet.cells(rctr,cctr).value
next cctr
next rctr

=====
If this doesn't help (and I'd be kind of surprised if it did!), you may want to
add some more detail to your question.

BR wrote:

I have a macro that loops from column A to column IV(the last column in
Excel). Once the last column is read how can I cause my macro to restart at
the column A again? Can I set my macro to return to column A after it reads
column IV. What would be the macro syntax for that? Thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Where would I place your code to make it work in my macro?

Your code:
if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if


"Dave Peterson" wrote:

You might be able to use a worksheet_change event.

But I think that most people would shy away from selecting cells in their code.



BR wrote:

Is there a simply way to have the cursor in an Excel spreadsheet, once it
reaches a certain cell due to macro movement, to automatically jump back to
Column A.

"Dave Peterson" wrote:

I don't quite understand what you're doing, but it sounds like you're selecting
cells. It's not usually necessary to select cells to work on them.

But you could use

if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if



BR wrote:

Thanks. I am just trying to get my cursor back to the beginning of my
worksheet after my macro has moved it to the last column, by way of the macro
loop, without having to manually move the cursor. Where would I place your
code?

"Dave Peterson" wrote:

Maybe you could just loop between column 1 and the maximum column:

dim cCtr as long
for cctr = 1 to activesheet.columns.count
'do something
next cctr

Say you want to loop through rows looking through columns:

dim cCtr as long
dim rCtr as long

for rctr = 1 to 10
for cctr = 1 to activesheet.columns.count
msgbox activesheet.cells(rctr,cctr).value
next cctr
next rctr

=====
If this doesn't help (and I'd be kind of surprised if it did!), you may want to
add some more detail to your question.

BR wrote:

I have a macro that loops from column A to column IV(the last column in
Excel). Once the last column is read how can I cause my macro to restart at
the column A again? Can I set my macro to return to column A after it reads
column IV. What would be the macro syntax for that? Thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Check the other branch of this thread.



BR wrote:

Where would I place your code to make it work in my macro?

Your code:
if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if

"Dave Peterson" wrote:

You might be able to use a worksheet_change event.

But I think that most people would shy away from selecting cells in their code.



BR wrote:

Is there a simply way to have the cursor in an Excel spreadsheet, once it
reaches a certain cell due to macro movement, to automatically jump back to
Column A.

"Dave Peterson" wrote:

I don't quite understand what you're doing, but it sounds like you're selecting
cells. It's not usually necessary to select cells to work on them.

But you could use

if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if



BR wrote:

Thanks. I am just trying to get my cursor back to the beginning of my
worksheet after my macro has moved it to the last column, by way of the macro
loop, without having to manually move the cursor. Where would I place your
code?

"Dave Peterson" wrote:

Maybe you could just loop between column 1 and the maximum column:

dim cCtr as long
for cctr = 1 to activesheet.columns.count
'do something
next cctr

Say you want to loop through rows looking through columns:

dim cCtr as long
dim rCtr as long

for rctr = 1 to 10
for cctr = 1 to activesheet.columns.count
msgbox activesheet.cells(rctr,cctr).value
next cctr
next rctr

=====
If this doesn't help (and I'd be kind of surprised if it did!), you may want to
add some more detail to your question.

BR wrote:

I have a macro that loops from column A to column IV(the last column in
Excel). Once the last column is read how can I cause my macro to restart at
the column A again? Can I set my macro to return to column A after it reads
column IV. What would be the macro syntax for that? Thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

We are getting so close. When my macro gets to the last cell of data and then
tries to move to the first blank cell, I get the following error:

Case ActiveCell.Value = Error 2024

on this part of the mac code:

Do
Select Case True
Case ActiveCell.Value = 0
Selection.EntireRow.Hidden = True
Case ActiveCell.Offset(0, -3).Value = ""
Selection.EntireRow.Hidden = True
End Select
ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell.Offset(0, -3).Value < 0

"Dave Peterson" wrote:

Maybe...

Option Explicit

Function FunctNextCookie(Optional blnHide As Boolean)
Sheets("C_Data").Select
Cells(3, ActiveCell.Column).Select
' Moves cursor to row 3.
If ActiveCell.Column = ActiveSheet.Columns.Count Then
ActiveSheet.Cells(ActiveCell.Row + 1, 1).Select
Else
ActiveCell.Offset(0, 1).Range("A1").Select
End If
' Selects next SKU.
Selection.Copy
Sheets("Cookie").Select
Range("C5").PasteSpecial Paste:=xlValues
' Does not HideZeroUsage if called by ReportAll.
If blnHide = True Then HideZeroUsage

End Function



BR wrote:

The following is a copy of my macro. Where would I place your code to make my
cursor automatically loop back to the first column of my spreadsheet after
the mac has read the very last column in the spreadsheet?

Function FunctNextCookie(Optional blnHide As Boolean)
Sheets("C_Data").Select
Cells(3, ActiveCell.Column).Select
' Moves cursor to row 3.
ActiveCell.Offset(0, 1).Range("A1").Select
' Selects next SKU.
Selection.Copy
Sheets("Cookie").Select
Range("C5").PasteSpecial Paste:=xlValues
' Does not HideZeroUsage if called by ReportAll.
If blnHide = True Then HideZeroUsage

End Function

"Dave Peterson" wrote:

I don't quite understand what you're doing, but it sounds like you're selecting
cells. It's not usually necessary to select cells to work on them.

But you could use

if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if



BR wrote:

Thanks. I am just trying to get my cursor back to the beginning of my
worksheet after my macro has moved it to the last column, by way of the macro
loop, without having to manually move the cursor. Where would I place your
code?

"Dave Peterson" wrote:

Maybe you could just loop between column 1 and the maximum column:

dim cCtr as long
for cctr = 1 to activesheet.columns.count
'do something
next cctr

Say you want to loop through rows looking through columns:

dim cCtr as long
dim rCtr as long

for rctr = 1 to 10
for cctr = 1 to activesheet.columns.count
msgbox activesheet.cells(rctr,cctr).value
next cctr
next rctr

=====
If this doesn't help (and I'd be kind of surprised if it did!), you may want to
add some more detail to your question.

BR wrote:

I have a macro that loops from column A to column IV(the last column in
Excel). Once the last column is read how can I cause my macro to restart at
the column A again? Can I set my macro to return to column A after it reads
column IV. What would be the macro syntax for that? Thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

We are getting so close. When my macro gets to the last cell of data and then
tries to move to the first blank cell, I get the following error:

Case ActiveCell.Value = Error 2024

on this part of the mac code:

Do
Select Case True
Case ActiveCell.Value = 0
Selection.EntireRow.Hidden = True
Case ActiveCell.Offset(0, -3).Value = ""
Selection.EntireRow.Hidden = True
End Select
ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell.Offset(0, -3).Value < 0



"Dave Peterson" wrote:

Check the other branch of this thread.



BR wrote:

Where would I place your code to make it work in my macro?

Your code:
if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if

"Dave Peterson" wrote:

You might be able to use a worksheet_change event.

But I think that most people would shy away from selecting cells in their code.



BR wrote:

Is there a simply way to have the cursor in an Excel spreadsheet, once it
reaches a certain cell due to macro movement, to automatically jump back to
Column A.

"Dave Peterson" wrote:

I don't quite understand what you're doing, but it sounds like you're selecting
cells. It's not usually necessary to select cells to work on them.

But you could use

if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if



BR wrote:

Thanks. I am just trying to get my cursor back to the beginning of my
worksheet after my macro has moved it to the last column, by way of the macro
loop, without having to manually move the cursor. Where would I place your
code?

"Dave Peterson" wrote:

Maybe you could just loop between column 1 and the maximum column:

dim cCtr as long
for cctr = 1 to activesheet.columns.count
'do something
next cctr

Say you want to loop through rows looking through columns:

dim cCtr as long
dim rCtr as long

for rctr = 1 to 10
for cctr = 1 to activesheet.columns.count
msgbox activesheet.cells(rctr,cctr).value
next cctr
next rctr

=====
If this doesn't help (and I'd be kind of surprised if it did!), you may want to
add some more detail to your question.

BR wrote:

I have a macro that loops from column A to column IV(the last column in
Excel). Once the last column is read how can I cause my macro to restart at
the column A again? Can I set my macro to return to column A after it reads
column IV. What would be the macro syntax for that? Thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

That's the error you get?

Or are you saying that you have an error in that activecell?

Maybe...

Do
Select Case True
case iserror(activecell.value)
'do something else??
Case ActiveCell.Value = 0
Selection.EntireRow.Hidden = True
Case ActiveCell.Offset(0, -3).Value = ""
Selection.EntireRow.Hidden = True
End Select
ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell.Offset(0, -3).Value < 0

BR wrote:

We are getting so close. When my macro gets to the last cell of data and then
tries to move to the first blank cell, I get the following error:

Case ActiveCell.Value = Error 2024

on this part of the mac code:

Do
Select Case True
Case ActiveCell.Value = 0
Selection.EntireRow.Hidden = True
Case ActiveCell.Offset(0, -3).Value = ""
Selection.EntireRow.Hidden = True
End Select
ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell.Offset(0, -3).Value < 0

"Dave Peterson" wrote:

Check the other branch of this thread.



BR wrote:

Where would I place your code to make it work in my macro?

Your code:
if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if

"Dave Peterson" wrote:

You might be able to use a worksheet_change event.

But I think that most people would shy away from selecting cells in their code.



BR wrote:

Is there a simply way to have the cursor in an Excel spreadsheet, once it
reaches a certain cell due to macro movement, to automatically jump back to
Column A.

"Dave Peterson" wrote:

I don't quite understand what you're doing, but it sounds like you're selecting
cells. It's not usually necessary to select cells to work on them.

But you could use

if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if



BR wrote:

Thanks. I am just trying to get my cursor back to the beginning of my
worksheet after my macro has moved it to the last column, by way of the macro
loop, without having to manually move the cursor. Where would I place your
code?

"Dave Peterson" wrote:

Maybe you could just loop between column 1 and the maximum column:

dim cCtr as long
for cctr = 1 to activesheet.columns.count
'do something
next cctr

Say you want to loop through rows looking through columns:

dim cCtr as long
dim rCtr as long

for rctr = 1 to 10
for cctr = 1 to activesheet.columns.count
msgbox activesheet.cells(rctr,cctr).value
next cctr
next rctr

=====
If this doesn't help (and I'd be kind of surprised if it did!), you may want to
add some more detail to your question.

BR wrote:

I have a macro that loops from column A to column IV(the last column in
Excel). Once the last column is read how can I cause my macro to restart at
the column A again? Can I set my macro to return to column A after it reads
column IV. What would be the macro syntax for that? Thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

If I use my mouse to hoover the cursor over Case ActiveCell.Value = 0, then
the error pops up in a box that disappears when I move my curosr. There is no
error in the actual cell, itself.

"Dave Peterson" wrote:

That's the error you get?

Or are you saying that you have an error in that activecell?

Maybe...

Do
Select Case True
case iserror(activecell.value)
'do something else??
Case ActiveCell.Value = 0
Selection.EntireRow.Hidden = True
Case ActiveCell.Offset(0, -3).Value = ""
Selection.EntireRow.Hidden = True
End Select
ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell.Offset(0, -3).Value < 0

BR wrote:

We are getting so close. When my macro gets to the last cell of data and then
tries to move to the first blank cell, I get the following error:

Case ActiveCell.Value = Error 2024

on this part of the mac code:

Do
Select Case True
Case ActiveCell.Value = 0
Selection.EntireRow.Hidden = True
Case ActiveCell.Offset(0, -3).Value = ""
Selection.EntireRow.Hidden = True
End Select
ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell.Offset(0, -3).Value < 0

"Dave Peterson" wrote:

Check the other branch of this thread.



BR wrote:

Where would I place your code to make it work in my macro?

Your code:
if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if

"Dave Peterson" wrote:

You might be able to use a worksheet_change event.

But I think that most people would shy away from selecting cells in their code.



BR wrote:

Is there a simply way to have the cursor in an Excel spreadsheet, once it
reaches a certain cell due to macro movement, to automatically jump back to
Column A.

"Dave Peterson" wrote:

I don't quite understand what you're doing, but it sounds like you're selecting
cells. It's not usually necessary to select cells to work on them.

But you could use

if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if



BR wrote:

Thanks. I am just trying to get my cursor back to the beginning of my
worksheet after my macro has moved it to the last column, by way of the macro
loop, without having to manually move the cursor. Where would I place your
code?

"Dave Peterson" wrote:

Maybe you could just loop between column 1 and the maximum column:

dim cCtr as long
for cctr = 1 to activesheet.columns.count
'do something
next cctr

Say you want to loop through rows looking through columns:

dim cCtr as long
dim rCtr as long

for rctr = 1 to 10
for cctr = 1 to activesheet.columns.count
msgbox activesheet.cells(rctr,cctr).value
next cctr
next rctr

=====
If this doesn't help (and I'd be kind of surprised if it did!), you may want to
add some more detail to your question.

BR wrote:

I have a macro that loops from column A to column IV(the last column in
Excel). Once the last column is read how can I cause my macro to restart at
the column A again? Can I set my macro to return to column A after it reads
column IV. What would be the macro syntax for that? Thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I'd look again at that activecell.

I bet it has an error in it.

BR wrote:

If I use my mouse to hoover the cursor over Case ActiveCell.Value = 0, then
the error pops up in a box that disappears when I move my curosr. There is no
error in the actual cell, itself.

"Dave Peterson" wrote:

That's the error you get?

Or are you saying that you have an error in that activecell?

Maybe...

Do
Select Case True
case iserror(activecell.value)
'do something else??
Case ActiveCell.Value = 0
Selection.EntireRow.Hidden = True
Case ActiveCell.Offset(0, -3).Value = ""
Selection.EntireRow.Hidden = True
End Select
ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell.Offset(0, -3).Value < 0

BR wrote:

We are getting so close. When my macro gets to the last cell of data and then
tries to move to the first blank cell, I get the following error:

Case ActiveCell.Value = Error 2024

on this part of the mac code:

Do
Select Case True
Case ActiveCell.Value = 0
Selection.EntireRow.Hidden = True
Case ActiveCell.Offset(0, -3).Value = ""
Selection.EntireRow.Hidden = True
End Select
ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell.Offset(0, -3).Value < 0

"Dave Peterson" wrote:

Check the other branch of this thread.



BR wrote:

Where would I place your code to make it work in my macro?

Your code:
if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if

"Dave Peterson" wrote:

You might be able to use a worksheet_change event.

But I think that most people would shy away from selecting cells in their code.



BR wrote:

Is there a simply way to have the cursor in an Excel spreadsheet, once it
reaches a certain cell due to macro movement, to automatically jump back to
Column A.

"Dave Peterson" wrote:

I don't quite understand what you're doing, but it sounds like you're selecting
cells. It's not usually necessary to select cells to work on them.

But you could use

if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if



BR wrote:

Thanks. I am just trying to get my cursor back to the beginning of my
worksheet after my macro has moved it to the last column, by way of the macro
loop, without having to manually move the cursor. Where would I place your
code?

"Dave Peterson" wrote:

Maybe you could just loop between column 1 and the maximum column:

dim cCtr as long
for cctr = 1 to activesheet.columns.count
'do something
next cctr

Say you want to loop through rows looking through columns:

dim cCtr as long
dim rCtr as long

for rctr = 1 to 10
for cctr = 1 to activesheet.columns.count
msgbox activesheet.cells(rctr,cctr).value
next cctr
next rctr

=====
If this doesn't help (and I'd be kind of surprised if it did!), you may want to
add some more detail to your question.

BR wrote:

I have a macro that loops from column A to column IV(the last column in
Excel). Once the last column is read how can I cause my macro to restart at
the column A again? Can I set my macro to return to column A after it reads
column IV. What would be the macro syntax for that? Thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I tried your code. I think that we are getting very close. When I run my
macro to the last column of data, the next(blank) cell is activated but the
macro errors. My entire mac code is as follows:

Option Explicit
' How do I return to memory the 'puter power that is used
' by my variables?

' Module Name: NextProduct

' CONTENTS OF THIS MODULE
' 1. NextCookie [Ctrl+Shift+C]
' Calls FunctNextCookie frm keyboard;
' instructs it to un/hide ingreds.
' 1f. FunctNextCookie - FUNCTION
' Runs next SKU thru cookie cost template;
' un/hides rows to display ingreds if so instructed.
' 2. NextCookie [Ctrl+Shift+D]
' Calls FunctNextCookie frm keyboard;
' instructs it to un/hide ingreds.
' 2f. FunctNextCookie - FUNCTION
' Runs next SKU thru cookie cost template;
' un/hides rows to display ingreds if so instructed.
' 3. NextMuffin [Ctrl+Shift+M]
' Calls FunctNextMuffin frm keyboard;
' instructs it to un/hide ingreds.
' 3f. FunctNextMuffin - FUNCTION
' Runs next SKU thru muffin cost template;
' un/hides rows to display ingreds if so instructed.
' 4. PrintAllProducts [Ctrl+Shift+A]
' Runs each product & prints a copy.
' 5. HideZeroUsage
' Hides any raw & pkg ingrediant items w/ 0 usage for
' currently displayed SKU.

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
Sub NextCookie()
' Macro recorded 1/3/2002, revised 12/5/2005
' by Bill Riley.
'
' Keyboard Shortcut: Ctrl+Shift+C
'
' Will select C_Data sheet, move cursor one cell to right,
' copy, and paste value on cell C5 of Cookie sheet.
'
' Invokes HideZeroUsage.
FunctNextCookie (True)
End Sub

Function FunctNextCookie(Optional blnHide As Boolean)
Sheets("C_Data").Select
Cells(3, ActiveCell.Column).Select
' Moves cursor to row 3.
ActiveCell.Offset(0, 1).Range("A1").Select
'Selects next SKU.
Selection.Copy
Sheets("Cookie").Select
Range("C5").PasteSpecial Paste:=xlValues
' Does not HideZeroUsage if called by ReportAll.
If blnHide = True Then HideZeroUsage
End Function

Sub NextCookie2()
' Macro recorded 1/3/2002, revised 12/5/2005
' by Bill Riley.
'
' Keyboard Shortcut: Ctrl+Shift+D
'
' Will select C_Data2 sheet, move cursor one cell to right,
' copy, and paste value on cell C5 of Cookie sheet.
'
' Invokes HideZeroUsage.
FunctNextCookie2 (True)
End Sub

Function FunctNextCookie2(Optional blnHide As Boolean)
Sheets("C_Data2").Select
Cells(3, ActiveCell.Column).Select
' Moves cursor to row 3.
ActiveCell.Offset(0, 1).Range("A1").Select
' Selects next SKU.
Selection.Copy
Sheets("Cookie2").Select
Range("C5").PasteSpecial Paste:=xlValues
' Does not HideZeroUsage if called by ReportAll.
If blnHide = True Then HideZeroUsage
End Function

Sub NextMuffin()
' Macro recorded 1/3/2002, revised 1/7/2002
' by Lee Salazar.
'
' Keyboard Shortcut: Ctrl+Shift+M
'
' Will select M_Data sheet, move cursor one cell to right,
' copy, and paste value on cell C5 of Muffin sheet.
'
' Invokes HideZeroUsage.
FunctNextMuffin (True)
End Sub

Function FunctNextMuffin(Optional blnHide As Boolean)
Sheets("M_Data").Select
Cells(3, ActiveCell.Column).Select
' Moves cursor to row 3.
ActiveCell.Offset(0, 1).Range("A1").Select
' Selects next SKU.
Selection.Copy
Sheets("Muffin").Select
Range("C5").PasteSpecial Paste:=xlValues
' Does not HideZeroUsage if called by ReportAll.
If blnHide = True Then HideZeroUsage
End Function

Sub PrintAllProducts()
' by Lee Salazar.
' Keyboard Shortcut: Ctrl+Shift+A
Dim bytGo As Byte
Dim objSheet As Object
Dim strDataSource As String

bytGo = MsgBox("Print cost model for every product?", vbYesNo, _
"Print All Products")
' User's opportunity to back out.
Select Case bytGo
Case vbYes
For Each objSheet In Worksheets
' Loops through all sheets.
If objSheet.Name = "C_Data" Or _
objSheet.Name = "C_Data2" Or _
objSheet.Name = "M_Data" Then
' Skips all but data sheets, so Cookies
' and Muffins each get read once.
objSheet.Select
Cells(3, 1).Select
' Starting point for NextCookie & NextMuffin.
Do
' Keeps reading to right until it runs out
' of SKUs, then moves onto next sheet.
If WhichPlant = "Cookie" Then
FunctNextCookie (True) ' Runs next cookie.
Else
FunctNextCookie2 (True) ' Runs next cookie.
Else
FunctNextCookie2 (True) ' Runs next muffin.
End If
' MsgBox Range("C5").Value
' Uncomment to test macro w/o wasting paper.
ActiveWindow.SelectedSheets.PrintOut Copies:=1
' Prints.
objSheet.Select
' Starting point for next iteration of
' NextCookie or NextMuffin.
Loop Until ActiveCell.Offset(0, 1).Range("A1").Value = ""
End If
Next objSheet
Case Else
' Do nothing.
End Select
End Sub

Sub HideZeroUsage()
' HOW TO BREAK THIS MACRO
' allowing blank spaces in the
' -- Item Desc ranges (raws)
' -- HLOOKUP(row) ranges for the Item Descs (pkg)

Select Case ActiveSheet.Name = "Cookie" Or ActiveSheet.Name = "Cookie2"
Or ActiveSheet.Name = "Muffin"
' prevents macro from running on any sheet but templates.
Case True

Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select

' Raws
Select Case ActiveSheet.Name
' positions cursor at start of raw materials
Case "Cookie"
Range("E21").Activate
Case "Cookie2"
Range("E21").Activate
Case "Muffin"
Range("E23").Activate
End Select

Do
Select Case True
Case ActiveCell = 0
Selection.EntireRow.Hidden = True
Case ActiveCell.Offset(0, -3).Value = ""
Selection.EntireRow.Hidden = True
End Select
ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell.Offset(0, -3).Value < 0

' Packaging
Select Case ActiveSheet.Name
' positions cursor at start of raw materials
Case "Cookie"
Range("E133").Activate
Case "Cookie2"
Range("E133").Activate
Case "Muffin"
Range("E138").Activate
End Select
Do
Select Case True
Case ActiveCell.Value = 0
Selection.EntireRow.Hidden = True
Case ActiveCell.Offset(0, -3).Value = ""
Selection.EntireRow.Hidden = True
End Select
ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell.Offset(0, -4).Value < 0

Case Else
End Select
' prevents macro from running on any sheet but templates.
End Sub



Public Function Reset()

End Function

Did I lose you?

"Dave Peterson" wrote:

I'd look again at that activecell.

I bet it has an error in it.

BR wrote:

If I use my mouse to hoover the cursor over Case ActiveCell.Value = 0, then
the error pops up in a box that disappears when I move my curosr. There is no
error in the actual cell, itself.

"Dave Peterson" wrote:

That's the error you get?

Or are you saying that you have an error in that activecell?

Maybe...

Do
Select Case True
case iserror(activecell.value)
'do something else??
Case ActiveCell.Value = 0
Selection.EntireRow.Hidden = True
Case ActiveCell.Offset(0, -3).Value = ""
Selection.EntireRow.Hidden = True
End Select
ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell.Offset(0, -3).Value < 0

BR wrote:

We are getting so close. When my macro gets to the last cell of data and then
tries to move to the first blank cell, I get the following error:

Case ActiveCell.Value = Error 2024

on this part of the mac code:

Do
Select Case True
Case ActiveCell.Value = 0
Selection.EntireRow.Hidden = True
Case ActiveCell.Offset(0, -3).Value = ""
Selection.EntireRow.Hidden = True
End Select
ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell.Offset(0, -3).Value < 0

"Dave Peterson" wrote:

Check the other branch of this thread.



BR wrote:

Where would I place your code to make it work in my macro?

Your code:
if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if

"Dave Peterson" wrote:

You might be able to use a worksheet_change event.

But I think that most people would shy away from selecting cells in their code.



BR wrote:

Is there a simply way to have the cursor in an Excel spreadsheet, once it
reaches a certain cell due to macro movement, to automatically jump back to
Column A.

"Dave Peterson" wrote:

I don't quite understand what you're doing, but it sounds like you're selecting
cells. It's not usually necessary to select cells to work on them.

But you could use

if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if



BR wrote:

Thanks. I am just trying to get my cursor back to the beginning of my
worksheet after my macro has moved it to the last column, by way of the macro
loop, without having to manually move the cursor. Where would I place your
code?

"Dave Peterson" wrote:

Maybe you could just loop between column 1 and the maximum column:

dim cCtr as long
for cctr = 1 to activesheet.columns.count
'do something
next cctr

Say you want to loop through rows looking through columns:

dim cCtr as long
dim rCtr as long

for rctr = 1 to 10
for cctr = 1 to activesheet.columns.count
msgbox activesheet.cells(rctr,cctr).value
next cctr
next rctr

=====
If this doesn't help (and I'd be kind of surprised if it did!), you may want to
add some more detail to your question.

BR wrote:

I have a macro that loops from column A to column IV(the last column in
Excel). Once the last column is read how can I cause my macro to restart at
the column A again? Can I set my macro to return to column A after it reads
column IV. What would be the macro syntax for that? Thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I don't know which line is causing the error.

And I don't see where you checked the activecell.column in any of the code.

Something like this--

if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if

But I don't have a guess where it should go.

BR wrote:

I tried your code. I think that we are getting very close. When I run my
macro to the last column of data, the next(blank) cell is activated but the
macro errors. My entire mac code is as follows:

Option Explicit
' How do I return to memory the 'puter power that is used
' by my variables?

' Module Name: NextProduct

' CONTENTS OF THIS MODULE
' 1. NextCookie [Ctrl+Shift+C]
' Calls FunctNextCookie frm keyboard;
' instructs it to un/hide ingreds.
' 1f. FunctNextCookie - FUNCTION
' Runs next SKU thru cookie cost template;
' un/hides rows to display ingreds if so instructed.
' 2. NextCookie [Ctrl+Shift+D]
' Calls FunctNextCookie frm keyboard;
' instructs it to un/hide ingreds.
' 2f. FunctNextCookie - FUNCTION
' Runs next SKU thru cookie cost template;
' un/hides rows to display ingreds if so instructed.
' 3. NextMuffin [Ctrl+Shift+M]
' Calls FunctNextMuffin frm keyboard;
' instructs it to un/hide ingreds.
' 3f. FunctNextMuffin - FUNCTION
' Runs next SKU thru muffin cost template;
' un/hides rows to display ingreds if so instructed.
' 4. PrintAllProducts [Ctrl+Shift+A]
' Runs each product & prints a copy.
' 5. HideZeroUsage
' Hides any raw & pkg ingrediant items w/ 0 usage for
' currently displayed SKU.

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
Sub NextCookie()
' Macro recorded 1/3/2002, revised 12/5/2005
' by Bill Riley.
'
' Keyboard Shortcut: Ctrl+Shift+C
'
' Will select C_Data sheet, move cursor one cell to right,
' copy, and paste value on cell C5 of Cookie sheet.
'
' Invokes HideZeroUsage.
FunctNextCookie (True)
End Sub

Function FunctNextCookie(Optional blnHide As Boolean)
Sheets("C_Data").Select
Cells(3, ActiveCell.Column).Select
' Moves cursor to row 3.
ActiveCell.Offset(0, 1).Range("A1").Select
'Selects next SKU.
Selection.Copy
Sheets("Cookie").Select
Range("C5").PasteSpecial Paste:=xlValues
' Does not HideZeroUsage if called by ReportAll.
If blnHide = True Then HideZeroUsage
End Function

Sub NextCookie2()
' Macro recorded 1/3/2002, revised 12/5/2005
' by Bill Riley.
'
' Keyboard Shortcut: Ctrl+Shift+D
'
' Will select C_Data2 sheet, move cursor one cell to right,
' copy, and paste value on cell C5 of Cookie sheet.
'
' Invokes HideZeroUsage.
FunctNextCookie2 (True)
End Sub

Function FunctNextCookie2(Optional blnHide As Boolean)
Sheets("C_Data2").Select
Cells(3, ActiveCell.Column).Select
' Moves cursor to row 3.
ActiveCell.Offset(0, 1).Range("A1").Select
' Selects next SKU.
Selection.Copy
Sheets("Cookie2").Select
Range("C5").PasteSpecial Paste:=xlValues
' Does not HideZeroUsage if called by ReportAll.
If blnHide = True Then HideZeroUsage
End Function

Sub NextMuffin()
' Macro recorded 1/3/2002, revised 1/7/2002
' by Lee Salazar.
'
' Keyboard Shortcut: Ctrl+Shift+M
'
' Will select M_Data sheet, move cursor one cell to right,
' copy, and paste value on cell C5 of Muffin sheet.
'
' Invokes HideZeroUsage.
FunctNextMuffin (True)
End Sub

Function FunctNextMuffin(Optional blnHide As Boolean)
Sheets("M_Data").Select
Cells(3, ActiveCell.Column).Select
' Moves cursor to row 3.
ActiveCell.Offset(0, 1).Range("A1").Select
' Selects next SKU.
Selection.Copy
Sheets("Muffin").Select
Range("C5").PasteSpecial Paste:=xlValues
' Does not HideZeroUsage if called by ReportAll.
If blnHide = True Then HideZeroUsage
End Function

Sub PrintAllProducts()
' by Lee Salazar.
' Keyboard Shortcut: Ctrl+Shift+A
Dim bytGo As Byte
Dim objSheet As Object
Dim strDataSource As String

bytGo = MsgBox("Print cost model for every product?", vbYesNo, _
"Print All Products")
' User's opportunity to back out.
Select Case bytGo
Case vbYes
For Each objSheet In Worksheets
' Loops through all sheets.
If objSheet.Name = "C_Data" Or _
objSheet.Name = "C_Data2" Or _
objSheet.Name = "M_Data" Then
' Skips all but data sheets, so Cookies
' and Muffins each get read once.
objSheet.Select
Cells(3, 1).Select
' Starting point for NextCookie & NextMuffin.
Do
' Keeps reading to right until it runs out
' of SKUs, then moves onto next sheet.
If WhichPlant = "Cookie" Then
FunctNextCookie (True) ' Runs next cookie.
Else
FunctNextCookie2 (True) ' Runs next cookie.
Else
FunctNextCookie2 (True) ' Runs next muffin.
End If
' MsgBox Range("C5").Value
' Uncomment to test macro w/o wasting paper.
ActiveWindow.SelectedSheets.PrintOut Copies:=1
' Prints.
objSheet.Select
' Starting point for next iteration of
' NextCookie or NextMuffin.
Loop Until ActiveCell.Offset(0, 1).Range("A1").Value = ""
End If
Next objSheet
Case Else
' Do nothing.
End Select
End Sub

Sub HideZeroUsage()
' HOW TO BREAK THIS MACRO
' allowing blank spaces in the
' -- Item Desc ranges (raws)
' -- HLOOKUP(row) ranges for the Item Descs (pkg)

Select Case ActiveSheet.Name = "Cookie" Or ActiveSheet.Name = "Cookie2"
Or ActiveSheet.Name = "Muffin"
' prevents macro from running on any sheet but templates.
Case True

Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select

' Raws
Select Case ActiveSheet.Name
' positions cursor at start of raw materials
Case "Cookie"
Range("E21").Activate
Case "Cookie2"
Range("E21").Activate
Case "Muffin"
Range("E23").Activate
End Select

Do
Select Case True
Case ActiveCell = 0
Selection.EntireRow.Hidden = True
Case ActiveCell.Offset(0, -3).Value = ""
Selection.EntireRow.Hidden = True
End Select
ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell.Offset(0, -3).Value < 0

' Packaging
Select Case ActiveSheet.Name
' positions cursor at start of raw materials
Case "Cookie"
Range("E133").Activate
Case "Cookie2"
Range("E133").Activate
Case "Muffin"
Range("E138").Activate
End Select
Do
Select Case True
Case ActiveCell.Value = 0
Selection.EntireRow.Hidden = True
Case ActiveCell.Offset(0, -3).Value = ""
Selection.EntireRow.Hidden = True
End Select
ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell.Offset(0, -4).Value < 0

Case Else
End Select
' prevents macro from running on any sheet but templates.
End Sub

Public Function Reset()

End Function

--

Dave Peterson

Thanks for your help. My goal is to have the cursor reset all the way back to
original starting position once it reaches the end of the spreadsheet
columns. There would probably need to be some code specifically for the last
cell (IV3) that, when the macro moves the cursor to that cell, it would tell
the macro to reset the cursor position to the start? Any ideas?


"Dave Peterson" wrote:

I don't know which line is causing the error.

And I don't see where you checked the activecell.column in any of the code.

Something like this--

if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if

But I don't have a guess where it should go.

BR wrote:

I tried your code. I think that we are getting very close. When I run my
macro to the last column of data, the next(blank) cell is activated but the
macro errors. My entire mac code is as follows:

Option Explicit
' How do I return to memory the 'puter power that is used
' by my variables?

' Module Name: NextProduct

' CONTENTS OF THIS MODULE
' 1. NextCookie [Ctrl+Shift+C]
' Calls FunctNextCookie frm keyboard;
' instructs it to un/hide ingreds.
' 1f. FunctNextCookie - FUNCTION
' Runs next SKU thru cookie cost template;
' un/hides rows to display ingreds if so instructed.
' 2. NextCookie [Ctrl+Shift+D]
' Calls FunctNextCookie frm keyboard;
' instructs it to un/hide ingreds.
' 2f. FunctNextCookie - FUNCTION
' Runs next SKU thru cookie cost template;
' un/hides rows to display ingreds if so instructed.
' 3. NextMuffin [Ctrl+Shift+M]
' Calls FunctNextMuffin frm keyboard;
' instructs it to un/hide ingreds.
' 3f. FunctNextMuffin - FUNCTION
' Runs next SKU thru muffin cost template;
' un/hides rows to display ingreds if so instructed.
' 4. PrintAllProducts [Ctrl+Shift+A]
' Runs each product & prints a copy.
' 5. HideZeroUsage
' Hides any raw & pkg ingrediant items w/ 0 usage for
' currently displayed SKU.

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
Sub NextCookie()
' Macro recorded 1/3/2002, revised 12/5/2005
' by Bill Riley.
'
' Keyboard Shortcut: Ctrl+Shift+C
'
' Will select C_Data sheet, move cursor one cell to right,
' copy, and paste value on cell C5 of Cookie sheet.
'
' Invokes HideZeroUsage.
FunctNextCookie (True)
End Sub

Function FunctNextCookie(Optional blnHide As Boolean)
Sheets("C_Data").Select
Cells(3, ActiveCell.Column).Select
' Moves cursor to row 3.
ActiveCell.Offset(0, 1).Range("A1").Select
'Selects next SKU.
Selection.Copy
Sheets("Cookie").Select
Range("C5").PasteSpecial Paste:=xlValues
' Does not HideZeroUsage if called by ReportAll.
If blnHide = True Then HideZeroUsage
End Function

Sub NextCookie2()
' Macro recorded 1/3/2002, revised 12/5/2005
' by Bill Riley.
'
' Keyboard Shortcut: Ctrl+Shift+D
'
' Will select C_Data2 sheet, move cursor one cell to right,
' copy, and paste value on cell C5 of Cookie sheet.
'
' Invokes HideZeroUsage.
FunctNextCookie2 (True)
End Sub

Function FunctNextCookie2(Optional blnHide As Boolean)
Sheets("C_Data2").Select
Cells(3, ActiveCell.Column).Select
' Moves cursor to row 3.
ActiveCell.Offset(0, 1).Range("A1").Select
' Selects next SKU.
Selection.Copy
Sheets("Cookie2").Select
Range("C5").PasteSpecial Paste:=xlValues
' Does not HideZeroUsage if called by ReportAll.
If blnHide = True Then HideZeroUsage
End Function

Sub NextMuffin()
' Macro recorded 1/3/2002, revised 1/7/2002
' by Lee Salazar.
'
' Keyboard Shortcut: Ctrl+Shift+M
'
' Will select M_Data sheet, move cursor one cell to right,
' copy, and paste value on cell C5 of Muffin sheet.
'
' Invokes HideZeroUsage.
FunctNextMuffin (True)
End Sub

Function FunctNextMuffin(Optional blnHide As Boolean)
Sheets("M_Data").Select
Cells(3, ActiveCell.Column).Select
' Moves cursor to row 3.
ActiveCell.Offset(0, 1).Range("A1").Select
' Selects next SKU.
Selection.Copy
Sheets("Muffin").Select
Range("C5").PasteSpecial Paste:=xlValues
' Does not HideZeroUsage if called by ReportAll.
If blnHide = True Then HideZeroUsage
End Function

Sub PrintAllProducts()
' by Lee Salazar.
' Keyboard Shortcut: Ctrl+Shift+A
Dim bytGo As Byte
Dim objSheet As Object
Dim strDataSource As String

bytGo = MsgBox("Print cost model for every product?", vbYesNo, _
"Print All Products")
' User's opportunity to back out.
Select Case bytGo
Case vbYes
For Each objSheet In Worksheets
' Loops through all sheets.
If objSheet.Name = "C_Data" Or _
objSheet.Name = "C_Data2" Or _
objSheet.Name = "M_Data" Then
' Skips all but data sheets, so Cookies
' and Muffins each get read once.
objSheet.Select
Cells(3, 1).Select
' Starting point for NextCookie & NextMuffin.
Do
' Keeps reading to right until it runs out
' of SKUs, then moves onto next sheet.
If WhichPlant = "Cookie" Then
FunctNextCookie (True) ' Runs next cookie.
Else
FunctNextCookie2 (True) ' Runs next cookie.
Else
FunctNextCookie2 (True) ' Runs next muffin.
End If
' MsgBox Range("C5").Value
' Uncomment to test macro w/o wasting paper.
ActiveWindow.SelectedSheets.PrintOut Copies:=1
' Prints.
objSheet.Select
' Starting point for next iteration of
' NextCookie or NextMuffin.
Loop Until ActiveCell.Offset(0, 1).Range("A1").Value = ""
End If
Next objSheet
Case Else
' Do nothing.
End Select
End Sub

Sub HideZeroUsage()
' HOW TO BREAK THIS MACRO
' allowing blank spaces in the
' -- Item Desc ranges (raws)
' -- HLOOKUP(row) ranges for the Item Descs (pkg)

Select Case ActiveSheet.Name = "Cookie" Or ActiveSheet.Name = "Cookie2"
Or ActiveSheet.Name = "Muffin"
' prevents macro from running on any sheet but templates.
Case True

Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select

' Raws
Select Case ActiveSheet.Name
' positions cursor at start of raw materials
Case "Cookie"
Range("E21").Activate
Case "Cookie2"
Range("E21").Activate
Case "Muffin"
Range("E23").Activate
End Select

Do
Select Case True
Case ActiveCell = 0
Selection.EntireRow.Hidden = True
Case ActiveCell.Offset(0, -3).Value = ""
Selection.EntireRow.Hidden = True
End Select
ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell.Offset(0, -3).Value < 0

' Packaging
Select Case ActiveSheet.Name
' positions cursor at start of raw materials
Case "Cookie"
Range("E133").Activate
Case "Cookie2"
Range("E133").Activate
Case "Muffin"
Range("E138").Activate
End Select
Do
Select Case True
Case ActiveCell.Value = 0
Selection.EntireRow.Hidden = True
Case ActiveCell.Offset(0, -3).Value = ""
Selection.EntireRow.Hidden = True
End Select
ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell.Offset(0, -4).Value < 0

Case Else
End Select
' prevents macro from running on any sheet but templates.
End Sub

Public Function Reset()

End Function

--

Dave Peterson

I still didn't look at all the code, but maybe you could just check the range's
address:

if activecell.address = "IV3" then
'do what you want
.....

BR wrote:

Thanks for your help. My goal is to have the cursor reset all the way back to
original starting position once it reaches the end of the spreadsheet
columns. There would probably need to be some code specifically for the last
cell (IV3) that, when the macro moves the cursor to that cell, it would tell
the macro to reset the cursor position to the start? Any ideas?

"Dave Peterson" wrote:

I don't know which line is causing the error.

And I don't see where you checked the activecell.column in any of the code.

Something like this--

if activecell.column = activesheet.columns.count then
activesheet.cells(activecell.row+1,1).select
end if

But I don't have a guess where it should go.

BR wrote:

I tried your code. I think that we are getting very close. When I run my
macro to the last column of data, the next(blank) cell is activated but the
macro errors. My entire mac code is as follows:

Option Explicit
' How do I return to memory the 'puter power that is used
' by my variables?

' Module Name: NextProduct

' CONTENTS OF THIS MODULE
' 1. NextCookie [Ctrl+Shift+C]
' Calls FunctNextCookie frm keyboard;
' instructs it to un/hide ingreds.
' 1f. FunctNextCookie - FUNCTION
' Runs next SKU thru cookie cost template;
' un/hides rows to display ingreds if so instructed.
' 2. NextCookie [Ctrl+Shift+D]
' Calls FunctNextCookie frm keyboard;
' instructs it to un/hide ingreds.
' 2f. FunctNextCookie - FUNCTION
' Runs next SKU thru cookie cost template;
' un/hides rows to display ingreds if so instructed.
' 3. NextMuffin [Ctrl+Shift+M]
' Calls FunctNextMuffin frm keyboard;
' instructs it to un/hide ingreds.
' 3f. FunctNextMuffin - FUNCTION
' Runs next SKU thru muffin cost template;
' un/hides rows to display ingreds if so instructed.
' 4. PrintAllProducts [Ctrl+Shift+A]
' Runs each product & prints a copy.
' 5. HideZeroUsage
' Hides any raw & pkg ingrediant items w/ 0 usage for
' currently displayed SKU.

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
Sub NextCookie()
' Macro recorded 1/3/2002, revised 12/5/2005
' by Bill Riley.
'
' Keyboard Shortcut: Ctrl+Shift+C
'
' Will select C_Data sheet, move cursor one cell to right,
' copy, and paste value on cell C5 of Cookie sheet.
'
' Invokes HideZeroUsage.
FunctNextCookie (True)
End Sub

Function FunctNextCookie(Optional blnHide As Boolean)
Sheets("C_Data").Select
Cells(3, ActiveCell.Column).Select
' Moves cursor to row 3.
ActiveCell.Offset(0, 1).Range("A1").Select
'Selects next SKU.
Selection.Copy
Sheets("Cookie").Select
Range("C5").PasteSpecial Paste:=xlValues
' Does not HideZeroUsage if called by ReportAll.
If blnHide = True Then HideZeroUsage
End Function

Sub NextCookie2()
' Macro recorded 1/3/2002, revised 12/5/2005
' by Bill Riley.
'
' Keyboard Shortcut: Ctrl+Shift+D
'
' Will select C_Data2 sheet, move cursor one cell to right,
' copy, and paste value on cell C5 of Cookie sheet.
'
' Invokes HideZeroUsage.
FunctNextCookie2 (True)
End Sub

Function FunctNextCookie2(Optional blnHide As Boolean)
Sheets("C_Data2").Select
Cells(3, ActiveCell.Column).Select
' Moves cursor to row 3.
ActiveCell.Offset(0, 1).Range("A1").Select
' Selects next SKU.
Selection.Copy
Sheets("Cookie2").Select
Range("C5").PasteSpecial Paste:=xlValues
' Does not HideZeroUsage if called by ReportAll.
If blnHide = True Then HideZeroUsage
End Function

Sub NextMuffin()
' Macro recorded 1/3/2002, revised 1/7/2002
' by Lee Salazar.
'
' Keyboard Shortcut: Ctrl+Shift+M
'
' Will select M_Data sheet, move cursor one cell to right,
' copy, and paste value on cell C5 of Muffin sheet.
'
' Invokes HideZeroUsage.
FunctNextMuffin (True)
End Sub

Function FunctNextMuffin(Optional blnHide As Boolean)
Sheets("M_Data").Select
Cells(3, ActiveCell.Column).Select
' Moves cursor to row 3.
ActiveCell.Offset(0, 1).Range("A1").Select
' Selects next SKU.
Selection.Copy
Sheets("Muffin").Select
Range("C5").PasteSpecial Paste:=xlValues
' Does not HideZeroUsage if called by ReportAll.
If blnHide = True Then HideZeroUsage
End Function

Sub PrintAllProducts()
' by Lee Salazar.
' Keyboard Shortcut: Ctrl+Shift+A
Dim bytGo As Byte
Dim objSheet As Object
Dim strDataSource As String

bytGo = MsgBox("Print cost model for every product?", vbYesNo, _
"Print All Products")
' User's opportunity to back out.
Select Case bytGo
Case vbYes
For Each objSheet In Worksheets
' Loops through all sheets.
If objSheet.Name = "C_Data" Or _
objSheet.Name = "C_Data2" Or _
objSheet.Name = "M_Data" Then
' Skips all but data sheets, so Cookies
' and Muffins each get read once.
objSheet.Select
Cells(3, 1).Select
' Starting point for NextCookie & NextMuffin.
Do
' Keeps reading to right until it runs out
' of SKUs, then moves onto next sheet.
If WhichPlant = "Cookie" Then
FunctNextCookie (True) ' Runs next cookie.
Else
FunctNextCookie2 (True) ' Runs next cookie.
Else
FunctNextCookie2 (True) ' Runs next muffin.
End If
' MsgBox Range("C5").Value
' Uncomment to test macro w/o wasting paper.
ActiveWindow.SelectedSheets.PrintOut Copies:=1
' Prints.
objSheet.Select
' Starting point for next iteration of
' NextCookie or NextMuffin.
Loop Until ActiveCell.Offset(0, 1).Range("A1").Value = ""
End If
Next objSheet
Case Else
' Do nothing.
End Select
End Sub

Sub HideZeroUsage()
' HOW TO BREAK THIS MACRO
' allowing blank spaces in the
' -- Item Desc ranges (raws)
' -- HLOOKUP(row) ranges for the Item Descs (pkg)

Select Case ActiveSheet.Name = "Cookie" Or ActiveSheet.Name = "Cookie2"
Or ActiveSheet.Name = "Muffin"
' prevents macro from running on any sheet but templates.
Case True

Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select

' Raws
Select Case ActiveSheet.Name
' positions cursor at start of raw materials
Case "Cookie"
Range("E21").Activate
Case "Cookie2"
Range("E21").Activate
Case "Muffin"
Range("E23").Activate
End Select

Do
Select Case True
Case ActiveCell = 0
Selection.EntireRow.Hidden = True
Case ActiveCell.Offset(0, -3).Value = ""
Selection.EntireRow.Hidden = True
End Select
ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell.Offset(0, -3).Value < 0

' Packaging
Select Case ActiveSheet.Name
' positions cursor at start of raw materials
Case "Cookie"
Range("E133").Activate
Case "Cookie2"
Range("E133").Activate
Case "Muffin"
Range("E138").Activate
End Select
Do
Select Case True
Case ActiveCell.Value = 0
Selection.EntireRow.Hidden = True
Case ActiveCell.Offset(0, -3).Value = ""
Selection.EntireRow.Hidden = True
End Select
ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell.Offset(0, -4).Value < 0

Case Else
End Select
' prevents macro from running on any sheet but templates.
End Sub

Public Function Reset()

End Function

--

Dave Peterson


--

Dave Peterson