Creating a report-change text in D cell based upon text in E cell

lvchoc · Sep 19th 2019

I have created a few macros to automatic format and arrange date I place into my macro enabled spreadsheet. I pull these reports 10 times each Monday with different data but same data layout. I cannot figure out a macro I can execute to look at each cell in the E column and if it contains the word "Implement" replace the text in the D column with "Flagged". Same row in this example. I need the macro to check every cell in the E column, Can you help?

**alansidman** · Sep 19th 2019

Code

Option Explicit


Sub ivchoc()
    Dim c As Range
    Dim rng As Range, lr As Long
    lr = Range("E" & Rows.Count).End(xlUp).Row
    Set rng = Range("E1:E" & lr)
    For Each c In rng
        If c = "implement" Then
            c.Offset(0, -1) = "Flagged"
        End If
    Next c




End Sub

Display More

**norie** · Sep 19th 2019

Try this.

Code

Dim arrIn As Variant
Dim idxRow As Long


    With Sheets("Sheet1").CurrentRegion.Columns(4).Resize(,2)


         arrIn = .Value


         For idxRow = LBound(arrIn,1) To UBound(arrIn,1)
             If arrIn(idxRow, 2) = "Implement" Then
                 arrIn(idxRow,1) = "Flagged"
             End If
        Next idxRow


        .Value = arrIn


    End With

Display More

lvchoc · Sep 19th 2019

this is the code I tried and get a 438 error

Code

Sub Flagged()
Dim arrIn As Variant
Dim idxRow As Long


With Sheets("R3 Events & Hospitality Dept (U").CurrentRegion.Columns(4).Resize(, 2)


arrIn = .Value


For idxRow = LBound(arrIn, 1) To UBound(arrIn, 1)
If arrIn(idxRow, 2) = "Implement" Then
arrIn(idxRow, 1) = "Flagged"
End If
Next idxRow


.Value = arrIn


End With
Application.ScreenUpdating = True
End Sub

Display More

**Carim** · Sep 19th 2019

Hello,

The sheet name looks a bit strange ... just make sure you are using the exact same spelling as the one in your tab ....

**alansidman** · Sep 19th 2019

@lvchoc

Code Tags Added
Your post does not comply with our Forum RULES. Use code tags around code.

Posting code between

Code

tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window.

(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

lvchoc · Sep 19th 2019

I really appreciate all the help. Still getting an error. I changed my sheet name to a simple one and it matches. I have a total of 5 columns and I am targeting column 5 & 5, D & E. Here is the code. What am I doing wrong?

Sub ReplaceFlagged()
Dim arrIn As Variant
Dim idxRow As Long

With Sheets("RptTemplate").CurrentRegion.Columns(5).Resize(, 4)

arrIn = .Value

For idxRow = LBound(arrIn, 1) To UBound(arrIn, 1)
If arrIn(idxRow, 5) = "Impediment" Then
arrIn(idxRow, 4) = "Flagged"
End If
Next idxRow

.Value = arrIn

End With
Application.ScreenUpdating = True
End Sub

**Carim** · Sep 19th 2019

Hello,

Actually ... to be in a position to solve your problem ...

You should attach a small sample file ... which would allow to make a few tests ...:smile:

lvchoc · Sep 19th 2019

here is a sample of the data
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 73"]Issue key[/TD]
[TD="width: 319"]Summary[/TD]
[TD="width: 104"]Due Date[/TD]
[TD="width: 166"]Status[/TD]
[TD="width: 168"]Custom field (Epic Status)[/TD]

[/tr]

[tr]

[td]

EVENT-225

[/td]

[td]

Example 1

[/td]

[TD="align: right"]9/30/2019[/TD]

[td]

In Progress

[/td]

[td]

Impediment

[/td]

[/tr]

[tr]

[td]

EVENT-53

[/td]

[td]

Example 2

[/td]

[TD="align: right"]10/17/2019[/TD]

[td]

In Progress

[/td]

[td][/td]

[/tr]

[tr]

[td]

EVENT-155

[/td]

[td]

Example 3

[/td]

[TD="align: right"]10/24/2019[/TD]

[td]

In Progress

[/td]

[td][/td]

[/tr]

[tr]

[td]

EVENT-158

[/td]

[td]

Example 4

[/td]

[TD="align: right"]10/24/2019[/TD]

[td]

In Progress

[/td]

[td]

Impediment

[/td]

[/tr]

[tr]

[td]

EVENT-156

[/td]

[td]

Example 5

[/td]

[TD="align: right"]10/24/2019[/TD]

[td]

In Progress

[/td]

[td][/td]

[/tr]

[tr]

[td]

EVENT-157

[/td]

[td]

Example 6

[/td]

[TD="align: right"]10/24/2019[/TD]

[td]

In Progress

[/td]

[td][/td]

[/tr]

[/TABLE]

**Carim** · Sep 19th 2019

At the risk of repeating myself ...

Quote

You should attach a small sample file ... which would allow to make a few tests ...

lvchoc · Sep 19th 2019

Here is a sample

**Carim** · Sep 19th 2019

Thanks ...

Attached is your test file

Hope this will help

lvchoc · Sep 19th 2019

:party: That works wonderfully! Thank you everyone.

**Carim** · Sep 19th 2019

Great ... !!

Once your sample file was attached ... matter could be quickly solved ...:wink:

Creating a report-change text in D cell based upon text in E cell

Participate now!

Share