Compare 2 cols and replace lowest value in one of them? Help!
I have a spreadsheet with Prices in one column and Lowest Prices in another column.
At the moment I have 100 instructions like this:
If Range("d4") < Range("i4") Then Range("i4") = Range("d4")
through to
If Range("d100") < Range("i100") Then Range("i100") = Range("d100")
I know there is a better way using If statements and looping through the cells.
Can I find the syntax or an example of how to do this - NO!
Is there anyone out there with the solution? Any help would be really appreciated.
H
»
- Loweh000's blog
- Login or register to post comments
- 2792 reads
Though it would have been
Though it would have been straightforward with a simple formula but would have required an additional column.
However, here is the code that will do. Paste the following code in a general module and run.
Sub LowestPrice()
Dim x As Long
Dim wks As Worksheet
Set wks = ThisWorkbook.ActiveSheet
x = 2 'Starting Row - change this accordingly
Do While wks.Cells(x, "D") <> ""
If wks.Cells(x, "D") < wks.Cells(x, "I") Then
wks.Cells(x, "I") = wks.Cells(x, "D")
End If
x = x + 1
Loop
End Sub