[VBA] Enter a value in a neighboring cell based on cell occupancy

[Martin_H]

Hi Team,

let there be a specific range:

Union(Range("E14:E19,E21:E26,E28:E33,E35:E40,E42:E47,E49:E54,E56:E61,E63:E68,E70:E75,E77:E82,E84:E89,E91:E96,E98:E103,E105:E110,E112:E117,E119:E124,E126:E131,E133:E138,E140:E145,E147:E152"), Range("E154:E159,E161:E166,E168:E173,E175:E180,E182:E187,E189:E194,E196:E201,E203:E208,E210:E215,E217:E222,E224:E229,E231:E236,E238:E243,E245:E250,E252:E257"))

For each occupied E in my range (↑), I want to enter the value 10 in the same row as E, but in the AC column.

For example:
Cell E14 is occupied with text that is formatted as General, macro should insert value 10 in the cell AC14.
The macro should check the entire range and enter a value 10 in the neighboring AC cell for each occupied E.

Thank you for help.

 

[Alex Blakenburg]

Did you just mean something like this ?

Sub PopulateAC()
    Dim rng As Range, rCell As Range

    Set rng = Union(Range("E14:E19,E21:E26,E28:E33,E35:E40,E42:E47,E49:E54,E56:E61,E63:E68,E70:E75,E77:E82,E84:E89,E91:E96,E98:E103,E105:E110,E112:E117,E119:E124,E126:E131,E133:E138,E140:E145,E147:E152"), Range("E154:E159,E161:E166,E168:E173,E175:E180,E182:E187,E189:E194,E196:E201,E203:E208,E210:E215,E217:E222,E224:E229,E231:E236,E238:E243,E245:E250,E252:E257"))
    For Each rCell In rng
        If rCell.Value <> "" Then
            Range("AC" & rCell.Row).Value = 10
        End If
    Next rCell

End Sub

 

[Martin_H]

Did you just mean something like this ?

Sub PopulateAC()
    Dim rng As Range, rCell As Range

    Set rng = Union(Range("E14:E19,E21:E26,E28:E33,E35:E40,E42:E47,E49:E54,E56:E61,E63:E68,E70:E75,E77:E82,E84:E89,E91:E96,E98:E103,E105:E110,E112:E117,E119:E124,E126:E131,E133:E138,E140:E145,E147:E152"), Range("E154:E159,E161:E166,E168:E173,E175:E180,E182:E187,E189:E194,E196:E201,E203:E208,E210:E215,E217:E222,E224:E229,E231:E236,E238:E243,E245:E250,E252:E257"))
    For Each rCell In rng
        If rCell.Value <> "" Then
            Range("AC" & rCell.Row).Value = 10
        End If
    Next rCell

End Sub

Exactly what I have been looking for

Would it be also possible to add a comment with some random text to those cells aswell?

Apply same logic as before.

Only add comment with some random text to those cells as value 10.

 

[Alex Blakenburg]

Do you mean an Excel Comment or Note or just text in the cell ?
How do you want the code to work out what text, is the same text for all rows in AC with the 10 ? Do you want me to just set up a variable that you can change later ?

 

[Martin_H]

Do you mean an Excel Comment or Note or just text in the cell ?
How do you want the code to work out what text, is the same text for all rows in AC with the 10 ? Do you want me to just set up a variable that you can change later ?

I mean Excel Comment. Let's just say "Hello", will be the same for all cells in AC with value 10.

I would like to change the text or the value by myself if needed.

 

[Alex Blakenburg]

OK but can you just confirm you mean the new "Threaded Comment" not the Original Comment that is now called a Note ?

 

[Martin_H]

OK but can you just confirm you mean the new "Threaded Comment" not the Original Comment that is now called a Note ?

This kind of comment.

 

[Alex Blakenburg]

Yup they are now called Notes, I will get back to you shortly.

 

[Alex Blakenburg]

See if this gives you what you need.
(and to add to the confusion, although it is not called Note in Excel it is still Comment in VBA)

Sub PopulateAC()
    Dim rng As Range, rCell As Range
    Dim sCmt As String, cmt As Comment
    
    sCmt = "Hello1"

    Set rng = Union(Range("E14:E19,E21:E26,E28:E33,E35:E40,E42:E47,E49:E54,E56:E61,E63:E68,E70:E75,E77:E82,E84:E89,E91:E96,E98:E103,E105:E110,E112:E117,E119:E124,E126:E131,E133:E138,E140:E145,E147:E152"), Range("E154:E159,E161:E166,E168:E173,E175:E180,E182:E187,E189:E194,E196:E201,E203:E208,E210:E215,E217:E222,E224:E229,E231:E236,E238:E243,E245:E250,E252:E257"))
    For Each rCell In rng
        If rCell.Value <> "" Then
            Range("AC" & rCell.Row).Value = 10
            
            ' Optional if you want to replace an existing comment
            Set cmt = Range("AC" & rCell.Row).Comment
            If Not cmt Is Nothing Then
                Range("AC" & rCell.Row).Comment.Delete
            End If
            ' End Optional
            
            Range("AC" & rCell.Row).AddComment sCmt
        End If
    Next rCell

End Sub

 

[Martin_H]

See if this gives you what you need.
(and to add to the confusion, although it is not called Note in Excel it is still Comment in VBA)

Sub PopulateAC()
    Dim rng As Range, rCell As Range
    Dim sCmt As String, cmt As Comment
   
    sCmt = "Hello1"

    Set rng = Union(Range("E14:E19,E21:E26,E28:E33,E35:E40,E42:E47,E49:E54,E56:E61,E63:E68,E70:E75,E77:E82,E84:E89,E91:E96,E98:E103,E105:E110,E112:E117,E119:E124,E126:E131,E133:E138,E140:E145,E147:E152"), Range("E154:E159,E161:E166,E168:E173,E175:E180,E182:E187,E189:E194,E196:E201,E203:E208,E210:E215,E217:E222,E224:E229,E231:E236,E238:E243,E245:E250,E252:E257"))
    For Each rCell In rng
        If rCell.Value <> "" Then
            Range("AC" & rCell.Row).Value = 10
           
            ' Optional if you want to replace an existing comment
            Set cmt = Range("AC" & rCell.Row).Comment
            If Not cmt Is Nothing Then
                Range("AC" & rCell.Row).Comment.Delete
            End If
            ' End Optional
           
            Range("AC" & rCell.Row).AddComment sCmt
        End If
    Next rCell

End Sub

Awesome.

Thank you very much @Alex Blakenburg, much appreciated.