VBA - If statement - Range

[Shukis7]

Hi,

This is my previous thread which got resolved:

"I'm trying to use the Textjoin function in VBA.

I know how to do it for the first row, but I'm having issues when I want it to apply for the whole column.

For example, I want it to start at AH2 and the formula on the cell should be =TEXTJOIN("-",TRUE,I2,AE2,AF2,AG2).
For AH3 the formula should be =TEXTJOIN("-",TRUE,I3,AE3,AF3,AG3) and the same for AH4...
I know it should be something for i =1 in the range ... but I can't figure it out. (for each cell in range)

Any help would be really appreciated.

Thanks!"

The answer to this is the following:

"
Sub Shukis7()
Dim lr As Long
lr = Cells.Find("*", , xlFormulas, , 1, 2).Row
With Range("AH2:AH" & lr)
.FormulaR1C1 = "=TEXTJOIN("" - "",TRUE,RC9,RC31,RC32,RC33)"
.Value2 = .Value2
End With
End Sub
"

This worked perfectly, but now instead of textjoin I want to use the following formula: IF(COUNTIF($AH$2:AH2,AH2)>1,"Duplicate","Original")
The concept is the same, but now I am using only the column AH (RC34). I've tried modifying the previous formula, but it doesn't seem to work.

Any help would be really appreciated! Thank you!

 

[mohadin]

Try it like this

Sub Shukis7()
Dim lr As Long
lr = Cells.Find("*", , xlFormulas, , 1, 2).Row
With Range("Ag2:Ag" & lr)
.Formula = "= IF(COUNTIF($AH$2:AH2,AH2)>1,""Duplicate"",""Original"")"
.Value2 = .Value2
End With
End Sub

 

[Shukis7]

I've already tried that, it doesn't work. It's giving me Runtime Error 1004 "Application-defined or Object-defined error". Any other ideas? Thanks for your help!

 

[Shukis7]

Nevermind, it worked! Thanks for your help!