Checking for values, then copying only cells with values to

[Dave]

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

 

[Jay Petrulis]

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

 

[Dave]

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

 

[Dave]

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

 

[Dave Hawley]

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