CUT AND PASTE (FORMULA WITH CONCATENATE)?

[rhwebb]

Need some help from the VBA gurus please. I'm working on a project (simplified here) in which I need to remove the text in cell F2:J2 (merged). The data within that cell will then be placed into cell A3:J3 (merged). However I do not need the entire text/date string-- only "Report Date: dd - mmm - yyyy". I'm looking either for a VBA solution to cut and paste which will isolate that text string (variable date) or a formula to CONCATENATE "Report Date:" with Today(). When I tried to write an ActiveCell.FormulaR1C1= i received an error for expected end. Would really appreciate any help.

Thanks

 

[Peter_SSs]

I need to remove the text in cell F2:J2 (merged). The data within that cell will then be placed into cell A3:J3 (merged). However I do not need the entire text/date string-- only "Report Date: dd - mmm - yyyy".

See if this does what you want

Sub Report_Date()
  With Range("F2")
    Range("A3").Value = Mid(.Text, InStr(1, .Text, "Report"))
    .MergeArea.ClearContents
  End With
End Sub

 

[rhwebb]

See if this does what you want

Sub Report_Date()
  With Range("F2")
    Range("A3").Value = Mid(.Text, InStr(1, .Text, "Report"))
    .MergeArea.ClearContents
  End With
End Sub

Thank you for the help. Unfortunately I could not get this to return the correct result. Instead of cutting and pasting the Report Date: xx xxx xxxx it copied the "personnel report" text from row 2 and placed it in row 3. So now my text is (row 2) 'personnel report', (row 3) 'personnel report'. Both rows are merged and centered which is good. I just need row 3 to have the following text "Report Date: dd - mmm - yyyy". Close but no joy yet.

 

[Peter_SSs]

it copied the "personnel report" text

Isn't that text originally in the merged cells A2:E2?

 

[rhwebb]

Yes, but I'm trying to move the text in F2:J2 down to A3

 

[Peter_SSs]

My code does not reference A2:E2 at all. Something must be different about your sheet set-up to mine, or else you may have modified the code?

Here is my sheet before running the code

rhwebb_1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	HEADER
2	Personnel Report					Source: database Report Date: 17 - Jan - 2022
3
4	FOOTER
Sheet3

.. and after:

rhwebb_1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	HEADER
2	Personnel Report
3	Report Date: 17 - Jan - 2022
4	FOOTER
Sheet3

Is that what you are wanting?

 

[rhwebb]

Very close. I'm hoping for row 2 to be centered as well. Thank you for all of the trouble.

 

[Peter_SSs]

Did you work out what was going wrong before?

In any case, try this

Sub Report_Date_v2()
  With Range("F2")
    Range("A3").Value = Mid(.Text, InStr(1, .Text, "Report"))
    .MergeArea.ClearContents
  End With
  Range("A2:J2").Merge
End Sub

 

[rhwebb]

Yes! I found some code in the original project that I was able to "switch off". That was causing the hang up. Last question. In the code you gave me it was for a text string starting (MID) cell and all text after that. For future reference, how do I keep only part of a string instead of all text past a certain word? I.e. if I only wanted two words "Report Date" instead of "Report Date: dd mmm yyyy".

 

[Peter_SSs]

if I only wanted two words "Report Date" instead of "Report Date: dd mmm yyyy".

How to extract text depends on individual circumstances. It would depend on what variations of text could be in the original cell and what "rule" you want to apply to extract part of that text.
You would need to give a variety of examples of the original text together with the desired result for each to make a suitable suggestion.