Modify the value in one cell if another cell contains a certain value

[zack8576]

I need this macro to make sure there is a letter "J" at the end of the value in column D if column K contains "Riser", "Cone", "Riser", and column N contains "Brevard"
see highlighted below, F14232 would be F14232J, F14424Z would be F14424ZJ...etc

code posted below, any help is appreciated

 Public c As Long, lr As Long
Sub BrevardCounty()
 
    For Each c In ws1.Range("N2:N" & lr) 
        If c Like "*Brevard*" Then Brevard = c.Offset(, -9)
            For Each d In ws1.Range("K2:K" & lr)
                If d Like "*4'*" And d Like "*Riser*" Or _
                   d Like "*4'*" And d Like "*Top Slab*" Or _
                   d Like "*4'*" And d Like "*Cone*" Then
                    If d.Offset(, -9) = Brevard Then
                        If Right(d.Offset(, -7), 1) <> "J" Then
                            d.Offset(, -7) = d.Offset(, -7) & "J"
                        End If
                    End If
                End If
            Next d
    Next c
End Sub

2022070041 MODIFIED BREVARD CO.csv

Shared with Dropbox

www.dropbox.com

 

[ouvay]

Hey,

You were almost there.. I've fixed your code to do what you expected... although there was some discrepancies with what you described and the way the code was written.

For example

if column K contains "Riser", "Cone", "Riser",

but the way the code was provided was

If d Like "*4'*" And d Like "*Riser*" Or _ d Like "*4'*" And d Like "*Top Slab*" Or _ d Like "*4'*" And d Like "*Cone*" Then

In this case, the expected behaviour is as the code is written..
so if column K contains 4" AND Top Raise/Top Slab/Cone
and Brevard County..
you'll get what you want.


here you go.

Public c As Long, lr As Long

Sub BrevardCounty()
    Dim c As Variant
    For Each c In Sheet1.Range("N2:N" & Sheet1.Cells(Rows.Count, 1).End(3).Row)
        If c Like "*Brevard*" Then Brevard = c.Offset(, -9)
        For Each d In Sheet1.Range("K2:K" & Sheet1.Cells(Rows.Count, 1).End(3).Row)
            If d Like "*4'*" And d Like "*Riser*" Or _
               d Like "*4'*" And d Like "*Top Slab*" Or _
               d Like "*4'*" And d Like "*Cone*" Then
                If d.Offset(, 3) Like "*Brevard*" Then
                    If Right(d.Offset(, -7), 1) <> "J" Then
                        d.Offset(, -7) = d.Offset(, -7) & "J"
                    End If
                End If
            End If
        Next d
    Next c
End Sub

 

[zack8576]

 If c Like "*Brevard*" Then Brevard = c.Offset(, -9)

Interesting, VBA is stating "Brevard" is not defined now

 

[zack8576]

Hey,

You were almost there.. I've fixed your code to do what you expected... although there was some discrepancies with what you described and the way the code was written.

For example



but the way the code was provided was



In this case, the expected behaviour is as the code is written..
so if column K contains 4" AND Top Raise/Top Slab/Cone
and Brevard County..
you'll get what you want.


here you go.

Public c As Long, lr As Long

Sub BrevardCounty()
    Dim c As Variant
    For Each c In Sheet1.Range("N2:N" & Sheet1.Cells(Rows.Count, 1).End(3).Row)
        If c Like "*Brevard*" Then Brevard = c.Offset(, -9)
        For Each d In Sheet1.Range("K2:K" & Sheet1.Cells(Rows.Count, 1).End(3).Row)
            If d Like "*4'*" And d Like "*Riser*" Or _
               d Like "*4'*" And d Like "*Top Slab*" Or _
               d Like "*4'*" And d Like "*Cone*" Then
                If d.Offset(, 3) Like "*Brevard*" Then
                    If Right(d.Offset(, -7), 1) <> "J" Then
                        d.Offset(, -7) = d.Offset(, -7) & "J"
                    End If
                End If
            End If
        Next d
    Next c
End Sub

with couple more slight tweaking, this code is doing exactly what it is supposed to do now, working code posted here.

Public c As Range, Brevard As String, d As Range
Sub BrevardCounty()
    Dim c As Variant
    For Each c In Range("N2:N" & Cells(Rows.Count, 1).End(3).Row)
        If c Like "*Brevard*" Or _
           c Like "*Brevard*" Then Brevard = c.Offset(, -9)
        For Each d In Range("K2:K" & Cells(Rows.Count, 1).End(3).Row)
            If d Like "*4'*" And d Like "*Riser*" Or _
               d Like "*4'*" And d Like "*Top Slab*" Or _
               d Like "*4'*" And d Like "*Cone*" Then
                If d.Offset(, 3) Like "*Brevard*" Then
                    If Right(d.Offset(, -7), 1) <> "J" Then
                        d.Offset(, -7) = d.Offset(, -7) & "J"
                    End If
                End If
            End If
        Next d
    Next c
End Sub

 

[ouvay]

awesome! glad you got it to work