Better Style for Code

Hi All,

As you can tell I am pretty new to this VBA stuff but am trying to learn.

I wrote a bit of code, which I think could be vastly improved on,

Code:

---

For j = 5 To LastCol - 1
For i = 2 To LastRow - 1
Cells(i, LastCol + j - 1).Value = _
"=IF(IF(ISERROR(MATCH(" & Cells(i, LastCol + 1).Address & _
"," & Cells(i, j).Address & ":" & Cells(i, j).Address & ",0)),0,1),1, )"
If Cells(i, LastCol + j - 1).Value = 0 And _
IsNumeric(Cells(i, LastCol + j - 1).Value) Then
Cells(i, LastCol + j - 1).ClearContents
End If
Next i
Next j

---

I could not figure out how to do this will a formulaR1C1 style. Could you please give me the correct syntax and a bit of an explaination so I can
understand it.

Thank you very much,

Mick

Hi Mick, here is an explanation of R1C1 notation, as well as what you could change your formula to:

'In the following explanation, x would be a whole number
'
'R by itself means "same row"
'Rx means Row x
'R[x] means x offset from current row, R[1] means "one row down", R[-1] means "one row up"
'The same applies for C, Cx, and C[x]

'Your Code

Code:

---

Cells(i, LastCol + j - 1)..FormulaR1C1 = _
"=IF(IF(ISERROR(MATCH(RC" & LastCol + 1 & ",RC[-" & LastCol - 1 & "],0)),0,1),1, )"
'"RC" & LastCol + 1 <-- Same row, Last Column + 1
' This is the same as you saying "Cells(i, Last Col + 1).Address"
'
'"RC[-" & Last Col - 1 & "]" <-- Same row, (LastCol-1) columns to the left
' This is the same as you saying "Cells(i, j).Address & ":" & Cells(i, j).Address

---

But overall, I would probably change your code to:

Code:

---

Dim CLL As Range, RG As Range
Set RG = Range(Cells(2, 4 + LastCol), Cells(LastRow - 1, 2 * (LastCol - 1)))
RG.FormulaR1C1 = "=IF(IF(ISERROR(MATCH(RC" & LastCol + 1 & ",RC[-" & LastCol - 1 & "],0)),0,1),1, )"
'Loops through each cell in that region with a formula that returns a number
For Each CLL In RG.SpecialCells(xlFormulas, 1).Cells
If CLL = 0 Then CLL.ClearContents 'If the cell=0, clearcontents
Next CLL

---

Matt

Hi Matt,

Thanks very much - it works a treat and is much faster and cleaner. More importantly I can understand it!

Thank you very much for your help!

Mick

Hi Mick,

If this does it for you on this thread, did you know you can mark your own threads solved? By clicking Thread Tools (top of the thread) --> Mark Solved --> Perform Action. Glad you got it working. :yes

Hi Matt,

Just done it.

Thanks!

Glad I could help, Mick, let me know if you need any more code cleanup like this (something I love to do, for some reason)

Matt

Quote:

---

Originally Posted by mvidas

(something I love to do, for some reason)

---

Ummm ... ... you're psychotic? :dunno ROFLMAO!!!

I'm not denying it :)

Hi All,

Maybe psychotic - but also a very nice helpful person!
And yes I am sure I will take you up on your kind offer.

Thanks!

Mick

:hi: