For loop help

Herbalgiraffe

New Member
Joined
Feb 22, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
    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
 

Herbalgiraffe

New Member
Joined
Feb 22, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
My apologies for not being more clear. The end goal would be to have the value of fn.Offset(1, 4) to equal the result of that formula, but since it is within the loop, I would need A2 to change to whatever row the loop is executing on. Maybe there is a way to accomplish it without the formula at all, im not entirely sure, but however I could possibly accomplish that function so that I can pull that item's price from Ws1 into Ws2, thats the goal regardless of how I get there.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,514
Office Version
  1. 2013
Platform
  1. Windows
If I read it right then this should put the price where you want it.

VBA Code:
fn.Offset(1, 4).Value = c.Offset(, 2),Value
 

Watch MrExcel Video

Forum statistics

Threads
1,112,860
Messages
5,542,929
Members
410,577
Latest member
ZvK
Top