How to write a for loop to get the range between a ,b columns

T

tom

Dim x,y As Integer

NumRows = Range("B3", Range("B3").End(xlDown)).Rows.Count

Range("C2").Select


For x = 1 To NumRows

For y= cells(A).value to cells(B).value
'which code we have to write for comparing a1,b1 are equal or not
ActiveCell.FormulaR1C1 = "=Sum(RC[-1]+1)"

ActiveCell.Offset(x, 0).FillDown
Next
Next
 
J

JLGWhiz

This compares the cell in column A to the cell in column B on the same row.
If there is a match, then the message box will identify which row.


Sub rngCompare()
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = ActiveSheet.Range("A2:A" & lr)
For Each c In rng
If c.Value = c.Offset(0,1).Value Then
MsgBox "Bingo on row " & c.Row
End If
Next
End Sub
 
J

Jacob Skaria

The below code marks a "Mismatch" in ColC

Dim lngRow as Long, lngLastRow as Long
lngLastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
For lngRow = 1 To lngLastRow
If Range("A" & lngRow)<> Range("B" & lngRow) Then
Range("C" & lngRow) = "Mismatch"
End If
Next

If this post helps click Yes
 
P

Patrick Molloy

I'm not clear what you're trying to achieve here. Do you just want to see
where items in column A don't match the item in the same row in column B?
why were you using the SUM function in column C?

for checking mismatches try this:-

With Range(Range("B3"), Range("B3").End(xlDown)).Offset(, 1)
.FormulaR1C1 = "=IF(RC1<>RC2,""MISMATCH"","""")"
End With
 
P

Patrick Molloy

the question is very similar to Sam's "How to program this" on 25th June at
18:03.
please check the many replies already given.

Patrick Molloy said:
I'm not clear what you're trying to achieve here. Do you just want to see
where items in column A don't match the item in the same row in column B?
why were you using the SUM function in column C?

for checking mismatches try this:-

With Range(Range("B3"), Range("B3").End(xlDown)).Offset(, 1)
.FormulaR1C1 = "=IF(RC1<>RC2,""MISMATCH"","""")"
End With

tom said:
Dim x,y As Integer

NumRows = Range("B3", Range("B3").End(xlDown)).Rows.Count

Range("C2").Select


For x = 1 To NumRows

For y= cells(A).value to cells(B).value
'which code we have to write for comparing a1,b1 are equal or not
ActiveCell.FormulaR1C1 = "=Sum(RC[-1]+1)"

ActiveCell.Offset(x, 0).FillDown
Next
Next
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top