do until loop

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Do Until Loop

Do Until Loop

resolvedResolved · 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...

test macro.xlsm

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...

DSL GB013596 AR Monthly Upload.xlsm

RE: Do Until Loop

Hi Jens,

Thank you, I cannot see the file you attached. Would you be able to email it to me or attach again?

Thanks

 

Training courses

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Shortcuts for working with named ranges in Excel

If you are working with or creating named ranges in your spreadsheets, then you may find the following shortcut keys useful.

- Bring up the Define Names dialogue box on screen by using Ctrl + F3 (instead of going to Insert - Names).

- Create Names from labels you have entered into the spreadsheet by highlighting the labels and related figures, then hold down Shift + Ctrl + F3. You can then choose to create names from the top or bottom rows, or left or right columns.

- Go directly to a named range by hitting the F5 key. The Go To dialogue box will open and display any named ranges in the spreadsheet. Simply select the named range to navigate to it in the spreadsheet.

View all Excel hints and tips


Server loaded in 0.08 secs.