CODEWITHSUNDEEP
vbaexcel
Shikhar Parashar
Shikhar Parashar

Reputation: 226

Incorrect formula result via VBA

I have made a simple program to put a particular formula in Excel. The problem is the occurrence of single inverted quotes in the result.

Where the result should be ='[Fiber Loss Report - 7210 DCN.xlsx]1310'!G9, the result i get is ='[Fiber Loss Report - 7210 DCN.xlsx]1310'!'G9'

Sub CopyValues()
Dim n As Integer
Dim y As Integer
Dim rng As Range
y = 6
For n = 9 To 175
    rngText = "D" & y
    Range(rngText).Select
    'rng.Select
    'Range("D6").Select
    formulaText = "='[Fiber Loss Report - 7210 DCN.xlsx]1310'!G" & n
    ActiveCell.FormulaR1C1 = formulaText
    rngText = "E" & y
    Range(rngText).Select
    formulaText = "='[Fiber Loss Report - 7210 DCN.xlsx]1310'!G" & n + 1
    ActiveCell.FormulaR1C1 = formulaText
    n = n + 2
    y = y + 1
Next
End Sub

Please let me know on how can I avoid the occurrence of these single quotes.

Upvotes: 1

Views: 124

Answers (4)

Shikhar Parashar
Shikhar Parashar

Reputation: 226

Using .value instead of .FormulaR1C1 solves the problem. :)

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149335

Further to my comments below your question...

You need to find trends in your code. That ways you can avoid the loops.

For example, your formula from D6 to D61 increase from G9 to G12 to G15 and so on. Similarly for Col E.

So there is a trend. The next question you can ask yourself is "What formula can I write where I can get results like 9, 12, 15, 18 and so on?". The formula is

=(ROW()-3)*3

If you put that formula say in G6 and pull it down, you will get that sequence.

Now also instead of looping we can write formula to the entire range is one go. So we simply combine the above with Indirect. See this

Shortest possible code?

Sub CopyValues()
    Dim ws As Worksheet
    Dim fName As String

    fName = "Fiber Loss Report - 7210 DCN.xlsx"
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '"='[Fiber Loss Report - 7210 DCN.xlsx]1310'!G9"
        .Range("D6:D61").Formula = "=INDIRECT(" & Chr(34) & "'[" & _
                                   fName & "]1310'!G" & Chr(34) & _
                                   " & (ROW()-3)*3)"

        .Range("E6:E61").Formula = "=INDIRECT(" & Chr(34) & "'[" & _
                                   fName & "]1310'!G" & Chr(34) & _
                                   " & (ROW()-3)*3+1)"
    End With
End Sub

JUST FOR FUN

I can convert the above code in just two lines. Simply replace Sheet1 with the relevant sheet name.

Sub CopyValues()
    ThisWorkbook.Sheets("Sheet1").Range("D6:D61").Formula = "=INDIRECT(" & Chr(34) & "'[Fiber Loss Report - 7210 DCN.xlsx]1310'!G" & Chr(34) & " & (ROW()-3)*3)"
    ThisWorkbook.Sheets("Sheet1").Range("E6:E61").Formula = "=INDIRECT(" & Chr(34) & "'[Fiber Loss Report - 7210 DCN.xlsx]1310'!G" & Chr(34) & " & (ROW()-3)*3+1)"
End Sub

Upvotes: 3

kelvin 004
kelvin 004

Reputation: 423

You can just remove single inverted quotes and change to RC format:

Sub CopyValues()
 Dim n As Integer
 Dim y As Integer
 Dim rng As Range
 y = 6
 For n = 9 To 175
    rngText = "D" & y
    Range(rngText).Select
    'rng.Select
    'Range("D6").Select
    formulaText = "=[Fiber Loss Report - 7210 DCN.xlsx]1310!R" & n & "C7"        
    ActiveCell.FormulaR1C1 = formulaText
    rngText = "E" & y
    Range(rngText).Select
    formulaText = "=[Fiber Loss Report - 7210 DCN.xlsx]1310!R" & n + 1 & "C7"        
    ActiveCell.FormulaR1C1 = formulaText

    n = n + 2
    y = y + 1
Next
End Sub

Upvotes: 1

paul bica
paul bica

Reputation: 10705

Option Explicit

Sub CopyValues()

    Dim i As Long, j As Long

    j = 6
    For i = 9 To 175 Step 3
        Range("D" & j).Formula = "='[Fiber Loss Report - 7210 DCN.xlsx]1310'!G" & i
        Range("E" & j).Formula = "='[Fiber Loss Report - 7210 DCN.xlsx]1310'!G" & i + 1
        j = j + 1
    Next

End Sub

Result: ='[Fiber Loss Report - 7210 DCN.xlsx]1310'!G9

Upvotes: 2

Related Questions