Create a comment from cell in another sheet

[gav_881]

I'm using a bunch of sheets like forms and pulling the values to the main sheet with
=INDIRECT(E1&"!b2") where E1 is the sheet name

I would like to add comments to the same cell from the corresponding C cell

Ie. cell content =INDIRECT(E1&"!b2") cell comment =INDIRECT(E1&"!C2")

Is there a way to do this?

the formula is repeated for the full column Ie.

cell 2 =INDIRECT(E1&"!b2")
cell 3 =INDIRECT(E1&"!b3")
cell 4 =INDIRECT(E1&"!b4")

 

[tyija1995]

Hey,

I have somewhat of a solution, try this:

Sub AddComments()
    Dim CommentText As String
    Dim nRows As Long
    Dim i As Long
    nRows = Sheets(Range("E1").Value).Cells(Rows.Count, 2).End(xlUp).Row
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    For i = 2 To nRows
        Cells(i, 1).Formula = "=INDIRECT($F$1 &""!"" &CELL(""address"", B" & i & "))"
        Cells(i, 4).Formula = "=INDIRECT($F$1 &""!"" &CELL(""address"", D" & i & "))"
        CommentText = Cells(i, 4).Value
            With Cells(i, 1)
                .AddComment
                .Comment.Visible = False
                .Comment.Text Text:="Title:" & Chr(10) & CommentText
            End With
    Next i
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
End Sub

 

[gav_881]

Hey,

I have somewhat of a solution, try this:

Sub AddComments()
    Dim CommentText As String
    Dim nRows As Long
    Dim i As Long
    nRows = Sheets(Range("E1").Value).Cells(Rows.Count, 2).End(xlUp).Row
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    For i = 2 To nRows
        Cells(i, 1).Formula = "=INDIRECT($F$1 &""!"" &CELL(""address"", B" & i & "))"
        Cells(i, 4).Formula = "=INDIRECT($F$1 &""!"" &CELL(""address"", D" & i & "))"
        CommentText = Cells(i, 4).Value
            With Cells(i, 1)
                .AddComment
                .Comment.Visible = False
                .Comment.Text Text:="Title:" & Chr(10) & CommentText
            End With
    Next i
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
End Sub

Thank you for your response, i tried running the code but it replaces the values in the A column with random values and comments of 0.

not sure if i was clear the A column is descriptions then B onward refers to a different sheet each time so the value in B1 references the sheet for all B column and C1 does the same for C column and so on.

very new to using VB, am i supposed to just paste the code using insert module?

 

[tyija1995]

Hey,

Thanks for the feedback on the procedure, you're right that you paste in to a new module - I will look into the error now, have you got a screenshot of how your data looks like? My assumption was to take the value in E1 of your active sheet (which I assume to be the name of another worksheet) and then put in the A column the values of the B column in the sheet corresponding to cell E1 & then comment it with the C column from the sheet linking to E1.