Loop ?

[Guest]

Can someone tell how or what i need to do with this code so that it will
start in Column B and compare with Column C and do this until the last row in
column B
Right now the code only compare's B5 with C5 and i need it to compare
B6 with C6, B7 with C7 and so on till the last row in column B

Thanks for any help!

Option Explicit

Sub priceComparison()
Const priceCompSheetName = "Price Comparison"
Const ourPriceCol = "B"
Const alltelPriceCol = "C"
Dim pcWS As Worksheet

Set pcWS = Worksheets(priceCompSheetName)

If pcWS.Range(ourPriceCol & "5").Value _
<> pcWS.Range(alltelPriceCol & "5").Value Then
pcWS.Range(ourPriceCol & "5").Value _
= pcWS.Range(alltelPriceCol & "5").Value
End If
End Sub

 

[John]

Can someone tell how or what i need to do with this code so that it will
start in Column B and compare with Column C and do this until the last row in
column B
Right now the code only compare's B5 with C5 and i need it to compare
B6 with C6, B7 with C7 and so on till the last row in column B

Thanks for any help!

Option Explicit

Sub priceComparison()
Const priceCompSheetName = "Price Comparison"
Const ourPriceCol = "B"
Const alltelPriceCol = "C"
Dim pcWS As Worksheet

Set pcWS = Worksheets(priceCompSheetName)

If pcWS.Range(ourPriceCol & "5").Value _
<> pcWS.Range(alltelPriceCol & "5").Value Then
pcWS.Range(ourPriceCol & "5").Value _
= pcWS.Range(alltelPriceCol & "5").Value
End If
End Sub

this shoudl do it

for i = 5 to lastrow
if not pcws.cells(i,2).value = pcws.cells(i,3).value then
pcws.cells(i,2).value = pcws.cells(i,3).value
end if
next i

 

[Guest]

Option Explicit

Sub priceComparison()
Const priceCompSheetName = "Price Comparison"
Const ourPriceCol = "B"
Const alltelPriceCol = "C"
Dim pcWS As Worksheet, i as Long
Dim lastrow as Long

Set pcWS = Worksheets(priceCompSheetName)
lastrow = pcWs.Cells(rows.count,ourpriceCol).End(xlup).row
for i = 5 to lastrow
If pcWS.Range(ourPriceCol & i).Value _
<> pcWS.Range(alltelPriceCol & i).Value Then
pcWS.Range(ourPriceCol & i).Value _
= pcWS.Range(alltelPriceCol & i).Value
End If
Next i
End Sub

 

[Guest]

Thanks Tom

Option Explicit

Sub priceComparison()
Const priceCompSheetName = "Price Comparison"
Const ourPriceCol = "B"
Const alltelPriceCol = "C"
Dim pcWS As Worksheet, i as Long
Dim lastrow as Long

Set pcWS = Worksheets(priceCompSheetName)
lastrow = pcWs.Cells(rows.count,ourpriceCol).End(xlup).row
for i = 5 to lastrow
If pcWS.Range(ourPriceCol & i).Value _
<> pcWS.Range(alltelPriceCol & i).Value Then
pcWS.Range(ourPriceCol & i).Value _
= pcWS.Range(alltelPriceCol & i).Value
End If
Next i
End Sub

 

[Guest]

Also consider this, as long as it's OK to change the activecell (note that,
in this example and the others, the if is not necessary and just adds
processing, since, in the end, you're just making the column B value equal to
the column C value regarless.):

Range("B5").Select
Do Until ActiveCell.Value = ""
ActiveCell.Value = ActiveCell.Offset(0,1).Value
ActiveCell.Offset(1, 0).Select
Loop