Sambora
02-10-2013, 03:25 PM
I've got a macro that checks whether a value in one column matches the value in another column (same row) and enters the value in another column that either enters the same value as the first column or says that a check is required.
A couple of things that I'd like to change though. I'd like a user to be able to select which columns they want to compare without having to change the macro itself. I suppose I'd need some input boxes but I don't know to set this up. Ideally there'd a user would be asked to select the columns they want to compare (there'd only ever be 2) and the column that will indicate whether a check is required.
I'd also like to be able to select the number of rows at this stage or alternatively automate it somehow, so that it selects the first and last rows in the columns in the macro rather than require a user to do this. Currently the macro is set to check the first 10 rows but the number of rows will vary per report and I'd rather make this as easy as possible to set.
Can anyone offer any suggestions to re-write the below macro?
Sub Macro1()
Dim i As Long
For i = 1 To 10
If Range("A" & i).Value = Range("B" & i).Value Then
Range("F" & i).Value = Range("A" & i).Value
ElseIf Range("A" & i).Value <> Range("B" & i).Value Then
Range("F" & i).Value = "Check Req"
End If
Next
Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("F:F").Select
Selection.NumberFormat = "00000000"
End Sub
A couple of things that I'd like to change though. I'd like a user to be able to select which columns they want to compare without having to change the macro itself. I suppose I'd need some input boxes but I don't know to set this up. Ideally there'd a user would be asked to select the columns they want to compare (there'd only ever be 2) and the column that will indicate whether a check is required.
I'd also like to be able to select the number of rows at this stage or alternatively automate it somehow, so that it selects the first and last rows in the columns in the macro rather than require a user to do this. Currently the macro is set to check the first 10 rows but the number of rows will vary per report and I'd rather make this as easy as possible to set.
Can anyone offer any suggestions to re-write the below macro?
Sub Macro1()
Dim i As Long
For i = 1 To 10
If Range("A" & i).Value = Range("B" & i).Value Then
Range("F" & i).Value = Range("A" & i).Value
ElseIf Range("A" & i).Value <> Range("B" & i).Value Then
Range("F" & i).Value = "Check Req"
End If
Next
Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("F:F").Select
Selection.NumberFormat = "00000000"
End Sub