Forum Discussion
In a column of Data, Find 2nd, 3rd, nth occurrences and apply a value in the corresponding column
Hi all, Attached is a spreadsheet that gives an example of what I'm working with. In it, I'm trying to create a formula that searches all of column F for the exact same tag numbers. And if it...
Best if you explain what it is needs doing, what my code does which is undesirable and also show what code you already have and where it needs adjusting.
So in the original Experiment attached to the original post.
We have this formula:
Application.CutCopyMode = False
Range("K1").Value = "Note"
Range("K2").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(C[-2],RC[-2])>1,""Multiple times"","""")"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K2000")
The person who originally made it up, I guess, decided to use R1C1 reference style.
So I guess what I'm asking is how do I apply your formula to regular reference style or convert your formula to R1C1.
Also whats happening when I apply your formula is that its giving me an error when running the Macro. application defined or object defined error.
The idea behind the formula is that it should be able to apply to an entire column of active data (it will stretch between hundreds of rows) find the duplicates, apply the "Again" or "Multiple Times" to that specific item in the row under the Notes Column, column I.
The R1C1 puts itself in column K and applies a formula that reaches over to apply the formulas. in another column.
But We are looking to enhance the formula, essentially, and instead of marking them ALL multiple times...we want to mark singular ones, as the second or third occurrence etc...
If that makes sense.
We have this formula:
Application.CutCopyMode = False
Range("K1").Value = "Note"
Range("K2").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(C[-2],RC[-2])>1,""Multiple times"","""")"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K2000")
The person who originally made it up, I guess, decided to use R1C1 reference style.
So I guess what I'm asking is how do I apply your formula to regular reference style or convert your formula to R1C1.
Also whats happening when I apply your formula is that its giving me an error when running the Macro. application defined or object defined error.
The idea behind the formula is that it should be able to apply to an entire column of active data (it will stretch between hundreds of rows) find the duplicates, apply the "Again" or "Multiple Times" to that specific item in the row under the Notes Column, column I.
The R1C1 puts itself in column K and applies a formula that reaches over to apply the formulas. in another column.
But We are looking to enhance the formula, essentially, and instead of marking them ALL multiple times...we want to mark singular ones, as the second or third occurrence etc...
If that makes sense.
- If you have devised a formula that works (manually, in Excel) the simple way to get the syntax of that formula for VBA is:
- Select cell with proper formula
- Hit Alt+F11 (Opens the VBA editor)
- Hit control+g (opens the immediate pane)
- Type:
?ActiveCell.FormulaR1C1
- place cursor on that line and hit Enter.
- Any quotes which are within the formula must be doubled-up when writing in VBA.
You can slo use ActiveCell.Formula (so without the R1C1), but the advantage of the R1C1 is that the VBA placing formula will become independent of the location of the formula. The cell addresses with numbers between square brackets in the formula become relative to where you place the formula. Clear as mud?