Getting error invalid or unqualified reference

Hi,
I am getting error invalid or unqualified reference. If I copy-paste special-value then the superscript macro in the sheet module didn’t work. So I want to change the formula into value but I am getting error.
The code is:

Sub Total()
Range("i8").FormulaR1C1 = _
"=IF(RC[-2]="""","""",IF(RC[-1]=0,RC[-2],CONCATENATE(RC[-2],""+"",RC[-1])))"
    Range("i8").Copy
                    Range("i9:i100").Select
    ActiveSheet.Paste
                'Range("i8:i100").Copy
        'Range("i8:i100").PasteSpecial Paste:=xlPasteValues
Formula = .Value
        MsgBox "Done", 64
End Sub

Any help will be appreciated.

Thanking you.

Sub Total()
With Range("I8:I100")
    .FormulaR1C1 = _
        "=IF(RC[-2]="""","""",IF(RC[-1]=0,RC[-2],CONCATENATE(RC[-2],""+"",RC[-1])))"
    .Value = .Value
End With
MsgBox "Done", 64
End Sub

Regards, TMS

Hi TMS,
Thank you. It's working fine but still I'm not getting correct output of superscript red code from sheet module.

Please see the file and solve the problem.

Thanking you.

Strictly speaking, the superscript issue is a different problem. However, the reason it is not working, I think, is because the formula is put into multiple cells. Hence, it seems to me that you can't use the Worksheet Change event handler to format the cells in this instance. The WSC event handler will work if you manually type, for example, 9+10, in a cell which will give you 9⁺¹⁰

Anyway, the following code seems to do the job:

Sub APlusB()

    Range("F8").FormulaR1C1 = _
        "=IF(COUNTIF(RC[-2]:RC[-1],""AB"")=2,""AB"",IF(SUM(RC[-2]:RC[-1])=0,"""",SUM(RC[-2]:RC[-1])))"
    Range("f8").Copy
                    Range("f9:f100").Select
    ActiveSheet.Paste
                Range("f8:f100").Copy
        Range("f8:f100").PasteSpecial Paste:=xlPasteValues
        MsgBox "Done", 64
End Sub

Sub Average()
Range("g8").FormulaR1C1 = _
                "=IF(RC[-1]=""AB"",""AB"",IF(RC[-1]="""","""",IF(RC[-1]/2=0,"""",ROUND(RC[-1]/2,0))))"
    Range("g8").Copy
                    Range("g9:g100").Select
    ActiveSheet.Paste
                Range("g8:g100").Copy
        Range("g8:g100").PasteSpecial Paste:=xlPasteValues
        MsgBox "Done", 64
End Sub

Sub Total()

Dim cell As Range
Dim iStart As Integer
Dim iLength As Integer
Dim lLR As Long
Dim rRangeToFormat As Range

lLR = Range("D" & Rows.Count).End(xlUp).Row
Set rRangeToFormat = Range("I8:I" & lLR)

Application.ScreenUpdating = False
Application.EnableEvents = False
With rRangeToFormat
    .FormulaR1C1 = _
        "=IF(RC[-2]="""","""",IF(RC[-1]=0,RC[-2],CONCATENATE(RC[-2],""+"",RC[-1])))"
    .Value = .Value
End With
Application.EnableEvents = True

For Each cell In rRangeToFormat
    If InStr(1, cell, "+") > 0 Then
        iStart = InStr(1, cell, "+")
        iLength = Len(cell) - iStart + 1
        With cell.Characters(Start:=iStart, Length:=iLength).Font
            .Superscript = True
            .ColorIndex = 3
        End With
    Else
        With cell.Font
            .FontStyle = "Regular"
            .Superscript = False
            .ColorIndex = xlAutomatic
        End With
    End If
Next 'cell
Application.ScreenUpdating = True

MsgBox "Done", 64

End Sub

See the updated example workbook.


Regards, TMS

Hi TMS,
Excellent! It's working fine. You have done a great job.

I appreciate you for your kind cooperation to complete my job.

Thank you.

You're welcome. Thanks for the rep.

Hi TMS,
There are headings of columns in row 7 which I don't want to superscript. So I want the code work from row 8. To achieve it I want to insert the code line in the code. I'm confused where to insert the code line to achieve the target. Please tell me where to insert the code line in the code.

The code line is:

                If .Row >= 8 Then

Thank you and sorry for trouble.

So I want the code work from row 8.

The code does work from row 8.

Neither the code that I provided, nor your WSC event handler affect row 7.

I'm guessing it's just the residual effect of earlier formatting. If you just re-type what you want in row 7 it shouldn't superscript it.

Regards, TMS

Hi TMS,
Sorry for asking the problem here in solved thread again. The problem is related to this thread so I am posting my problem here.

The superscript problem is solved but I am not getting the correct output in total columns. I am getting output of total column 'I' in other total columns. I am not understanding why it is happening.

Please see the attachment and give me your valuable guidance to solve this problem.

Thanking you in anticipation.

OK, try it this way:

Option Explicit

Sub TMS()

'Dim cell As Range
Dim iStart As Integer
Dim iLength As Integer
Dim lLR As Long
Dim rRangeToFormat As Range

lLR = Range("D" & Rows.Count).End(xlUp).Row
Set rRangeToFormat = ActiveSheet.Range("I8:I100,O8:O100,u8:u100,aa8:aa100,ag8:ag100,am8:am100,as8:as100,ay8:ay100,be8:be100,bh8:bh100")

Application.ScreenUpdating = False
Application.EnableEvents = False
With rRangeToFormat
    .FormulaR1C1 = _
        "=IF(RC[-2]="""","""",IF(RC[-1]=0,RC[-2],CONCATENATE(RC[-2],""+"",RC[-1])))"
    '.Value = .Value            ' <<< comment this out
End With
Application.EnableEvents = True

For Each cell In rRangeToFormat
    cell.Value = cell.Value     ' <<< and add this
    If InStr(1, cell, "+") > 0 Then
        iStart = InStr(1, cell, "+")
        iLength = Len(cell) - iStart + 1
        With cell.Characters(Start:=iStart, Length:=iLength).Font
            .Superscript = True
            .ColorIndex = 3
        End With
    Else
        With cell.Font
            .FontStyle = "Regular"
            .Superscript = False
            .ColorIndex = xlAutomatic
        End With
    End If
Next 'cell
Application.ScreenUpdating = True

End Sub

Note that you have reverted to fixed ranges going down to row 100. You are not using lLR so you might as well remove the lines:

Dim lLR As Long

and:

lLR = Range("D" & Rows.Count).End(xlUp).Row

Hi TMS,
Thank you. It's giving error 'variable not define'.

I didn't understand the suggestion in post 11. Please guide me.

Thanking you.

In the line of code that starts with

Set rRangeToFormat = ...

... you have "& lLR" at the end. Take it out (as I have done)


Don't worry about post #11. It won't hurt if you leave it as is; you're just not using it. No big deal.

Regards, TMS

Hi TMS,
Thank you.

Sorry for not understanding your suggestion due to my poor knowledge of vba.

I'm getting error 'variable not define' on the line

        For Each cell In Range("f8:f100, l8:l100, r8:r100, x8:x100, ad8:ad100, aj8:aj100, ap8:ap100, av8:av100, bb8:bb100")

on the word 'cell'.

Please see the macro 'multifunction' for your reference. I have added your new code in it.

Please do the necessary corrections in the macro 'multifunction' and if possible do send me the modified final copy of workbook.

Sorry for trouble.

Thanking you in anticipation.

Example updated ...

Hi TMS,
Thank you. The problem is solved. If we use 'option explicit' then the problem of 'variable not define' occures.

Just for knowledge I want to know the best approach to run the various macros one by one?

1. Integrate the macros in a single macro as we use in the example.
2. Use call event in this work module and call different macros one by one.

Thank you very much for your kind cooperation to solve my problems.

Looking to receive such a valuable guidance in future.

Thank you and have a nice day.