Macro If Then Question

[mslabbe]

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:A500>0, 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]

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

 

[mslabbe]

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

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

 

[mslabbe]

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:A500>0 Then F1:F500 copy and
paste into worksheet 'sheet3' B10:B510.

 

[Jay]

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