I totally screwed up...I posted this in the Access group. Anyway, I usually
find what I need but not this time...

I would like to run a macro based on a column. Example, if A7:A5000, then
copy F7:F500. Basically, if A7 is 0, I would like F7 to be copied and paste
in BB7, and for it to go down to 500.

Thanks in advance
Cheers

Hi mslabbe -

Here's one way:

Sub mslabbe()
For Each cel In Range("A1:A500")
If cel.Value 0 Then cel.Offset(0, 53) = _
cel.Offset(0, 5)
Next 'cel
End Sub

----
Jay



"mslabbe" wrote:

I totally screwed up...I posted this in the Access group. Anyway, I usually
find what I need but not this time...

I would like to run a macro based on a column. Example, if A7:A5000, then
copy F7:F500. Basically, if A7 is 0, I would like F7 to be copied and paste
in BB7, and for it to go down to 500.

Thanks in advance
Cheers

Jay...that works well...thanks!

"Jay" wrote:

Hi mslabbe -

Here's one way:

Sub mslabbe()
For Each cel In Range("A1:A500")
If cel.Value 0 Then cel.Offset(0, 53) = _
cel.Offset(0, 5)
Next 'cel
End Sub

----
Jay



"mslabbe" wrote:

I totally screwed up...I posted this in the Access group. Anyway, I usually
find what I need but not this time...

I would like to run a macro based on a column. Example, if A7:A5000, then
copy F7:F500. Basically, if A7 is 0, I would like F7 to be copied and paste
in BB7, and for it to go down to 500.

Thanks in advance
Cheers

Is there slick way to have it go to a different tab and start in a different
cell INSTEAD of going to column BB? So If A1:A5000 Then F1:F500 copy and
paste into worksheet 'sheet3' B10:B510.



"Jay" wrote:

Hi mslabbe -

Here's one way:

Sub mslabbe()
For Each cel In Range("A1:A500")
If cel.Value 0 Then cel.Offset(0, 53) = _
cel.Offset(0, 5)
Next 'cel
End Sub

----
Jay



"mslabbe" wrote:

I totally screwed up...I posted this in the Access group. Anyway, I usually
find what I need but not this time...

I would like to run a macro based on a column. Example, if A7:A5000, then
copy F7:F500. Basically, if A7 is 0, I would like F7 to be copied and paste
in BB7, and for it to go down to 500.

Thanks in advance
Cheers

Hi mslabbe -

Adjust the statements in the 'User-defined parameters' section to suit.

Sub mslabbe_V2()
'------------------------
'User-defined parameters
'------------------------
sSheet = "source_sheet_name_here"
dSheet = "destination_sheet_name_here"
dCol = "B": dRow = 10 'output col and 1st output row
'------------------------
Set ws1 = Worksheets(sSheet)
Set ws2 = Worksheets(dSheet)

For Each cel In ws1.Range("A1:A500")
If cel.Value 0 Then
ws2.Range(dCol & dRow) = cel.Offset(0, 5)
End If
dRow = dRow + 1
Next 'cel
End Sub
-----
Jay



"mslabbe" wrote:

Is there slick way to have it go to a different tab and start in a different
cell INSTEAD of going to column BB? So If A1:A5000 Then F1:F500 copy and
paste into worksheet 'sheet3' B10:B510.



"Jay" wrote:

Hi mslabbe -

Here's one way:

Sub mslabbe()
For Each cel In Range("A1:A500")
If cel.Value 0 Then cel.Offset(0, 53) = _
cel.Offset(0, 5)
Next 'cel
End Sub

----
Jay



"mslabbe" wrote:

I totally screwed up...I posted this in the Access group. Anyway, I usually
find what I need but not this time...

I would like to run a macro based on a column. Example, if A7:A5000, then
copy F7:F500. Basically, if A7 is 0, I would like F7 to be copied and paste
in BB7, and for it to go down to 500.

Thanks in advance
Cheers