For loop help

[Herbalgiraffe]

Hey guys, so long story short, I am trying to automate the process of updating item prices for my company, but for now I am just trying to create some sort of a For loop to do so. There are two worksheets involved, new prices to be input (Ws1), and the much larger current list of all items a customer may purchase, which holds the old pricing currently (Ws2). for sake of example Ws1 has maybe 200 items to be updated, and Ws2 has 1000. I need my loop to cycle through each item in Ws1, searching for a match in Ws2 so that upon identification, the new data is inputted on Ws2, which after updating is complete, gets uploaded back into a database. There are more steps for each found item, but for now I am just hoping to highlight found items on Ws2, and I will work on the rest from there. the other thing is that the amount of items in Ws1 and Ws2 can change depending on the customer, as not all customers buy all of our products. Is what I currently have somewhere close?

    Dim Ws1 As Range
    Dim Ws2 As Range
    Dim finalrow As Integer
    Dim i As Integer
    
    finalrow = Cells(Rows.Count, 1).End(xlUp).Row
    Set Ws2 = Range(Worksheets(Sheet2).Range("A1"), finalrow)
    Set Ws1 = Range(Worksheets(Sheet1).Range("A1"), finalrow)
        
    With ActiveSheet
         For i = 2 To finalrow
              If .Cells(Ws1) Like .Cells(Ws2) Then
              cell.Interior.ColorIndex = 3

              End If
         Next
    End With

End Sub

 

[JLGWhiz]

Sub t()
Dim Ws1 As Range
Dim Ws2 As Range
Dim c As Range, fn As Range
Set Ws2 = Worksheets("Sheet2").Range("A1", Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp))
Set Ws1 = Worksheets("Sheet1").Range("A1", Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp))
    For Each c In Ws1
        Set fn = Ws2.Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                fn.Interior.Color = vbRed
            End If
    Next
End Sub

 

[Herbalgiraffe]

Sub t()
Dim Ws1 As Range
Dim Ws2 As Range
Dim c As Range, fn As Range
Set Ws2 = Worksheets("Sheet2").Range("A1", Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp))
Set Ws1 = Worksheets("Sheet1").Range("A1", Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp))
    For Each c In Ws1
        Set fn = Ws2.Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                fn.Interior.Color = vbRed
            End If
    Next
End Sub

Thank you for responding! I copied the code over and swapped in the real sheet names, and when I ran the code it gave a runtime error 9, object out of range message specifically on the "set Ws2" line, any ideas why? additionally, how does the code know what C is, you set fn but I dont see a declaration for c aside from the initial dim. Sorry for all the questions I am still trying to get the hang of all this.

 

[JLGWhiz]

The code runs without error in test set up where I used a workbook with sheets named "Sheet1" and "Sheet2". If you do not have those specific sheet names in your workbook it will produce that error 9. Also make sure your column A has data on both sheets if you are testing in a different workbook. Post the code as you now have it so I can see what is happening.

 

[Herbalgiraffe]

You are so totally right, my sincere apologies. I swapped the sheet names incorrectly and that is my fault, the code works wonderfully. I still am not sure how the code understands what "c" is, but at least it works, thank you!

 

[JLGWhiz]

You are so totally right, my sincere apologies. I swapped the sheet names incorrectly and that is my fault, the code works wonderfully. I still am not sure how the code understands what "c" is, but at least it works, thank you!

You're Welcome
Maybe this will help you to understand how the code knows what "c" is.
When you use the 'For Each' loop mehtod, the underlying vba application tells the compiler that the next text will be an object in the collection that follows the 'In' word in the statemen6t. So when you use

For Each c In Range("Whatever")

the compiler automatically sees c as the first cell in the range that 'Whatever" represents and wnen the code gets down to "Next", the compiler knows to change the c to the next cell in the Whatever range and will do so on each iteration until the end of Whatever.
It is all built into the vba application. And that is what the code that we write does, it calls up the functrions of the vba application that are in all of the .dll and other files you see if you look at Excel in the File Explorer.
Regards, JLG

 

[Herbalgiraffe]

That makes sense, thank you for explaining. is there a way for me to know aside from experimenting and reading which functions will have the ability for assumed values in them like a for each loop, or which ones support that practice?

 

[JLGWhiz]

That makes sense, thank you for explaining. is there a way for me to know aside from experimenting and reading which functions will have the ability for assumed values in them like a for each loop, or which ones support that practice?

Tricky question. I suppose the answer is to see what it does when you use the code then try to analyze the logic of it, keeping in mind that the vba code we are using is simply a means to trigger a predesigned function available in the underlying application. We do not have direct access to the underlying application code, but it consists of many algorithms and functions that respond to the vba code that we write when we execute the vba code. Logic is the key to the whole mess. There is no mystery to it, as long as the syntax of the code is correct, it will cause something to happen.. Making stuff happen in the order we want it to is where our problems lie.
Regards, JLG

 

[Herbalgiraffe]

oh man, I am close to having this completed, Hoping you can help me with the last puzzle piece. I inserted most of what I need the loop to execute and it works amazingly, but when I try to get fn.offset(1, 4) to do a VLOOKUP backwards to Ws1 to input the new pricing, I realized the formula will only look to one referenced cell, is there a way to make it relative so that it completes the formula relative to what is in fn.Offset(1)?

    Dim Ws1 As Range
    Dim Ws2 As Range
    Dim C As Range, fn As Range
        
    Set Ws2 = Worksheets("Sheet2").Range("A1", Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp))
    Set Ws1 = Worksheets("Sheet1").Range("A1", Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp))
        
    Worksheets("Sheet2").Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$Q$10151").AutoFilter Field:=7, Criteria1:="="
    
    For Each C In Ws1
        Set fn = Ws2.Find(C.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                fn.Interior.Color = vbYellow
                fn.Offset(1).EntireRow.Insert Shift:=xlDown
                fn.Offset(1).Value = fn
                fn.Offset(1, 1).Value = fn.Offset(0, 1)
                fn.Offset(1, 2).Value = fn.Offset(0, 2)
                fn.Offset(1, 3).Value = Date
                fn.Offset(0, 6).Value = Date
                fn.Offset(1, 4).Value = "=VLOOKUP(A2,Sheet1!A:C,3,FALSE)"
            End If

Next

 

[JLGWhiz]

is there a way to make it relative so that it completes the formula relative to what is in fn.Offset(1)?

I don't understand the question.

 fn.Offset(1, 4).Value = "=VLOOKUP(A2,Sheet1!A:C,3,FALSE)"

I am not sure what you are trying to do here but the syntax is not correct.
If you want to put a formula in fn.Offset(1, 4) then

 fn.Offset(1, 4).Formula = "=VLOOKUP(A2,Sheet1!A:C,3,FALSE)"