How to offset and merge columns and enter value in merged columns?

[Dave Smith]

Hi,

I am stuck at one point in vba and i am here to find solution.

I am trying to write program as explained below :
I want to find last row in the sheet (sheet name: "Sheet1") and select the next two rows using offset command and then it should merge column (from A1 to E1) and enter value "Designated value"

I am able to write code for finding the last row and offset it but i am not able to figure out how to merge columns.
The data from range A10 to A15 will be varied as the value enter in the shift cell .

pl. find my code below

Sheet1.Range("A10").End(xlDown).Offset(2, 0).Value = "Designated value"

Any help will be appreciated .

Dave Smith.

 

[Dave Smith]

Hi,

I am stuck at one point in vba and i am here to find solution.

I am trying to write program as explained below :
I want to find last row in the sheet (sheet name: "Sheet1") and select the next two rows using offset command and then it should merge column (from A1 to E1) and enter value "Designated value"

I am able to write code for finding the last row and offset it but i am not able to figure out how to merge columns.
The data from range A10 to A15 will be varied as the value enter in the shift cell .

pl. find my code below

Sheet1.Range("A10").End(xlDown).Offset(2, 0).Value = "Designated value"

Any help will be appreciated .

Dave Smith.

I am not knowing how to merge the columns after using offset command

 

[bebo021999]

Option Explicit
Sub insert_value()
Dim cell As Range
Set cell = Cells(Rows.Count, "A").End(xlUp)
    If cell.MergeCells And cell.Value = "Designated value" Then Exit Sub
    With cell.Offset(2, 0).Resize(, 5)
        .Merge
        .HorizontalAlignment = xlCenter
        .Value = "Designated value"
    End With
End Sub

 

[Dave Smith]

Option Explicit
Sub insert_value()
Dim cell As Range
Set cell = Cells(Rows.Count, "A").End(xlUp)
    If cell.MergeCells And cell.Value = "Designated value" Then Exit Sub
    With cell.Offset(2, 0).Resize(, 5)
        .Merge
        .HorizontalAlignment = xlCenter
        .Value = "Designated value"
    End With
End Sub

Thank you so much bebo021999 for helping me it worked for me