Combining Cells

[inafitz]

Hello there, thank you for taking the time to read my question.

I need to combine two cells via VBA.

For instance, if I'm in cell ES128 I need to combine ES1 and A126. (i.e. =ES1&A128)

In a nutshell, whatever cell I'm in, I need to combine row 1 of the active column and column A of the active row.

So for another instance, if I'm in cell DR272 I need to combine DR1 and A272. (i.e. =DR1&A272).

My VBA skills are not good enough to do this myself, I would appreciate if someone could help.

Kind Regards.

 

[JoeMo]

This is sheet code for the sheet you want cells combined when you change the activecell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Target(1)
Target.Value = Me.Cells(1, Target.Column) & Me.Cells(Target.Row, "A")
End Sub

 

[Nine Zero]

Try this out in the worksheet code

***Important*** Make sure you save the file first as this will continue to edit all cells infinitely and you might not liike what it did

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Value = Cells(1, Target.Column).Value & Cells(Target.Row, 1).Value
End Sub

 

[Logit]

.

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Cells(Target.Row, 1).Value = 0 Or Cells(1, Target.Column).Value = 0 Then
    Exit Sub
Else
    Target.Value = Cells(1, Target.Column).Value + Cells(Target.Row, 1).Value
End If


End Sub

If there is no value in Col A for any row or no value in Row 1 of the active Col, the action exits the macro.

Download : https://www.amazon.com/clouddrive/share/PdnHLx8YVZdk0EGXGl9PesmLMrX7KF2wQlCSjms3JBM

 

[inafitz]

Thank you to everyone that responded.

My sincere apologies, I've had a little play around, but I don't understand Private Subs.

I just needed a bit of VBA to add in to a current macro.

Apologies again.

 

[Fluff]

Maybe

Dim Cl As Range
Set Cl = Range("ES123")
Cl.Value = Cells(1, Cl.Column) & Cells(Cl.Row, 1)

 

[inafitz]

Maybe

Dim Cl As Range
Set Cl = Range("ES123")
Cl.Value = Cells(1, Cl.Column) & Cells(Cl.Row, 1)

Thank you very much kind sir.

 

[Fluff]

You're welcome