Move data from one sheet to another with argu

Closed
steve - Oct 21, 2010 at 08:00 AM
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
- Oct 22, 2010 at 01:36 AM
Hello,
What I have is a sheet in excel 07. It's set up to track bags of different fertilizers on my yard, and how many, and cost. sheet1 looks like this
A1 C1 E1 F1
premergent 1 17.00 17.00
bug guard 0 15.00 0
starter fertilizer 2 24.00 48.00
summer guard 0 28.00 0
winterizer 1 22.00 22.00

This list continues for 15 rows and totals on F22
What I'd like to do is have C1 ignore the zero's and move the information to sheet2 D4, skipping any zero's, and put the total of F22, at G7
On sheet2, at D4, and G7 would look like this
D4
1 premergent
2 starter fertilizer
1 winterizer
G7
87.00
Is this something I can do with arguement statements.
If so, please help.
If not, tell me to go to programming school and learn macros, I'll understand.

Thank you in advance,
Steve



1 reply

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Oct 22, 2010 at 01:36 AM
quote
D4
1 premergent
2 starter fertilizer
1 winterizer
unquote

you did not make clear whether these three texts should be in one celll D4 or they can be in D4,D5 and D^ (I presumed latter)

the row no. 1 for headings.
PREFERBALY ALWAYS HAVE FIRST ROW AS COLUMN HEDINGS.

use this macro and see sheet 2.

Sub test() 
Dim r As Range, c As Range, r1 As Range 
Dim j As Integer, x As String, ssum As Double 
j = 0 
ssum = 0 
Worksheets("sheet1").Activate 
Set r = Range(Range("c2"), Range("c2").End(xlDown)) 
For Each c In r 
If c = 0 Then GoTo nextc 
x = c & " " & Cells(c.Row, "A") 
ssum = ssum + Cells(c.Row, "f") 
'msgbox x 
With Worksheets("sheet2") 
Set r1 = .Range("D4") 
r1.Offset(j, 0) = x 
End With 
j = j + 1 
nextc: 
Next c 
'msgbox ssum 
Worksheets("sheet2").Range("G7") = ssum 

End Sub 


0