Change the code from looking in Row (5) and fill down to looking in Column (8) and filling across

[Nlhicks]

Requirement burndown sheet:

Test Tracking Test2 adding new section.xlsm
	H	I	J	K	L	M	N	O	P
5		Abort_12	Cold_Cut_2	Hot_Cut_1	Hot_Cut_3	Omega_1	Pharos_1	Hot_Cut_2	Cold_Cut_1
6	D_ANT_SFTY_01
7	D_ANT_SFTY_02
8	D_ANT_SFTY_03
9	BITO_1010
10	BITO_1011
11	BITO_1012
12	BITO_1013
13	QNTI_01
14	QNTI_02
15	QNTI_03
16	PLT_555
17	PLT_556
18	PLT_557
Requirement Burndown Table

Old Macro Estelle Tracking Sheet:

Test Tracking Test2 adding new section.xlsm
	A	B
1	Run One
2	Abort_12	Pass
3	Hot_Cut_1	Fail
4	Omega_1	Pass
5	Cold_Cut_1	Fail
6	Cold_Cut_2	Pass
7	Hot_Cut_2	Fail
8	Pharos_1	Pass
Estelle Tracking

Old Macro:

Sub Test2()
If ActiveSheet.Name <> "Estelle Tracking" Then
MsgBox "Run this from the Estelle Tracking Sheet.", 64, "Note:"
Exit Sub
End If

Application.ScreenUpdating = False
Dim cellEstelle As Range, strEstelle$, strPassFail$
Dim varFindScenario As Variant, lngFindScenario&, lngNextRow&, xRow&

With Sheets("Requirement Burndown Table")

For Each cellEstelle In Columns(1).SpecialCells(2)
Set varFindScenario = Nothing
strEstelle = cellEstelle.Value
Set varFindScenario = .Rows(5).Find(What:=strEstelle, LookIn:=xlFormulas, LookAt:=xlWhole)

If Not varFindScenario Is Nothing Then
lngFindScenario = varFindScenario.Column
'Modify thse column number boundaries as needed.
If lngFindScenario >= 5 And lngFindScenario <= 26 Then
If Cells(cellEstelle.Row, 2).Value = "Pass" Then
strPassFail = "+"
Else
strPassFail = "-"
End If

For xRow = 6 To 18
If Len(.Cells(xRow, lngFindScenario).Value) = 0 And .Cells(xRow, lngFindScenario).Interior.ColorIndex = -4142 Then .Cells(xRow, lngFindScenario).Value = strPassFail
Next xRow

End If
End If
Next cellEstelle

End With

Set varFindScenario = Nothing
Application.ScreenUpdating = True
MsgBox "Completed.", , "Done."
End Sub

Sheet1:

Test Tracking Test2 adding new section.xlsm
	H	I	J	K	L	M	N	O	P
5		Abort_12	Cold_Cut_2	Hot_Cut_1	Hot_Cut_3	Omega_1	Pharos_1	Hot_Cut_2	Cold_Cut_1
6	D_ANT_SFTY_01
7	D_ANT_SFTY_02
8	D_ANT_SFTY_03
9	BITO_1010
10	BITO_1011
11	BITO_1012
12	BITO_1013
13	QNTI_01
14	QNTI_02
15	QNTI_03
16	PLT_555
17	PLT_556
18	PLT_557
19	DPIP_01
Sheet1

Sheet 2:

Test Tracking Test2 adding new section.xlsm
	A	B
1	One
2	D_ANT_SFTY_01	Pass
3	D_ANT_SFTY_02	Fail
4	D_ANT_SFTY_03	Pass
5	BITO_1010	Pass
6	BITO_1011	Pass
7	BITO_1012	Pass
8	BITO_1013	Pass
9	QNTI_01	Pass
10	QNTI_02	Fail
11	QNTI_03	Pass
12	PLT_555	Pass
13	PLT_556	Pass
14	PLT_557	Pass
15	DPIP_01	Fail
Sheet2

New Macro that is not working right I think the problem is in the underlined and bolded section :

Sub Test2()
If ActiveSheet.Name <> "Sheet2" Then
MsgBox "Run this from the Sheet 2.", 64, "Note:"
Exit Sub
End If

Application.ScreenUpdating = False
Dim cellEstelle As Range, strEstelle$, strPassFail$
Dim varFindScenario As Variant, lngFindScenario&, lngNextCol&, xCol&

With Sheets("Sheet1")

For Each cellEstelle In Columns(1).SpecialCells(2)
Set varFindScenario = Nothing
strEstelle = cellEstelle.Value
Set varFindScenario = .Columns(8).Find(What:=strEstelle, LookIn:=xlFormulas, LookAt:=xlWhole)

If Not varFindScenario Is Nothing Then
lngFindScenario = varFindScenario.Row
'Modify thse column number boundaries as needed.
If lngFindScenario >= 9 And lngFindScenario <= 16 Then
If Cells(cellEstelle.Row, 2).Value = "Pass" Then
strPassFail = "+"
Else
strPassFail = "-"
End If

For xCol = 6 To 20
If Len(.Cells(xCol, lngFindScenario).Value) = 0 And .Cells(xCol, lngFindScenario).Interior.ColorIndex = -4142 Then .Cells(xCol, lngFindScenario).Value = strPassFail
Next xCol

End If
End If
Next cellEstelle

End With

Set varFindScenario = Nothing
Application.ScreenUpdating = True
MsgBox "Completed.", , "Done."
End Sub

 

[Nlhicks]

I got it to start looking at the right criteria but now the + and - are not going into the correct place. The underlined and bolded section seems to be the place that I am experiencing difficulty.
Does some one have a fix for that?

 

[Nlhicks]

Can someone please help? I have to take whatever I find here and extrapolate it out to a huge project that I have been working on since last Christmas.

 

[Nlhicks]

Hello, please help

 

[NoSparks]

I think your best bet would be to tell us what you expect it to do and what it actually is or isn't doing.

 

[Nlhicks]

No Sparks, This is what the bottom code is doing. It is pasting in a + or - but not with the correct result. According to the special cell D_ANT_SFTY_01 should have a + in every empty cell in that row but as you can see it has a - in one of them.

Test Tracking Test2 adding new section.xlsm
	H	I	J	K	L	M	N	O	P
6	D_ANT_SFTY_01	+			+	+	-
7	D_ANT_SFTY_02		+				-	+
8	D_ANT_SFTY_03
9	BITO_1010	+		+
10	BITO_1011	+	+		+	+		+	+
11	BITO_1012		+	+			-	+
12	BITO_1013	+			+	+			+
13	QNTI_01
14	QNTI_02	+			+	+			+
15	QNTI_03					+			+
16	PLT_555		+	+			-	+
17	PLT_556	+			+	+			+
18	PLT_557
19	DPIP_01	+			+	+	-
20		+	+	+	+	+	-	+	+
Sheet1

 

[johnnyL]

I haven't tested your code but I would say a problem is:

For xCol = 6 To 20
If Len(.Cells(xCol, lngFindScenario).Value) = 0 And .Cells(xCol, lngFindScenario).Interior.ColorIndex = -4142 Then .Cells(xCol, lngFindScenario).Value = strPassFail
Next xCol

cells uses (rows, columns) format. When you changed from the old code, you switched the names from row to col, but you didn't flip flop the variables in the parentheses.

I also think the 6 in your counter should be 9 to reflect the 9th column and the 20 should be 16 for the P column?

 

[Nlhicks]

I haven't tested your code but I would say a problem is:

For xCol = 6 To 20
If Len(.Cells(xCol, lngFindScenario).Value) = 0 And .Cells(xCol, lngFindScenario).Interior.ColorIndex = -4142 Then .Cells(xCol, lngFindScenario).Value = strPassFail
Next xCol

cells uses (rows, columns) format. When you changed from the old code, you switched the names from row to col, but you didn't flip flop the variables in the parentheses.

I will try that and see what happens

 

[Nlhicks]

I haven't tested your code but I would say a problem is:

For xCol = 6 To 20
If Len(.Cells(xCol, lngFindScenario).Value) = 0 And .Cells(xCol, lngFindScenario).Interior.ColorIndex = -4142 Then .Cells(xCol, lngFindScenario).Value = strPassFail
Next xCol

cells uses (rows, columns) format. When you changed from the old code, you switched the names from row to col, but you didn't flip flop the variables in the parentheses.

I also think the 6 in your counter should be 9 to reflect the 9th column and the 20 should be 16 for the P column?

I haven't tested your code but I would say a problem is:

For xCol = 6 To 20
If Len(.Cells(xCol, lngFindScenario).Value) = 0 And .Cells(xCol, lngFindScenario).Interior.ColorIndex = -4142 Then .Cells(xCol, lngFindScenario).Value = strPassFail
Next xCol

cells uses (rows, columns) format. When you changed from the old code, you switched the names from row to col, but you didn't flip flop the variables in the parentheses.

I also think the 6 in your counter should be 9 to reflect the 9th column and the 20 should be 16 for the P column?

Thank you sooooo very much JohnnyL, that was the solution. It works perfectly now, You are outstanding. You made my whole day, week and I am pretty sure month.

 

[johnnyL]

You are welcome @Nlhicks.