Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Checking for values, then copying only cells with values to

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Tennessee
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello:
    First off, pardon the long post.

    The setup:

    I have a data entry sheet with thirty different processes listed with cells for qty of good parts, and qty of bad parts. For example: cell C7 is 'Induction/MEECO', cell G7 is total 'Good' parts, and cell I7 is total 'Scrap' parts.

    Question:

    What is the best way (or any way for that matter) to:
    (1) Check values in cells G6:G30 for a value other than "".
    (2) If a cell has a value copy the process name, number of good parts, and number of scrap parts to another sheet.
    (3) Change change all values to real values instead of formulas (I really should know how to do this).

    Anyone care to help me avoid brain damage from beating my head against the wall on this one?

    Thanks in advance

    Dave

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Dave,

    I assume you want a VBA routine to do this, so try something like this:

    Sheet1 houses your data,
    Sheet2 is the destination range, where the contents of the C,G,I columns are transferred to the A,B,C columns of the new sheet.

    ---begin VBA---
    Sub transfer_to_other_sheet()

    Dim lastrow_first As Long
    Dim lastrow_second As Long
    Dim x As Long

    lastrow_first = Sheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row

    'Assume you have category headers and
    'your data starts in row 2
    For x = 2 To lastrow_first
    If Sheets("Sheet1").Cells(x, 7) <> "" Or _
    Not IsEmpty(Sheets("Sheet1").Cells(x, 7)) Then
    lastrow_second = _
    Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row

    Sheets("Sheet2").Cells(lastrow_second + 1, 1) = Sheets("Sheet1").Cells(x, 3).Value
    Sheets("Sheet2").Cells(lastrow_second + 1, 2) = Sheets("Sheet1").Cells(x, 7).Value
    Sheets("Sheet2").Cells(lastrow_second + 1, 3) = Sheets("Sheet1").Cells(x, 9).Value

    End If
    Next x


    End Sub
    ---end VBA---

    Watch the line wrapping.

    For your questions

    (1) In VBA,
    IsEmpty returns a T or F
    Cell = "" can be tested to T or F as well
    Len(Cell) will give a zero if blank or formula blank.

    (2) Done in code above. Make sure you complete your references when moving data from one sheet to another.

    (3)
    a)
    Edit>PasteSpecial>choose values for worksheet

    b)
    Range(xx).Copy
    Range(yy).PasteSpecial (xlValues) in code

    c)
    I just transferred the value of the cell to the new sheet rather than copying any formula over.

    HTH,
    Jay


    On 2002-03-22 17:42, Dave wrote:
    Hello:
    First off, pardon the long post.

    The setup:

    I have a data entry sheet with thirty different processes listed with cells for qty of good parts, and qty of bad parts. For example: cell C7 is 'Induction/MEECO', cell G7 is total 'Good' parts, and cell I7 is total 'Scrap' parts.

    Question:

    What is the best way (or any way for that matter) to:
    (1) Check values in cells G6:G30 for a value other than "".
    (2) If a cell has a value copy the process name, number of good parts, and number of scrap parts to another sheet.
    (3) Change change all values to real values instead of formulas (I really should know how to do this).

    Anyone care to help me avoid brain damage from beating my head against the wall on this one?

    Thanks in advance

    Dave

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Tennessee
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Jay:

    I copied and pasted your code, made some minor changes (my data starts in row 6 vs. row 2. Also changed sheets 1 & 2 to my sheet names) and I get a 'Runtime error 9. Subscript out of range.

    Here is the code after my changes:

    ----Start Code----
    Private Sub Data_Extraction()

    Dim lastrow_first As Long
    Dim lastrow_second As Long
    Dim x As Long

    lastrow_first = Sheets("Data_Entry").Cells(Rows.Count, "C").End(xlUp).Row

    'Assume you have category headers and

    'your data starts in row 2

    For x = 6 To lastrow_first

    If Sheets("Data_Entry").Cells(x, 7) <> "" Or Not IsEmpty(Sheets("Data_Entry").Cells(x, 7)) Then

    lastrow_second = Sheets("Data_Extraction").Cells(Rows.Count, "A").End(xlUp).Row

    Sheets("Data_Extraction").Cells(lastrow_second + 1, 1) = Sheets("Data_Entry").Cells(x, 3).Value

    Sheets("Data_Extraction").Cells(lastrow_second + 1, 2) = Sheets("Data_Entry").Cells(x, 7).Value

    Sheets("Data_Extraction").Cells(lastrow_second + 1, 3) = Sheets("Data_Entry").Cells(x, 9).Value

    End If

    Next x


    End Sub
    ----End Code----

    Any Clues??

    Thanks

    Dave

    _________________
    Does fuzzy logic tickle?

    [ This Message was edited by: Dave on 2002-03-23 18:11 ]

    [ This Message was edited by: Dave on 2002-03-23 18:12 ]

    [ This Message was edited by: Dave on 2002-03-23 18:14 ]

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Tennessee
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    HiYa Jay:

    Disreguard the previous post. I'm suffering from Cranial-Rectal Inversion (head up backside)! The Sheet labeled 'Data_Extraction' in the code was named 'Extraction_Data' in the work book. DUH!!!!

    Changed the names and it works fine.


    Thanks for the Help.

    Dave

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Dave

    What is the best way (or any way for that matter) to:
    (1) Check values in cells G6:G30 for a value other than "".

    AdvancedFilter


    (2) If a cell has a value copy the process name, number of good parts, and number of scrap parts to another sheet.

    AdvancedFilter

    (3) Change change all values to real values instead of formulas (I really should know how to do this).

    Sheet1.UsedRange=Sheet1.UsedRange.Value




Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •