PDA

View Full Version : [SOLVED] Help on "Grouping" the remark in formula VBA



alexnkc
11-28-2013, 10:53 PM
Hi,

I have a code which it will apply the Remark at Cell (I11) and it is depend on cell E11 (RC[-4])

When E11 was less then 79,9%, instate of Cell (I11) show N/A, it show & strremakr1 &.

Please help. Thanks


Sub Macro6()

Dim strRemark1 As String
Dim strRemark2 As String
Dim strRemark3 As String
Dim strRemark4 As String
strRemark1 = "N/A"
strRemark2 = "Ok"
strRemark3 = "Good"
strRemark4 = "Great"




With Range("I11")
.FormulaR1C1 = _
"=IF(RC[-4]=""-"",""-"",IF(RC[-4]<=79.9%,"" & strremark1 & ""," & _
"IF(AND(RC[-4]>=80%,RC[-4]<=89.9%),""& strremark2 &""," & _
"IF(AND(RC[-4]>=90%,RC[-4]<=94.9%),"" & strremark3 &""," & _
"IF(RC[-4]>=95%,""& strremark 4 &"","""")))))"
End With




End Sub

SamT
11-30-2013, 11:06 AM
Quotes are inherent in String variables. You don't need to add them when building strings


Sub Macro6()

Const DQ As String = """ 'Used when Quotes are needed in Formula
Const Dashes As String = DQ & "-" & DQ & "," & DQ & "-" & DQ
Dim strRemark1 As String
Dim strRemark2 As String
Dim strRemark3 As String
Dim strRemark4 As String
strRemark1 = "N/A"
strRemark2 = "Ok"
strRemark3 = "Good"
strRemark4 = "Great"

Range("I11") .FormulaR1C1 = _
"=IF(RC[-4]=" & Dashes & ",IF(RC[-4]<=79.9%," & strremark1 _
& ",IF(AND(RC[-4]>=80%,RC[-4]<=89.9%)," & strremark2 _
& ",IF(AND(RC[-4]>=90%,RC[-4]<=94.9%),"" & strremark3 _
&",IF(RC[-4]>=95%,"& strremark4 & ",)))))"
End Sub

I wonder about the last comma before all the closing parentheses.

alexnkc
12-01-2013, 08:31 AM
Hi SamT,

The result still the same,
When i remove the double quote ("& strRemark1 &"), result show that ---> #Name? (When i refer to excel formula area, it show ......IF(I11<=79.9%,N/A,IF.........
When i return the double quote back (""& strRemark1 &""), result show ---> strRemark1 (When i refer to excel formula area, it show ......IF(I11<=79.9%," & strRemark1 & ",IF...

What is going on?

SamT
12-01-2013, 03:43 PM
When i remove the double quote ("& strRemark1 &"), result show that ---> #Name? (When i refer to excel formula area, it show ......IF(I11<=79.9%,N/A,IF.........Shows that the VBA Code is correct.

However "N/A" is an Error Code, so the Cell result is an error.


Two things: First put
Option Explicit at the top of the code page.

Second change "strRemark1 = "N/A"" to "strRemark1 = "NA"," or "N-A" or "N\A."

alexnkc
12-01-2013, 04:36 PM
Hi SamT,

I have change the code base on your input but result still same.


Code below show Application-defined or object-defined error.



Sub Macro6()
Dim strRemark1 As String
Dim strRemark2 As String
Dim strRemark3 As String
Dim strRemark4 As String
strRemark1 = "N/A"
strRemark2 = "Ok"
strRemark3 = "Good"
strRemark4 = "Great"

With Range("I11")
.FormulaR1C1 = _
"=IF(RC[-4]=""-"",""-""," & _
"IF(RC[-4]<=79.9%," & strRemark1 & "," & _
"IF(AND(RC[-4]>=80%,RC[-4]<=89.9%)," & strRemark2 & "," & _
"IF(AND(RC[-4]>=90%,RC[-4]<=94.9%)," & strRemark3 & "," & _
"IF(RC[-4]>=95%," & strRemark4 & ","""")))))"
End With

End Sub



But when I remove the strRemark and straight away put the comment into it. Is show ok and run normal. So i guess problem occur at Dim there.



Sub Macro6()
Dim strRemark1 As String
Dim strRemark2 As String
Dim strRemark3 As String
Dim strRemark4 As String
strRemark1 = "N/A"
strRemark2 = "Ok"
strRemark3 = "Good"
strRemark4 = "Great"

With Range("I11")
.FormulaR1C1 = _
"=IF(RC[-4]=""-"",""-""," & _
"IF(RC[-4]<=79.9%,""N/A""," & _
"IF(AND(RC[-4]>=80%,RC[-4]<=89.9%),""Ok""," & _
"IF(AND(RC[-4]>=90%,RC[-4]<=94.9%),""Good""," & _
"IF(RC[-4]>=95%,""Great" ","""")))))"
End With

End Sub

SamT
12-01-2013, 05:01 PM
So i guess problem occur at Dim there.
I think problem is when Excel interprets "N/A" in the formula.

Try this code and see what happens

Sub Macro6()
Dim strRemark1 As String
Dim strRemark2 As String
Dim strRemark3 As String
Dim strRemark4 As String
strRemark1 = "NA" '<---
strRemark2 = "Ok"
strRemark3 = "Good"
strRemark4 = "Great"

With Range("I11")
.FormulaR1C1 = _
"=IF(RC[-4]=""-"",""-""," & _
"IF(RC[-4]<=79.9%," & strRemark1 & "," & _
"IF(AND(RC[-4]>=80%,RC[-4]<=89.9%)," & strRemark2 & "," & _
"IF(AND(RC[-4]>=90%,RC[-4]<=94.9%)," & strRemark3 & "," & _
"IF(RC[-4]>=95%," & strRemark4 & ","""")))))"
End With

End Sub

Whatever happens, you have some code that works :)

alexnkc
12-01-2013, 09:04 PM
Hi SamT,

I have modified the VBA according to your input. Result was negative.

Anyway, i just insert the commend directly to the VBA and let it work for the time been.
Thanks alot.