Minggu, 07 Desember 2014

Re: [ExcelVBA] Help required for a difficult macro

 



Alright, let me have a go at stating what I think might be the requirement.
 
Sheet 1 contains information about items (let's call them fruit) being supplied from different sources (let's call them growers).
 
Sheet1
Fruit Grower Tonnes
Apples Fred 2
Apples John 1
Apples Jim 10
Oranges Tom 3
Oranges Harry 30
 
Sheet 2 contains orders for fruit that are to be filled by the above suppliers
 
Sheet 2
Fruit DI# Grower Tonnes ordered
Apples - Fred 2
Apples - John 1
Apples - Jim 1
Lemons 2345 1
Bananas 3245 1
Oranges - Tom 3
Oranges - Harry 2
 
where the suppliers are filled in from top-to-bottom to indicate who supplies which order.
 
Is this something like right?
 
But this is only a tiny part of the transformation.  The following questions - plus many more - need to be addressed:
 
- What would happen if, for instance, Fred had supplied only 1 tonne and John had supplied 2 tonnes?  Would John then supply the whole of the first order, or would it still be worked top-to-bottom, with Fred and John supplying half each?
 
- What happens to the unused portions of the supplies?  They need to be accounted for in some way.
 
- Where is the receiver indicated in Sheet 2?  After all, this is a distribution (?) of the fruit from the supplier to the receiver (consumer?)
 
- What is this "DI#" that is used to stop A12 and A13 (Lemons and Bananas in my example) from being filled in?  It is not explained and makes no sense.
 
Ayaz ... the above is an attempt to make some sense of what you are asking.  It is undoubtedly incorrect in some or many aspects.  You need to provide something similar that describes your actual problem.
 

Regards, Dave S
 
----- Original Message -----
Sent: Sunday, December 07, 2014 6:51 PM
Subject: Re: [ExcelVBA] Help required for a difficult macro

more explanation there are total 13 qty against ID A11 having different EDI # in sheet1 but result is demanding 4 qty against ID A11 so we distribute data like below

ID DI# EDI # Qty
A11 - A1245 2
A11 - A1345 1
A11 - A1545 1


On Sunday, December 7, 2014 7:46 AM, "ayaz khan ayazthegreat2001@yahoo.com [ExcelVBA]" <ExcelVBA@yahoogroups.com> wrote:


 
thanks to replay but result is not as per my requirement 


On Sunday, December 7, 2014 2:02 AM, "'Tim Lewis' twlewis@reagan.com [ExcelVBA]" <ExcelVBA@yahoogroups.com> wrote:


 
Ayaz, can you see if this is what you are looking for? 
 
Option Explicit
Sub Get_Quantity()
' This subroutine will go through the list of items in the Result Sheet
' It will find items that do not have a quantity and get the quantity from Sheet1
' If the quantity in DI# does not match the quantity in Qty it will mark the cell in red
'
'
'Assign the sheet names to a variable so that if the sheet names change, it will only have to be
'  changed once in the code.
Dim ResultsSheet As String, SearchSheet As String
ResultsSheet = "Result Sheet"
SearchSheet = "Sheet1"
 
'Variable Used
Dim ResultsRowCount As Single, SearchRowCount As Single, ResultsSheetTotalRowCount As Single, _
    SearchSheetTotalRowCount As Single, DIValue As Single
Dim strResultsEDINumber As String, strResultsDINumber As String, strResultsQty As String
Dim strSearchEDINumber As String, strSearchQty As String
Dim strResultsCurrentRow As String, strSearchCurrentRow As String
 
ResultsSheetTotalRowCount = Sheets(ResultsSheet).UsedRange.Rows.Count
SearchSheetTotalRowCount = Sheets(SearchSheet).UsedRange.Rows.Count
   
For ResultsRowCount = 2 To ResultsSheetTotalRowCount
    'Convert the counter to a string
    strResultsCurrentRow = LTrim(Str(ResultsRowCount))
    strResultsDINumber = Sheets(ResultsSheet).Range("B" & strResultsCurrentRow).FormulaR1C1
    strResultsEDINumber = Sheets(ResultsSheet).Range("C" & strResultsCurrentRow).FormulaR1C1
    strResultsQty = Sheets(ResultsSheet).Range("D" & strResultsCurrentRow).FormulaR1C1
    'Get the numeric value of the DI entry. This is done to skip any that have a numeric value
    DIValue = Val(strResultsDINumber)
    If DIValue = 0 Then
        For SearchRowCount = 2 To SearchSheetTotalRowCount
            strSearchCurrentRow = LTrim(Str(SearchRowCount))
            strSearchEDINumber = Sheets(SearchSheet).Range("B" & strSearchCurrentRow).FormulaR1C1
            strSearchQty = Sheets(SearchSheet).Range("C" & strSearchCurrentRow).FormulaR1C1
            If strResultsEDINumber = strSearchEDINumber Then
                Sheets(ResultsSheet).Range("B" & strResultsCurrentRow).FormulaR1C1 = strSearchQty
                'Exit the For loop because there is no need to continue after the entry is found
            
            End If
        Next
    End If
    'Get the current DI value because it may have changed
    strResultsDINumber = Sheets(ResultsSheet).Range("B" & strResultsCurrentRow).FormulaR1C1
    'If the DI value MATCHES the quantity then do NOT color the cell.
    If strResultsDINumber = strResultsQty Then
        Sheets(ResultsSheet).Range("B" & strResultsCurrentRow).Interior.Pattern = xlNone
        Sheets(ResultsSheet).Range("B" & strResultsCurrentRow).Interior.TintAndShade = 0
        Sheets(ResultsSheet).Range("B" & strResultsCurrentRow).Interior.PatternTintAndShade = 0
    Else
        ' If the DI does NOT MATCH the quantity then color the cell red.
        Sheets(ResultsSheet).Range("B" & strResultsCurrentRow).Interior.Pattern = xlSolid
        Sheets(ResultsSheet).Range("B" & strResultsCurrentRow).Interior.PatternColorIndex = xlAutomatic
        Sheets(ResultsSheet).Range("B" & strResultsCurrentRow).Interior.Color = 255
        Sheets(ResultsSheet).Range("B" & strResultsCurrentRow).Interior.TintAndShade = 0
        Sheets(ResultsSheet).Range("B" & strResultsCurrentRow).Interior.PatternTintAndShade = 0
    End If
   
Next
 
End Sub
 
 
Tim
 
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com]
Sent: Friday, December 05, 2014 11:36 PM
To: ExcelVBA@yahoogroups.com
Subject: Re: [ExcelVBA] Help required for a difficult macro
 
Dave thanks for your reply , but let me explain you more about this data, actually sheet1 is source data which is distributing/copying data in result sheet as per given Qty  of result sheet in col D like as you mentioned that  ("(1) A1545 has quantity 10 in Sheet 1, but only 1 in the result sheet.") this is because  in result sheet the Qty in D col requirement is 1 and this is logic i want to communicate.
 
(2) B6789 has one entry in Sheet 1) sorry it is wrong entry.
 
Sir, i can explain you more in case of any confusion.
 
 
Sheet1
ID
EDI #
Qty
A11
A1245
2
A11
A1345
1
A11
A1545
10
A16
B4567
3
A16
B6789
30
 
Result Sheet
ID
DI#
EDI #
Qty
A11
-
A1245
2
A11
-
A1345
1
A11
-
A1545
1
A12
2345
1
A13
3245
1
A16
-
B4567
3
A16
-
B6789
2
 
On Saturday, December 6, 2014 4:05 AM, "'David Smart' smartware.consulting@gmail.com [ExcelVBA]" <ExcelVBA@yahoogroups.com> wrote:
 
 

Sorry, I can't see how you go from your Sheet1 to your result sheet.
 
(1) A1545 has quantity 10 in Sheet 1, but only 1 in the result sheet.
 
(2) B6789 has one entry in Sheet 1, but two entries in the result sheet.  Also, however, the quantity 30 has become quantity 2 and quantity 1 - what about the other 27?
 
Please explain how Sheet 1 transforms into the entries in the result sheet, with particular emphasis on the ones that don't have the same quantity, and on the ones that have more than one entry in the result sheet.

Regards, Dave S
 
----- Original Message -----
Sent: Saturday, December 06, 2014 6:44 AM
Subject: [ExcelVBA] Help required for a difficult macro
 
I need a macro for my result sheet , let me explain you that i have sheet1 and there are 3 fields from col a to c and i need a macro for result sheet which matches ID number from result sheet col a (where DI# is blank in col b) with sheet1 col a and copy/distribute b col value from sheet1 to result sheet in col c which must be equal d col value , ok please see below example in red highlighted font cells as a macro result.
Sheet1
ID
EDI #
Qty
A11
A1245
2
A11
A1345
1
A11
A1545
10
A16
B4567
3
A16
B6789
30
 
Result Sheet
ID
DI#
EDI #
Qty
A11
-
A1245
2
A11
-
A1345
1
A11
-
A1545
1
A12
2345
1
A13
3245
1
A16
-
B4567
3
A16
-
B6789
2
A16
-
B6789
1
 
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2015.0.5577 / Virus Database: 4235/8682 - Release Date: 12/04/14
 




No virus found in this message.
Checked by AVG - www.avg.com
Version: 2015.0.5577 / Virus Database: 4235/8693 - Release Date: 12/06/14

__._,_.___

Posted by: "David Smart" <smartware.consulting@gmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (13)
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

.

__,_._,___

Tidak ada komentar:

Posting Komentar