Select Case is not running

[Samuels90]

Hey everyone,

My program is supposed to go to Column R Row 2. If Column M contain "01" it should run the "sum(if array" for that Case,(no pun intended), in Column R.

Can someone tell me what is wrong with my code?

Cheers!

[CODE=vb]Sub GLAmount()

With Range("R:R").Va lue
Select Case Range("M:M").Te xt
Case "01"
.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34 C1=RC[-4],IF([Book5]Sheet1!R6C2:R34 C2=RC[-3],[Book5]Sheet1!R6C7:R34 C7,0),0))"
Case "02"
.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34 C1=RC[-4],IF([Book5]Sheet1!R6C2:R34 C2=RC[-3],[Book5]Sheet1!R6C17:R3 4C17,0),0))"
Case "03"
.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34 C1=RC[-4],IF([Book5]Sheet1!R6C2:R34 C2=RC[-3],[Book5]Sheet1!R6C27:R3 4C27,0),0))"
Case "04"
.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34 C1=RC[-4],IF([Book5]Sheet1!R6C2:R34 C2=RC[-3],[Book5]Sheet1!R6C37:R3 4C37,0),0))"
Case "05"
.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34 C1=RC[-4],IF([Book5]Sheet1!R6C2:R34 C2=RC[-3],[Book5]Sheet1!R6C47:R3 4C47,0),0))"
Case "06"
.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34 C1=RC[-4],IF([Book5]Sheet1!R6C2:R34 C2=RC[-3],[Book5]Sheet1!R6C57:R3 4C57,0),0))"
Case "07"
.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34 C1=RC[-4],IF([Book5]Sheet1!R6C2:R34 C2=RC[-3],[Book5]Sheet1!R6C67:R3 4C67,0),0))"
Case "08"
.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34 C1=RC[-4],IF([Book5]Sheet1!R6C2:R34 C2=RC[-3],[Book5]Sheet1!R6C77:R3 4C77,0),0))"
Case "09"
.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34 C1=RC[-4],IF([Book5]Sheet1!R6C2:R34 C2=RC[-3],[Book5]Sheet1!R6C87:R3 4C87,0),0))"
Case "10"
.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34 C1=RC[-4],IF([Book5]Sheet1!R6C2:R34 C2=RC[-3],[Book5]Sheet1!R6C97:R3 4C97,0),0))"
Case "11"
.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34 C1=RC[-4],IF([Book5]Sheet1!R6C2:R34 C2=RC[-3],[Book5]Sheet1!R6C107:R 34C107,0),0))"
Case "12"
.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34 C1=RC[-4],IF([Book5]Sheet1!R6C2:R34 C2=RC[-3],[Book5]Sheet1!R6C117:R 34C117,0),0))"
End Select
End With
End Sub[/CODE]

[Killer42]

I think you'll probably find that Range("M:M").Te xt is returning Null.

At least that's what a little experimentation on my system seems to imply. I just threw some text all over the first few columns, then went to the VB editor Immediate window and entered "Print Range("B:B").Te xt", and got "Null".

[QVeen72]

Hi,

you have to give Select case for :
Range("R:R").Va lue

(not text)


Regards
Veena

[Killer42]

Range("R:R").Va lue
(not text)

Are you sure about that? When I try to access Range("B:B").Va lue I get runtime error 13, type mismatch.

[QVeen72]

Hi,

Yes Killer, I just got mixed up with "Cells"
ExelWS.Cells(1, 6).Value = "TempText"

Regards
Veena