> Seems to be too difficult to be answered
It is not too difficult to be answered - you just need to provide a proper
description.
Please start again and describe it in detail. Do not try to condense it
down into a paragraph. It is reasonably complex, and will take several
paragraphs to describe.
In one of your replies you mentioned fruits. If this is what you're really
talking about, then please describe your problem in terms of those fruits,
suppliers, customers, etc.
Please do not assume that terms such as "distribution" are well known terms
and will be understood by others. Describe exactly how the information will
be transfered from one sheet to the other. In particular, you need to be
specific about how you can end up with fewer items on the second sheet than
on the first sheet, and what is to happen to the remaining items.
I think you are also confusing things by calling your second sheet a
"result" sheet. It doesn't appear to contain results - merely the copying
of EDI numbers across. Am I right in thinking that the first sheet is a
list a suppliers or producers, and the second sheet is a list of customers
and/or orders?
Regards, Dave S
----- Original Message -----
From: ayaz khan ayazthegreat2001@yahoo.com [ExcelVBA]
To: ExcelVBA@yahoogroups.com
Sent: Sunday, December 07, 2014 11:15 PM
Subject: Re: [ExcelVBA] Help required for a difficult macro
Seems to be too difficult to be answered
Sent from Yahoo Mail on Android
From:"ayaz khan ayazthegreat2001@yahoo.com [ExcelVBA]"
<ExcelVBA@yahoogroups.com>
Date:Sun, 7 Dec, 2014 at 1:23 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
IDDI#EDI #Qty
A11-A12452
A11-A13451
A11-A15451
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
IDEDI #Qty
A11A12452
A11A13451
A11A154510
A16B45673
A16B678930
Result Sheet
IDDI#EDI #Qty
A11-A12452
A11-A13451
A11-A15451
A1223451
A1332451
A16-B45673
A16-B67892
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 -----
From: ayaz khan ayazthegreat2001@yahoo.com [ExcelVBA]
To: ExcelVBA@yahoogroups.com
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
IDEDI #Qty
A11A12452
A11A13451
A11A154510
A16B45673
A16B678930
Result Sheet
IDDI#EDI #Qty
A11-A12452
A11-A13451
A11-A15451
A1223451
A1332451
A16-B45673
A16-B67892
A16-B67891
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 (12) |
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