Help with VBA Replace function in array formulas

[KateA]

Hi,
I'm making a master spreadsheet that needs to pull data from about 80 source sheets.

I want to put a forumula like this....
=SUM(IF((SHEETX!D20:D40="Defeasance")*(SHEETX!H20:H40="EUR"),SHEETX!L20:L36,0))<o></o>

.....in every cell in a column, but in each cell I want to replace SHEETX with a different sheet name. A big problem is that when the source sheets were being made, they were made in the wrong order, so they cant be reffered to as Sheet1, Sheet 2 etc. I'd like C3 to be linked to AdaptPharma, C4 to AditiTechnologies and so on. the only order the source sheets are in is alphabetical order. If it would help, the name of the sheet I want to link to each cell in the column (Csomething) is the value currently in Asomething. I was wondering if anyone could advise on writing a macro to solve my problem?

Thanks a mill

 

[KateA]

Sub PleaseGod()
For Each MyCell In Range("C3:C80")
Dim CellINeed As String
Dim Newtext As String
OriginalText = MyCell.FormulaArray
CellINeed = MyCell.Offset(0, -2).Value
Newtext = Replace(OriginalText, "Sheet1", "CellINeed")

MyCell = Newtext

Next MyCell
End Sub



Wrote this but it doesnt work... Can anyone see the problem (bearing in mind there may be more than one as I am VBA illiterate)

 

[Domenic]

Try...

Sub PleaseGod()

    Dim CellINeed As String
    Dim NewText As String
    
    For Each MyCell In Range("C3:C80")
        OriginalText = MyCell.FormulaArray
        CellINeed = MyCell.Offset(0, -2).Value
        NewText = Replace(OriginalText, "Sheet1", CellINeed)
        MyCell.FormulaArray = NewText
    Next MyCell
    
End Sub

Hope this helps!

 

[Domenic]

You may want to adopt the following instead...

Option Explicit

Sub PleaseGod()

    Dim MyCell          As Range
    Dim OriginalText    As String
    Dim CellINeed       As String
    Dim NewText         As String
    Dim LastRow         As Long
    
    LastRow = Cells(Rows.Count, "C").End(xlUp).Row
    
    If LastRow < 3 Then
        MsgBox "No data exists.", vbExclamation
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
    For Each MyCell In Range("C3:C" & LastRow)
        If Len(MyCell) > 0 Then
            OriginalText = MyCell.FormulaArray
            CellINeed = MyCell.Offset(0, -2).Value
            NewText = Replace(OriginalText, "Sheet1", CellINeed)
            MyCell.FormulaArray = NewText
        End If
    Next MyCell
    
    Application.ScreenUpdating = True
    
    Set MyCell = Nothing
    
End Sub

Hope this helps!

 

[KateA]

Thank you!

 

[Domenic]

You're very welcome!