Better Style for Code

[mp_robinson_uk]

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,



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

[mvidas]

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

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:


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

[mp_robinson_uk]

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

[Zack Barresse]

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

[mp_robinson_uk]

Hi Matt,

Just done it.

Thanks!

[mvidas]

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

[Zack Barresse]

(something I love to do, for some reason)

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

[mvidas]

I'm not denying it :)

[mp_robinson_uk]

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: