 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2008Macro If Then Question
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 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 > I totally screwed up...I posted this in the Access group. Anyway, I usually > find what I need but not this time... [quoted text clipped - 5 lines] > Thanks in advance > Cheers Jay...that works well...thanks! > Hi mslabbe - > [quoted text clipped - 19 lines] > > 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:A500>0 Then F1:F500 copy and paste into worksheet 'sheet3' B10:B510. > Hi mslabbe - > [quoted text clipped - 19 lines] > > 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 > 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 [quoted text clipped - 23 lines] > > > Thanks in advance > > > Cheers |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.