Find Cell Value. and perform Loop

[PNGBill]

Hi Forum,

I need to check records in an Excel 2016 file - Column D.
If this is "X" Then I need to save the Cell Value Offset -4 - Column A to Variable "SORef"
Then I need to Loop through all Rows in Column A with the same "SORef and change the Cell Value for Column D to "X"
This may mean checking Up and Down.

Should be simple but I am new to Excel VBA and cannot seem to get this started

Appreciate any support.
Column is

 

[June7]

I often 'get started' with Excel VBA coding by macro recording. Perform steps manually then modify the generated code as needed. I used recorder to get the Find syntax then figured out the rest. So maybe like:

Sub Macro1()
Dim SORef As String, intRow As Integer
Cells.Find(What:="X", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate
SORef = ActiveCell.Offset(0, -3)
With Worksheets("Sheet1")
    intRow = 1
    While Not IsEmpty(.Cells(intRow, 1))
        If .Cells(intRow, 1) = SORef Then .Cells(intRow, 4) = "X"
        intRow = intRow + 1
    Wend
End With
End Sub

 

[PNGBill]

Thanks June7,
Was hoping for some basic code examples and then I can take it from there.

Became skilled at Access VBA although not used same for 5 years
but Excel has different commands.

 

[Gasman]

Not that simple.?
Eg row D2 has an X in it.? A2 has a value of D2 let's say.
You then filter column A for D2 and then change column D to X for the filtered set

Now you unfilter and look for an X in column D ?

How do you know when you have finished.?
How do you avoid the rows already processed.?

 

[CJ_London]

would think something like this

dim I as integer
 
for I=2 to maxrows
     if cells(I,1)=SORef then cells(I,4)="X"
next i

you need to determine maxrows, but you have not provided any information on where your data is or how it is structured, so assuming it is a table format with no blank rows or columns, with headers in row 1 and data below, it can be determined using currentregion

maxrows=range("A1").currentregion.rows

 

[June7]

I edited my previous post after you read it.

 

[PNGBill]

Here is my code so far.

It works to find the Rows with "x" in Column 5 - E.
It appears to identify the Value in Column 1 - A for this Row.

I have inserted i2 to then process the worksheet to change the value in E where the value of A is SORef.

Of the 20 records it should have added x to all rows after finding x
:banghead:

Appreciate any support.

Sub IdentifyInstalls()

' Get the last row with text
    Dim shNames As Worksheet
    Set shNames = ThisWorkbook.Worksheets("Sheet1")
    Dim StartRow As Long
    Dim LastRow As Long
    LastRow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row
    StartRow = 2
    
    'Temp Last Row for Testing Purposes
    LastRow = 20
            
    'Check for "x" in Column E
    Dim i As Long
    Dim i2 As Long
    Dim SORef As Long
            
    'Go through Rows and check for "x"
    'For i = StartRow To LastRow
   
    For i = StartRow To LastRow Step 1
        If Sheet1.Cells(i, 5).Value = "x" Then
            SORef = ActiveCell.Offset(i, -4)
                For i2 = StartRow To LastRow Step 1
                If Sheet1.Cells(i2, 1).Value = SORef Then Sheet1.Cells(i2, 1).Offset(0, 4).Value2 = "x"
                Next i2
        End If
    Next
      Debug.Print "Task Complete"
End Sub

 

[June7]

Not clear to me what issue is. Does the code error, produce wrong results, do nothing?

Is Value2 a valid property?

Could you provide your file? Follow instructions at bottom of my post.

 

[PNGBill]

File attached
View attachment Sales Statistics Copy (2).zip

 

[PNGBill]

Macro is IdentifyInstalls()

 

[June7]

I have the file. Not clear to me what you actually want to happen. The worksheet structure doesn't seem to agree with description in original post. Column D does not have "X", Column E does. And X is associated with multiple different Order Number in Column A. Which Order Number should be used? Sorry, this is not making sense.

 

[PNGBill]

Thanks June7,
Some records in the last column have an x.
Where a row has an x. All Rows with the same reference in column A also need to have an x.
This is Sheet1.
Trust this helps and many thanks for your assistance.
However we need all of the rows that have an x in one or more

 

[June7]

Okay, here's what I did. In VBA Editor, set Reference to Microsoft ActiveX Data Objects x.x Library because code is using early binding.

Sub setX()
Dim rst As New ADODB.Recordset
Dim cnx As New ADODB.Connection
Dim intRow As Integer
    'setup the connection
    '[HDR=Yes] means the Field names are in the first row
    cnx.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & ThisWorkbook.FullName & "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
    'open the recordset
    rst.Open "SELECT DISTINCT [Order Number] FROM [Sheet1$] WHERE [Line Note]='x'", cnx, adOpenDynamic, adLockOptimistic
    While Not rst.EOF
        intRow = 1
        With Worksheets("Sheet1")
            While Not IsEmpty(.Cells(intRow, 1))
                If .Cells(intRow, 1) = rst![Order Number] Then .Cells(intRow, 5) = "x"
                intRow = intRow + 1
            Wend
        End With
        rst.MoveNext
    Wend
    'disconnect the recordset
    rst.Close
    Set rst.ActiveConnection = Nothing
    'cleanup
    If CBool(cnx.State And adStateOpen) = True Then cnx.Close
    Set cnx = Nothing
End Sub

Or use this for the recordset loop:

With Worksheets("Sheet1")
    While Not rst.EOF
        .Range("A1").AutoFilter Field:=1, Criteria1:=rst![Order Number]
        .Columns(5).Cells.Replace What:="", Replacement:="x"
        rst.MoveNext
    Wend
    .ShowAllData
End With

 

[PNGBill]

Thanks again June7.

I don't have ActiveX Data Objects x.x Library
2.0, 2.1, 2.5, 2.6, 2.7, 2.8, 6.1 plus ..Objects Recordset 2.8 Library & 6.0 Library

 

[PNGBill]

Just realised my Software details were out of date.
Now using Win 10 with Office 2016.

 

[June7]

I use Win 10 and Office 2010. The code works. I even edited my post with another variation.


x.x represents the version number. You should choose 6.1


Otherwise, try late binding.

Dim rst As Object
Dim cnx As Object
Dim cmd As Object

 

[PNGBill]

Thanks again June7.
Just got up and will try again in a hour or so when at work.

I was thinking...
Should the Sheet be Sorted first ?
Sort by A Descending then E Descending.
Run through the records and where an x is found, check the next record and if value in A is the same, then x E, check next record for same value.
When the next record is not the same, loop back to the original check for x for that row.
This way the rows will be run through once only and in one direction.

Appreciate the amount of work you have done and it looks like I have a lot to learn

 

[June7]

Both versions of code open a recordset of Order Numbers where E is x. Then looping through the recordset first method looks for match on each row to the Order Number in the recordset and sets E to x only if match found. The second version filters rows to display only those that match Order Number then sets all E to x.

I would still use recordset even if sorting were applied. Code could find first occurrence of the Order Number, activate that cell, then compare value on each row from that point to the recordset field and if match set E to x, and if no match exit worksheet loop. Need more code rewrite for this. I expect this would perform faster than first option but not faster than the filter option.

I can see that the first option would be far too slow on large dataset but the filter (or rewrite for sorting) should be fast enough since both would only touch on records that actually meet the criteria.

 

[PNGBill]

Thanks June7,

Your help is much appreciated.
The Project was never going to be easy as the Raw Data was not good but at least we have a result better than guess work.

The Data is nearly 70,000 rows but we do not have to run every week. Maybe once or twice a year.

 

[Darrell]

In post #7 you have this:

SORef = ActiveCell.Offset(i, -4)

But you've not Activated the sheet anywhere so I don't know how you were getting a value for SORef

I would change this section to:

    'Go through Rows and check for "x"
    With Sheet1
        For i = StartRow To LastRow
            If LCase(.Cells(i, 5)) = "x" Then
                SORef = .Cells(i, 1).Value
                For i2 = StartRow To LastRow
                    If .Cells(i2, 1).Value = SORef Then
                        .Cells(i2, 5) = "x"
                    End If
                Next i2
            End If
        Next i
    End With

With 70k rows though this type of looping may take a long time so you may want to either look at marking rows that have had their value changed so that they don't have to have it changed every loop or doing it completely differently altogether