VBA replacement, loops and sheet links

[KateA]

Hi all,
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!$D$20:$D$40="Defeasance")*(SHEETX!$H$20:$H$40="EUR"),SHEETX!$L$20:$L$36,0))

.....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'm very very VERY new to VBA and I've written this macro.....

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

.....but while it isnt triggering error messages, it isn't doing anything else either. The cells are unchanged after it appears to run. I'd love and cherish any help anyone is able to give!


Thanks a million

 

[Luke M]

So, it looks like the sheet names are in col A, and the formulas you are writing are in col C.
Put this formula (not an array) in C3, copy down.
=SUMIFS(INDIRECT("'"&A3&"'!L20:L40"),INDIRECT("'"&A3&"'!D20:D40"),"Defeasance",INDIRECT("'"&A3&"'!H20:H40"),"EUR")

Or, if you want to stick with your VB approach:

Sub GodAnswers()
Dim OriginalText As String, CellINeed As String, NewText As String
Dim MyCell As Range

Application.ScreenUpdating = False
For Each MyCell In Range("C3:C80")
    OriginalText = MyCell.FormulaArray
    CellINeed = MyCell.Offset(0, -2).Value
    'VB variables don't get quotation marks. That is what was wrong before
    NewText = Replace(OriginalText, "Sheet1", CellINeed)
    MyCell.FormulaArray = NewText
Next MyCell
Application.ScreenUpdating = True
End Sub

 

[KateA]

Never before have I felt this level of gratitude towards someone who hasn't just bought me a drink.

Thank you so much! Giving up your time has saved me hours of headache. The formula worked like magic.

 

[Luke M]

Aw, shucks, glad I could help Kate.