Looping Through a range and Marching Data

[ninjalearner]

Dear All,

I have a table that looks like the one i posted here. My aim is to pick information from the qty column and take their correspondent data and present them in a separate sheet as a summary report.


What i have been able to do is to set each row range here to where i want them to report in the summary sheet. but i have realised that if i encounter a huge database i will not be able to set ranges one by one. I am therefore in need of an example code that can loop through all the cells in qty column and march the contents of ecah cell that has value with its corresponding product name and stock value then present them in the result sheet.


Stock Items Stock Value Stock Qty

YellowBook $200.00

IZAP $120.00 25

Mailer $340.00 32

Installer Pro $600.01

HP Deskjet $201.00 60

Robims Amp $742.00

Show Musical $820.00 56

LG QuickPto $50.00 68

Victura $20.00


This is what i am looking at the solution to look like

For Each cell in Range("C2:---")

If c.value>= 1 Then:"How do i pick each cell value and and their corresnponding values of cell a and cell b from here and place in my summary sheet?

 

[Hui]

Have a look at Luke's post

It does what you want with formulas without looping

http://chandoo.org/wp/2011/11/18/formula-forensics-003/

 

[Debraj]

Hi ninjalearner,


Above cane be achieved by various way. Please check the below codes in two different way, assuming you have already a sheet named "SumamrySheet")

___________________________

Sub UsingLoop()

j = 1

i = 1

Sheets("SumamrySheet").Cells.Clear

While Sheets("Sheet1").Range("a" & i) <> ""

If Sheets("Sheet1").Range("C" & i) <> "" Then

Sheets("Sheet1").Range("a" & i & ":c" & i).Copy Sheets("SumamrySheet").Range("A" & j)

j = j + 1

End If

i = i + 1

Wend

End Sub

_______________________


you can do the same using Autofilter also..

_________________________

Sub using_autoFilter()

Sheets("SumamrySheet").Cells.Clear

Sheets("sheet1").Range("a:c").AutoFilter Field:=3, Criteria1:="<>"

Sheets("sheet1").UsedRange.Copy Sheets("SumamrySheet").Range("a1")

Sheets("sheet1").AutoFilterMode = False

End Sub

_______________________


I would like to suggest you to record Macro as many as possible, so that, you can learn about the various objects in excel and hierarchy level for them.

 

[Debraj]