change the value from 0 to 1 in a cell if these criterias are met

[zack8576]

I need to modify the value 0 to 1 in column H if on the same row: (I am munipulating csv files, and the macro is in a xlsb file)
1. F is exactly "P1"
2. column O contains keywords "P-" and 'STORM MANHOLE"

this should be very straight forward and it is not working..... help !

sample file and code below

2022100098.csv

Shared with Dropbox

www.dropbox.com

Sub ChangeValue()
    Dim i As Long, lastRow As Long
    lastRow = ws1.Cells(ws1.Rows.Count, "O").End(xlUp).Row
    For i = 2 To lastRow
        If Trim(ws1.Range("O" & i).Value) Like "*P-*" And _
            Trim(ws1.Range("O" & i).Value) Like "*STORM MANHOLE*" And _
            Trim(ws1.Range("F" & i).Value) Like "P1" Then
                ws1.Range("H" & i).Value = 1
        End If
    Next i
End Sub

below is the first part where the csv was opened and defined

Option Explicit        
Option Compare Text
    Public wb1 As Workbook, wb2 As Workbook
    Public ws1 As Worksheet, ws2 As Worksheet
Sub OpenQuoteFile()
    Dim csvFilePath As String, xlsmFilePath As String
    
    csvFilePath = Application.GetOpenFilename("CSV Files (*.csv),*.csv")
    If csvFilePath <> "False" Then
        Set wb1 = Workbooks.Open(csvFilePath)
        Set ws1 = wb1.Sheets(1)
    Else
        Exit Sub
    End If
    
    xlsmFilePath = Application.GetOpenFilename("XLSM Files (*.xlsm),*.xlsm")
    If xlsmFilePath <> "False" Then
        Set wb2 = Workbooks.Open(xlsmFilePath)
        Set ws2 = wb2.Sheets("QUOTE")
    Else
        wb1.Close SaveChanges:=False
        Exit Sub
    End If
    
    Dim i As Long
    For i = 19 To 46
        If ws2.Range("D" & i).Value <> "" Then
            ws2.Range("D" & i).Copy
            ws1.Range("S1:S28").NumberFormat = "@"
            ws1.Range("S1:S28").Value = ws2.Range("D19:D46").Value
            If ws2.Range("D" & i).MergeCells Then
                ws1.Range("S" & i - 28).UnMerge
            End If
            ws1.Range("S1:S28").EntireColumn.AutoFit
        End If
    Next i
    For i = 19 To 46
        If ws2.Range("E" & i).Value <> "" Then
            ws2.Range("E" & i).Copy
            ws1.Range("T1:T28").NumberFormat = "@"
            ws1.Range("T1:T28").Value = ws2.Range("E19:E46").Value
            If ws2.Range("E" & i).MergeCells Then
                ws1.Range("T" & i - 28).UnMerge
            End If
            ws1.Range("T1:T28").EntireColumn.AutoFit
        End If
    Next i
    For i = 19 To 46
        If ws2.Range("F" & i).Value <> "" Then
            ws2.Range("F" & i).Copy
            ws1.Range("U1:U28").NumberFormat = "@"
            ws1.Range("U1:U28").Value = ws2.Range("F19:F46").Value
            If ws2.Range("F" & i).MergeCells Then
                ws1.Range("U" & i - 28).UnMerge
            End If
            ws1.Range("U1:U28").EntireColumn.AutoFit
        End If
    Next i
    Application.CutCopyMode = False
    wb2.Close SaveChanges:=False
    Application.ScreenUpdating = True
End Sub

 

[Micron]

Not working means what? No result? Unexpected results? Error messages?
When i is any number (e.g. 2) how can O2 equal *P* and *STORM MANHOLE* at the same time? Not sure what that test is all about.

 

[Micron]

OK, I see that seems to work. There is only one record in the data that meets the requirement and it gets changed to 1 so I don't understand what the issue is.

 

[zack8576]

OK, I see that seems to work. There is only one record in the data that meets the requirement and it gets changed to 1 so I don't understand what the issue is.

it did work on your end? I ran it a few times and column H is still 0 ? below is the result after I ran it, H45 is still 0...

 

[Micron]

Yes, it worked for me (IIRC row 45 was the only change). Put a break point on the line that alters the 0 then run it. If it stops on that line it's going to execute it. If you press F8 it should execute it. If it doesn't stop there then I don't know what to suggest regarding no effect - except somewhere you turned off screen updating, so it changed but you're not seeing it. Closing and re-opening should reveal that. Alternatively, you could start the loop at 45 instead of 2, and step through. It should want to execute that line on the first pass.

 

[zack8576]

Yes, it worked for me (IIRC row 45 was the only change). Put a break point on the line that alters the 0 then run it. If it stops on that line it's going to execute it. If you press F8 it should execute it. If it doesn't stop there then I don't know what to suggest regarding no effect - except somewhere you turned off screen updating, so it changed but you're not seeing it. Closing and re-opening should reveal that. Alternatively, you could start the loop at 45 instead of 2, and step through. It should want to execute that line on the first pass.

so I did some more tests on it, and I got a break point on the line

ws1.Range("H" & i).Value = 1

when I stepped through the code, the value indeed changed from 0 to 1, but if I just run the complete code, the value does not change...
I am completely lost now, not sure how this sort of issue can be fixed..

 

[zack8576]

Yes, it worked for me (IIRC row 45 was the only change). Put a break point on the line that alters the 0 then run it. If it stops on that line it's going to execute it. If you press F8 it should execute it. If it doesn't stop there then I don't know what to suggest regarding no effect - except somewhere you turned off screen updating, so it changed but you're not seeing it. Closing and re-opening should reveal that. Alternatively, you could start the loop at 45 instead of 2, and step through. It should want to execute that line on the first pass.

now I feel really dumb, the mistake was in front of me, staring at me the entire time

I did not call the subroutine in the beginning of the code, so when I run the entire macro, this subroutine is not executed at all.........

I appreciate your help !

 

[Micron]

Glad you figured it out!