Copy Cell Value down For Each Matching Value in A (PartB)

**bdb1974** · 06-18-2009, 12:32 PM

Hi guys, in Reference to my last thread,

http://www.excelforum.com/excel-prog...ml#post2111409

I've got an unsolved issue.

I need help in bringing in the correct values in column U of my output sheet.
Donkey Ote has provided me a macro that will correct the values in columns
H:N. The macro, if rewritten to fit into my sub macro "Sub ActionNonCompleted(LookinPath)" (please see the attached example booklet
in my last thread), Then this will fix the issue of my values being placed in "U".

However, if I can't get this done, then I need a code that will insert down column U, the value in U for the first of the same value found in column A.

Thanks for anyones help.

bdb

**bdb1974** · 06-19-2009, 01:16 PM

Can something like this work?


Sub Correct_Status_For_Each_Row()
  Dim OutPL As Worksheet
  Set OutPL = Sheets("Non_Completed")
  Set fso = CreateObject("Scripting.filesystemobject")
  OutPL.Activate
  ThisWorkbook.Activate
Dim FormHolder As String
For i = 9 To Cells(Rows.Count, 1).End(xlUp).Row
k=WorksheetFunction.CountIf(Range("A9:A"), Cells(i, 1).Value) 
WorksheetFunction.CountIf(Range("A9:A"), Cells(i, 1).Value)=1 

FormHolder = Range("U" & i).formula
OutPL.Cells(i, "U").Value = Cells(k, "U").Value
ElseIf Not IsEmpty(Range("A" & i)) Then
Range("U" & i).formula = FormHolder
End If
Next i

End Sub

**bdb1974** · 06-19-2009, 03:28 PM

How can I combine these two statements into one?
I need the Find(what:=Cell(i,1... part to be the first cell found for each value
listed in U. Or for each cell(i,1)Value=1 in Range (A9:9),then copy and paste the value in U down the column for each value i= 2 to lastrow found.

Set findit = Sheets("Non_Completed").Range("A:A").Find(what:=Cells(i, 1).Value)
If WorksheetFunction.CountIf(Range("A:A"), Cells(i, 1).Value) = 1 Then

Thanks for anyones help.

bdb

**bdb1974** · 06-19-2009, 05:49 PM

I'm surprised that no has responded.
This does not seem it should be very difficult to do.

I'll keep playing around with this myself, hopefully, i'll come up with something.
It's just really difficult when you don't know the VBA

**bdb1974** · 06-20-2009, 12:50 PM

...still have not found any working solution.

Let me try and explain again what i need.

If my worksheet has the following:
Row1 columnA and column U
1 10000 Good
2 10000
3 20000 Need Soon
4 20000
5 33300 Good
6 33300
7 44444 Get Now
8 44444
9 44444
10 44444
11 65656 Need Soon
12 65656
13 65656
14 65656
15 88080 Good
16 88080
to
Last row

If want the following result
Row1 columnA and column U
1 10000 Good
2 10000 Good
3 20000 Need Soon
4 20000 Need Soon
5 33300 Good
6 33300 Good
7 44444 Get Now
8 44444 Get Now
9 44444 Get Now
10 44444 Get Now
11 65656 Need Soon
12 65656 Need Soon
13 65656 Need Soon
14 65656 Need Soon
15 88080 Good
16 88080 Good
to
Last row

I hope this explains what I need.

Thanks.

**shg** · 06-20-2009, 06:26 PM

Sub x()
    Dim r1 As Range, r2 As Range
    
    Set r1 = Intersect(Range("A1", Cells(Rows.Count, "A").End(xlUp)).EntireRow, Columns("U"))
    
    If WorksheetFunction.CountBlank(r1) = 0 Then Exit Sub
    Set r2 = r1.SpecialCells(xlCellTypeBlanks)
    
    r2.FormulaR1C1 = "=r[-1]c"
    r1.Value2 = r1.Value2
End Sub

**bdb1974** · 06-23-2009, 02:23 PM

Thanks SHG.
This is almost what I need. I guess I didn't setup a good true example.
It needs to work also if there are another value in the cells below the first row of each item.
If there is no value in A, then U should remain blank.
This is starting on row 9 down to last row with value in A.

Here's an example of what needs fixed.

Row1 columnA and column U
1 10000 Good
2 10000 Need Soon
3 20000 Need Soon
4 20000 Warning very Low
5 33300 Good
6 33300 Need Soon
7 44444 Get Now
8 44444 Get Now
9 44444 Get Now
10 44444 Unacceptale level
11 65656 Need Soon
12 65656 Need Soon
13 65656 Unacceptable level
14 65656 Unacceptable level
15 88080 Good
16 88080 Need Soon
to
Last row

and the fixed result should be:

1 10000 Good
2 10000 Good
3 20000 Need Soon
4 20000 Need Soon
5 33300 Good
6 33300 Good
7 44444 Get Now
8 44444 Get Now
9 44444 Get Now
10 44444 Get Now
11 65656 Need Soon
12 65656 Need Soon
13 65656 Need Soon
14 65656 Need Soon
15 88080 Good
16 88080 Good

**shg** · 06-23-2009, 02:41 PM

That's a completely different example.

**bdb1974** · 06-24-2009, 10:16 AM

SHG - Thanks again for your assistance

For a better setup example please use this link.

http://www.excelforum.com/excel-prog...ml#post2111409

You should find an attachment of the sheet layout.

Hopefully, you or someone will now be able help. I'm hoping there's enough

example codes to give guidance in direction of what I need to have the code

do.

Thanks and anymore attempts to help is welcome.

BDB