Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula question
I am trying to find a formula to copy the rows that contain "suzanne" in
column C and "open" in column B to another worksheet...anyone have any ideas??? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula question
Kristal_81;376238 Wrote: I am trying to find a formula to copy the rows that contain "suzanne" in column C and "open" in column B to another worksheet...anyone have any ideas???Formulae can not copy anything, they can display a result, so if your criteria was met and you had a whole row of formula to do it it could display the row that meets your criteria, however, if you changed either suzanne or open then you would lose your results, the only way to achieve this is with VBA like this, this is standard module code: Code: -------------------- Sub find_and_move_suzanne() Dim rng As Range, MyCell As Range Set rng = Sheets("Sheet1").Range("C1:C" & Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row) For Each MyCell In rng.SpecialCells(xlCellTypeVisible) If LCase(MyCell.Value) = LCase("suzanne") And LCase(MyCell.Offset(0, -1).Value) = LCase("open") Then MyCell.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) End If Next MyCell End Sub -------------------- *How to add and run a Macro*1. *Copy* the macro above pressing the keys *CTRL+C* 2. Open your workbook 3. Press the keys *ALT+F11* to open the Visual Basic Editor 4. Press the keys *ALT+I* to activate the *Insert menu* 5. *Press M* to insert a *Standard Module* 6. *Paste* the code by pressing the keys *CTRL+V* 7. Make any custom changes to the macro if needed at this time. 8. *Save the Macro* by pressing the keys *CTRL+S* 9. Press the keys *ALT+Q* to exit the Editor, and return to Excel. *To Run the Macro...* To run the macro from Excel, open the workbook, and press *ALT+F8* to display the *Run Macro Dialog*. Double Click the macro's name to *Run* it. -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=105276 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula question
You will have to use a macro to copy this. The below macro will copy the
mentioned rows from Sheet1 to Sheet2. Adjust the sheet names if any changes. If you are new to macros set the Security level to low/medium in (Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11. From menu 'Insert' a module and paste the below code. Save. Get back to Workbook. Run macro from Tools|Macro|Run <selected macro() Sub Copyrows() Dim wb As Workbook Dim ws1 As Worksheet, ws2 As Worksheet Dim lngRow As Long Dim lngLastRow1 As Long, lngLastRow2 As Long Set wb = ActiveWorkbook Set ws1 = wb.Sheets("Sheet1") Set ws2 = wb.Sheets("Sheet2") lngLastRow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row lngLastRow2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow1 If StrComp(ws1.Range("C" & lngRow), "suzanne", vbTextCompare) + _ StrComp(ws1.Range("b" & lngRow), "open", vbTextCompare) = 0 Then ws1.Rows(lngRow).Copy ws2.Rows(lngLastRow2 + 1) lngLastRow2 = lngLastRow2 + 1 End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "Kristal_81" wrote: I am trying to find a formula to copy the rows that contain "suzanne" in column C and "open" in column B to another worksheet...anyone have any ideas??? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula question | Excel Worksheet Functions | |||
Formula Question | Excel Discussion (Misc queries) | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
IF formula question | Excel Worksheet Functions | |||
formula Question | Excel Discussion (Misc queries) |