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
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?
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
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.