PDA

View Full Version : Solved: modify macro to get group of data



mtsf26
11-18-2011, 08:47 PM
i have attached excel file and in this file contain macro,then i confused how to modify it.
in here when i input name of supplier(pusaka tani) in RESULT sheet then they will obtain some data like supplier,jenis,ukuran,kwt,quantity and price for each row that contain supplier name=pusaka tani. but in here im going to obtain data from supplier,jenis, group of ukuran,group of kwt based on each ukuran,total quantity based on ukuran and kwt,total price based on ukuran and kwt.
hope you guys can help me to solve this problem.thanks in advance

nilem
11-19-2011, 02:09 AM
See attachment, green Arrow, Sub ertert().

mancubus
11-19-2011, 02:36 AM
hi.

try:

Sub FindProductsQualityForsupplierier()

Dim ws As Worksheet, wsFront As Worksheet
Dim i As Long, LR As Long
Dim supplier As String

Application.ScreenUpdating = False

Set wsFront = Worksheets("Result")

With wsFront
.Range("A5").CurrentRegion.ClearContents
supplier = .Range("A2").Value
.Range("A4:F4").Value = Array("Supplier", "Jenis", "Ukuran", "Kwt", "Quantity", "Price")
End With

If supplier = "" Then
MsgBox "Please Enter Supplier Name", vbCritical + vbOKOnly, "W A R N I N G"
Exit Sub
End If

For Each ws In Worksheets
If ws.Name <> wsFront.Name Then
With ws
i = 4
Do
If .Cells(i, "D").Value = supplier Then
LR = wsFront.Cells(Rows.Count, "A").End(xlUp).Row + 1
wsFront.Range("A" & LR & ":F" & LR) = Array(.Range("D" & i), _
.Range("I" & i), _
.Range("K" & i), _
.Range("M" & i), _
.Range("R" & i), _
.Range("S" & i))
End If
i = i + 1
Loop Until .Cells(i, "D").Value = ""
End With
End If
Next

wsFront.Range("A1").Activate

Application.ScreenUpdating = True

End Sub

mtsf26
11-19-2011, 07:57 AM
See attachment, green Arrow, Sub ertert().
thanks for reply.hey is it possible if then when we click blue arrow then the file will show the result from urs only?since in here i should put the the name of supplier 1st then click blue arrow then greenm arrow.

mancubus
11-19-2011, 04:35 PM
hi.

try:

Sub FindProductsQualityForsupplierier()

End Sub


it seems i completely misunderstood the requirement, thinking it is about worksheet consolidation.
:think: :think:

nilem
11-19-2011, 11:19 PM
May be this? See attachment.

mtsf26
11-20-2011, 01:00 AM
@mancubus its ok.heheh.btw thanks for helping me too. :)
@nilem..hi nilem thanks for helping me and it work..