Assign a specific value to a cell from a range depending on sheet name

I am trying to assign a different number from a range to the same cell in different sheets.

For example, I have range c4:c9. In Sheet A, A1=c4; Sheet B, A1=c5; Sheet C, A1=c6; etc.

I cannot make my macro move down the range.

Instead for each sheet it assigns C4 to A1.

I currently have the sheets named in a range so VBA will know to move sheets.

Here is my code:

Sub update()

   Dim sheet_name As Range
        For Each sheet_name In Sheets("Home").Range("A4:A9")
        If sheet_name.Value = "" Then
            Exit For
        ActiveCell.Formula = "=Home!r[3]c[2]"

    End If

    Next sheet_name
End Sub


sheet_name is not a range. While the strings inside the cells may be sheet names, they aren't sheets.


Sub test()
Dim sSheetName As String

For Each cell In [sheetnames]
    sSheetName = cell.Value
    Sheets(sSheetName).Range("a1") = "some formula"
Next cell

End Sub

Need Your Help

How to avoid processContents="lax" in WCF WSDL from classes implementing IXmlSerializable?

wcf wsdl datacontract

I have a wcf service written in a contract-first fashion. As some of the elements in the schema uses attributes, I had to create a custom serialization for this (using IXmlSerializable). Below is a