98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » If statement based on text string contents | Excel forum
If statement based on text string contents | Excel forum
Resolved · Medium Priority · Version 2010
Thu 22 May 2014, 14:53Delegate 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:45Trainer 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:49Delegate 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:12Trainer 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
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:New Normal WorksheetDo you want all your worksheets to confirm to a certain look? Then change the Defaults!!! |
