1

I have a function which gives a value depending on the colour of a specified cell.

Function CheckColor1(range)
If range.Interior.Color = RGB(189, 215, 238) Then
    CheckColor1 = "R"
ElseIf range.Interior.Color = RGB(255, 230, 153) Then
    CheckColor1 = "C"
End If
End Function

In L1 I have =CheckColor1(a1) and I fill down for all rows. It gives me the corresponding "R" and "C" for each row.

I then, in M1, use an =L1 next to it, fill down again, copy > paste values. I tried copy > paste values directly on the L Column, but it does not work.

I recorded this. Which gave me:

range("L1").Select
ActiveCell.FormulaR1C1 = "=CheckColor1(RC[-11])"
range("L1").Select
Selection.AutoFill Destination:=range("L1:L102")
range("L1:L102").Select
range("M1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
range("M1").Select
Selection.AutoFill Destination:=range("M1:M102")
range("M1:M102").Select

However, when I run the Macro, I get the first value correctly in L1, but then all values underneath that I get a #VALUE error. Which says A Value used in the formula is of the wrong data type.

The Columns are "General" format.

Community
  • 1
  • 1
Jade Rotheram-Loddo
  • 25
  • 7

1 Answers1

2

Avoid the use of .Select You may want to see How to avoid using Select in Excel VBA macros

All that code can be written in just two lines

range("L1:L102").Formula = "=CheckColor1(A1)"
range("M1:M102").Formula = "=L1"

Replace your code with these two lines and try again. It will work this time.

Edit

The idea is not to use .Autofill but to enter the formula in the complete range in ONE GO.

If the columns "L" and "M" are fixed and the rows may change everytime then find the last row of column "L" and incorporate that into your code. See this

Dim lRow As Long

With Worksheet("Orders")
    lRow = .Range("L" & .Rows.Count).End(xlUp).Row

    .Range("L1:L" & lRow).Formula = "=CheckColor1(A1)"
    .Range("M1:M" & lRow).Formula = "=L1"
End With
Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Hi, Thank you for you quick reply. Sorry I probably should have put this in the original question. The range I use for L and M will vary every time, either smaller or bigger. I have been replacing the Autofill code with this... `ActiveCell.Offset(0, -1).Select Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Select range(Selection, Selection.End(xlUp)).Select Selection.FillDown` But the error was occuring before I even changed it to that. And still occured after I had changed it. – Jade Rotheram-Loddo Aug 17 '16 at 10:42
  • I have updated the above post. You may have to refresh it – Siddharth Rout Aug 17 '16 at 10:47
  • Hi, Thanks again. Sorry, I'm not all that experienced in this! Store in a variable has gone right over my head. The sheet, is a sheet of orders, so the range can vary day to day, so the range I need the formula in could be L1:L102, or L1:L80, Or L1:L600, same with the M column. There will always be the same amount of columns. So to dumb it down for me, what do I need to put in and where? Thank you again! – Jade Rotheram-Loddo Aug 17 '16 at 10:54
  • If the rows change from 1:102 to 1:80 then simply change `Rw1 = 1: Rw2 = 102` to `Rw1 = 1: Rw2 = 80` And if the columns change then amend `Col1 = "L": Col2 = "M"` – Siddharth Rout Aug 17 '16 at 10:56
  • If you are not sure what the last row will be then see [THIS](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) link on how to find that via code – Siddharth Rout Aug 17 '16 at 10:58
  • Will it always be Col L and Col M? – Siddharth Rout Aug 17 '16 at 10:59
  • Thank you I will look at that link. And yes, always L and M. – Jade Rotheram-Loddo Aug 17 '16 at 11:02
  • I already anticipated your comment. Refresh the page and see the latest edit in the above post ;) – Siddharth Rout Aug 17 '16 at 11:03
  • Hi, Thank you very much for the simplification. It has correctly filled in L1 & M1, but not filled it down at all? – Jade Rotheram-Loddo Aug 17 '16 at 11:32
  • Yes because `lRow = .Range("L" & .Rows.Count).End(xlUp).Row` was an example ;) I wanted you to figure it out yourself... You need to change it to `lRow = .Range("A" & .Rows.Count).End(xlUp).Row` – Siddharth Rout Aug 17 '16 at 11:35