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

do until loop

ResolvedVersion 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:

Naming and Using Constants

Constants make calculations easier so worksheets are more easily understood. Constant values also need to be given relevant and memorably names. It is also easier to change the value of a constant.

For example:
Instead of entering 17.5% in each cell when you generate a VAT amount you could name a Constant "VAT" and assigning a "0.175" value to it. To do this:

From the 'Insert' menu select 'Name', then select 'Define'.

Enter the constant

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.