If statement based on text string contents | Excel forum

Resolved · Medium Priority · Version 2010

Thu 22 May 2014, 14:53 Delegate Gemma said...

Gemma has attended:
Excel VBA Intro Intermediate course
Excel Advanced - For Power Users course

If statement based on text string contents

Hi,

I'm trying to insert a blank column and copy contents into it if i receive a true result, based on an IF statement. Below is the code i have but it doesn't seem to move past the first two columns. I have tested the Do Until Part with the IF statement changing the interior colour white or blue and it seems to work. when I remove the colour indicators and replace with the insert and copy column lines that is when it seems to fall over.

Dim Str1 As String

Range("a1").Select
Str1 = ActiveCell.Text

Do Until ActiveCell = ""

If InStr(1, Str1, "Amount in local currency") Then

ActiveCell.Offset(0, 1).EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove

ActiveCell.Offset(1, 0).Range(Selection, Selection.End(xlDown)).Copy

ActiveCell.Offset(0, 1).PasteSpecial

End If

ActiveCell.Offset(0, 1).Select

Loop

Can anyone help?

Thanks

Thu 22 May 2014, 15:45 Trainer Gary said...

RE: If statement based on text string contents

Hi Gemma

Thanks for getting in touch. Looking at this code, the variable is not getting changed at the right time if I've interpreted it correctly.

You get the value into Str1 at the beginning, but it's never changed after that. Should the line Str1 = ActiveCell.Text come after the Do Until line? That way each time through the loop it would get the latest value of ActiveCell.

In it's current form, it gets the value from A1, then Str1 never changes. So it will insert a column each time through the loop until it runs out of columns.

Do let me know if I haven't got that right.

Kind regards

Gary Fenn
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

Thu 22 May 2014, 15:49 Delegate Gemma said...

RE: If statement based on text string contents

Hi Gary,

Thanks for coming back to me quickly.

That makes sense, I will amend the code. One question I do have then is that I want the macro to start in cell A1 and then work its way along until it finds the right cell. If I put the Activecell line after Do Until will it only work from the cell someone has selected rather than going to the top of the sheet?

Thanks

Thu 22 May 2014, 16:12 Trainer Gary said...

RE: If statement based on text string contents

Hi Gemma

Thanks for your reply. Because you have the line Range("A1").Select at the top, you know the code will always treat that as the first place.

Kind regards

Gary Fenn
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

Thu 22 May 2014, 16:12 Delegate Gemma said...

RE: If statement based on text string contents

ok great thanks Gary

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

New Normal Worksheet

Do you want all your worksheets to confirm to a certain look? Then change the Defaults!!!
1. Press Shift+F11 to create a new worksheet
2. Press Ctrl+A to select (higlight) all cells, Press Ctrl+1, make any formatting changes then click OK.
3. Press F12 (Function 12 key) click in the Save As Type, drop down, then select Template (*.xlt)
4. Click in the Save in drop-down, then find the folder; c:_program files_microsoft office_office_start. (For the underscores shown use backslash)
Name your templete sheet.xlt, then press Enter.
Sheet.xlt is used when you insert a new worksheet (Shift+F11)

Note: These changes are permanent changes on your PC.

View all Excel hints and tips