98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Do Until Loop
Do Until Loop
Resolved · Medium Priority · Version 2010
Gemma has attended:
Excel VBA Intro Intermediate course
Excel Advanced - For Power Users course
Do Until Loop
Hi,
I'm trying to write a code that will insert three columns after finding a specific text header. I'm using a Do Until Loop which works fine on one of my previous macro's but when I have changed the text name, it doesn't seem to add in the columns and rename them.
Below is the code, can anyone spot why it seems to miss half the code?
Sub DateFormat()
'
' DateFormat Macro
' Formats the date for the journal upload
'
Dim Str1 As String
Range("a1").Select
Application.ScreenUpdating = False
Do Until ActiveCell = "Document Date"
Str2 = ActiveCell.Text
If InStr(1, Str2, "Document Date") Then
ActiveCell.Offset(0, 1).EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(0, 1) = "Year"
ActiveCell.Offset(0, 1).EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(0, 1) = "Month"
ActiveCell.Offset(0, 1).EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(0, 1) = "Day"
End If
ActiveCell.Offset(0, 1).Select
Loop
Application.ScreenUpdating = True
End Sub
Thanks
RE: Do Until Loop
Hi Gemma,
Thank you for the forum question.
I have tested your code in attached Excel file and it works fine in my file. As I can see in the code you want to add 3 columns after each column where the label has Document Date as a part of the text string.
I can have a look at the file if you send it to info@stl-training.co.uk.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Read more: https://www.stl-training.co.uk/post-34560-formula-shows-quarters.html #ixzz2wE8kbKnu
Attached files...
RE: Do Until Loop
Hi Jens,
I have the file (even created it in a new workbook to check the original wasn't corrupted) but it still doesn't insert the three columns.
Can you tell me how I attached a file to this conversation?
Thanks
Gemma
RE: Do Until Loop
Hi Gemma,
Please send the file attached to an email to info@stl-training.co.uk and please add my name in the subject.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Read more: https://www.stl-training.co.uk/post-34560-formula-shows-quarters.html #ixzz2wE8kbKnu
RE: Do Until Loop
Hi Jens,
The email is on its way.
Thank you
RE: Do Until Loop
Hi Gemma,
The first macro in the first module is correct, but it didn't work first time I ran it, but when I stepped it (F8) it worked and it continued working every time I ran it. I have modified macro 2 in module 1. The only problem was that you asked the loop to stop to early.
I have attached the file with my changes.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Read more: https://www.stl-training.co.uk/post-34560-formula-shows-quarters.html #ixzz2wE8kbKnu
Attached files...
Training information:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Shortcuts for working with named ranges in ExcelIf you are working with or creating named ranges in your spreadsheets, then you may find the following shortcut keys useful. |