Data from 2 cells into one, with specific format

[happydonut]

Hello,

I have this code to add the value from two cells into one;

Range("C1").Value = Range("A1").Value & " " & Range("B1").Value

The cell in A1 has a date, e.g. 2021-01-25
The cell in B1 has a number, e.g. 123456789
Goal is to have the value in C1 as MMDD from cell A1 followed by the value in B1, e.g. 0125 123456789

Questions:
1. How do I format the C1 to get the value there as explained?
2. In real life, there could be many rows in A1 (all dates) and B1 (all numbers). How can I make a code that takes all rows into consideration, e.g. A2+B2=C2, A3+B3=C3 etc until last row with a value?

Thanks.

 

[FormR]

Hi,

1. How do I format the C1 to get the value there as explained?

Range("C1").Value = FORMAT(Range("A1").Value,"MMDD") & " " & Range("B1").Value

2. In real life, there could be many rows in A1 (all dates) and B1 (all numbers). How can I make a code that takes all rows into consideration, e.g. A2+B2=C2, A3+B3=C3 etc until last row with a value?

You could try something like.

Sub m()
Dim R As Range
Set R = Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)
R.Value = Evaluate("TEXT(" & R.Offset(0, -2).Address & ",""MMDD "")&" & R.Offset(0, -1).Address)
End Sub

 

[happydonut]

Hi,



Range("C1").Value = FORMAT(Range("A1").Value,"MMDD") & " " & Range("B1").Value



You could try something like.

Sub m()
Dim R As Range
Set R = Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)
R.Value = Evaluate("TEXT(" & R.Offset(0, -2).Address & ",""MMDD "")&" & R.Offset(0, -1).Address)
End Sub

Hi, and thanks for reply.

I just tested it. It works, but it jumps over the first row. So it starts with the second.

 

[FormR]

So it starts with the second

Hi, did you try changing the row reference in this part "...Range("C2:C" & Ran...." to a 1?

 

[happydonut]

Hi, did you try changing the row reference in this part "...Range("C2:C" & Ran...." to a 1?

Ah, I did not. Thank you

 

[happydonut]

Just a follow up question,

What if I want the value to be added in another workbook?

So for instance, Value in A1 and B1 from workbook 1
to value C1 in workbook 2

 

[FormR]

This assumes that workbook 1 is the active workbook when the macro runs, if that cannot be guaranteed then some changes will need to be made to full qualify all of the ranges.

Sub m()
Dim R As Range
Set R = Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)
Workbooks("Book2").ActiveSheet.Range("A1").Resize(R.Rows.Count) = Evaluate("TEXT(" & R.Offset(0, -2).Address & ",""MMDD "")&" & R.Offset(0, -1).Address)
End Sub

 

[happydonut]

Thank you.
Sorry to ask another question, but..

What if there is a filter in workbook 1 (i.e. not all dates visible). How can the macro only take the filtered data from Workbook 1 to Workbook 2?

 

[FormR]

Hi, it would need a different technique altogether - you'd probably need to loop through the visible cells one by one.

 

[Marc L]

I have this code to add the value from two cells into one;

Range("C1").Value = Range("A1").Value & " " & Range("B1").Value

Hi, just obviously use Text rather than Value for the source part, not for the destination …​