Ray.Mason
06-08-2013, 07:12 AM
Hi Guys,
I have code below which is a part of a macro that I use to generate reports from an excel scheduling tool. How can I ensure that once function "TaskActive" is true in "E" & HH range then update word table, exit and move on to next code. What is happening at the moment is the word table is being updated by the number of times condition is true, .i.e. if TaskActive function is true 3 times in "E" & HH range the table is being updated 3 times whereas it should only be once.
For Each rng In rngOutputColC.Offset(, 9).Cells
If Len(Range("A" & rng.Row).Text) Then
For HH = rng.Row To rng.Row + 100
If Range("E" & HH).Value = "" Then
Exit For
Else
If TaskActive(Range("E" & HH).Value) Then
If rng.Text = "G" Then
lngNrOfRows = lngNrOfRows + 1
If lngNrOfRows > t.Rows.Count Then t.Rows.Add
'column 1
t.Cell(lngNrOfRows, 1).Range.Text = Split(Range("A" & rng.Row).Text, ":")(0)
'column 2
t.Cell(lngNrOfRows, 2).Range.Text = Split(Range("A" & rng.Row).Text, ":")(1)
'column 3
t.Cell(lngNrOfRows, 3).Range.Text = Format(Range("I" & rng.Row).Offset(1, 0).Value, "dd/mm/yyyy")
'column 4
t.Cell(lngNrOfRows, 4).Range.Text = Format(Range("J" & rng.Row).Value, "dd/mm/yyyy")
'column 5
t.Cell(lngNrOfRows, 5).Range.Text = Range("G" & rng.Row).Value
'column 6
t.Cell(lngNrOfRows, 6).Range.Text = Format(Range("K" & rng.Row).Value / Range("G" & rng.Row).Value, "00%")
t.Cell(lngNrOfRows, 6).Shading.BackgroundPatternColor = wdColorGreen
'column 7
t.Cell(lngNrOfRows, 7).Range.Text = Range("A2").Value
End If
End If
End If
Next
End If
Next
Many thanks!
R
I have code below which is a part of a macro that I use to generate reports from an excel scheduling tool. How can I ensure that once function "TaskActive" is true in "E" & HH range then update word table, exit and move on to next code. What is happening at the moment is the word table is being updated by the number of times condition is true, .i.e. if TaskActive function is true 3 times in "E" & HH range the table is being updated 3 times whereas it should only be once.
For Each rng In rngOutputColC.Offset(, 9).Cells
If Len(Range("A" & rng.Row).Text) Then
For HH = rng.Row To rng.Row + 100
If Range("E" & HH).Value = "" Then
Exit For
Else
If TaskActive(Range("E" & HH).Value) Then
If rng.Text = "G" Then
lngNrOfRows = lngNrOfRows + 1
If lngNrOfRows > t.Rows.Count Then t.Rows.Add
'column 1
t.Cell(lngNrOfRows, 1).Range.Text = Split(Range("A" & rng.Row).Text, ":")(0)
'column 2
t.Cell(lngNrOfRows, 2).Range.Text = Split(Range("A" & rng.Row).Text, ":")(1)
'column 3
t.Cell(lngNrOfRows, 3).Range.Text = Format(Range("I" & rng.Row).Offset(1, 0).Value, "dd/mm/yyyy")
'column 4
t.Cell(lngNrOfRows, 4).Range.Text = Format(Range("J" & rng.Row).Value, "dd/mm/yyyy")
'column 5
t.Cell(lngNrOfRows, 5).Range.Text = Range("G" & rng.Row).Value
'column 6
t.Cell(lngNrOfRows, 6).Range.Text = Format(Range("K" & rng.Row).Value / Range("G" & rng.Row).Value, "00%")
t.Cell(lngNrOfRows, 6).Shading.BackgroundPatternColor = wdColorGreen
'column 7
t.Cell(lngNrOfRows, 7).Range.Text = Range("A2").Value
End If
End If
End If
Next
End If
Next
Many thanks!
R