Solved

VBA Macro Not Completing in Excel 2007

Posted on 2013-12-24
10
487 Views
Last Modified: 2013-12-24
I have the following macro that is supposed to generate specific data in column K if a cell in Column A contains a specific value.  This does not complete successfully, and only seems to get to the point of creating the heading Custom Attribute 3, and saving the file as "Active-Transform.xls".  The rest does not seem to occur.  I did combine 2 separate macros, so perhaps the formatting or the way I did it is incorrect.  I'm a novice. Below is the code.  Thanks you in advance!

Private Sub Workbook_Open()
  If ActiveSheet.Cells(1, 11) = "Custom Attribute 3" Then
        'nothing to do as already done
         ActiveWorkbook.Save
    Else
         'fill extra columns
         FillColumnKLM
         'now save and close
         Application.DisplayAlerts = False
         ActiveWorkbook.SaveAs "Active-transform.xls"
         Application.DisplayAlerts = True
  End If
  Application.Quit
End Sub
Sub FillColumnKLM()
Dim WS As Worksheet, I As Long, RwCnt As Long
Set WS = ActiveSheet
RwCnt = WS.Cells(Rows.Count, 1).End(xlUp).Row
WS.Cells(1, 11) = "Custom Attribute 3"
WS.Cells(1, 11).Font.Bold = True
For I = 2 To RwCnt
    On Error Resume Next
    If InStr(1, LCase(WS.Cells(I, 1)), "Fruit") Then
        WS.Cells(I, 11) = "Apple"
        Else
    End If
    If InStr(1, LCase(WS.Cells(I, 1)), "Vegetable") Then
        WS.Cells(I, 11) = "Carrot"
        Else
    End If
Next
Columns.AutoFit

Open in new window

0
Comment
Question by:fireguy1125
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 11

Assisted Solution

by:Gregory Miller
Gregory Miller earned 500 total points
ID: 39738361
Line 23 should be :
If InStr(1, LCase(WS.Cells(I, 1)), "fruit") Then

Open in new window



Line 27 should be:
If InStr(1, LCase(WS.Cells(I, 1)), "vegetable") Then

Open in new window


You are testing for a Lowercase string but your comparison had Initial Cap.
0
 
LVL 1

Author Comment

by:fireguy1125
ID: 39738373
How can I change this so it is exact (i'm not using fruit/vegetable in my actual code, but cities and States, some that are hyphenated, etc).  So in General the first letter is capital, and the rest are lowercase, but in some instances there's a hyphen, etc.  How can I make it so if I enter my If InStr it is exactly like what the source contains?
0
 
LVL 26

Expert Comment

by:pony10us
ID: 39738375
Your code was also missing the End Sub at the end of the second subroutine but that may just be a typo.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:fireguy1125
ID: 39738382
Thanks, pony10us, actually that is a typo.
0
 
LVL 1

Author Comment

by:fireguy1125
ID: 39738384
Also, it still needs to be kept as an "if contains", not "if is", as that cell contains other data I don't want included as part of the source info.
0
 
LVL 11

Expert Comment

by:Gregory Miller
ID: 39738386
Wow, that is not going to have an answer. There is no way to predict what will be there.

Hmmm... I guess you could create an array of all of the unique values in the column and then youwould have the exact data, but I do not know of a simple way to do what you want without a lot of trial and error.
0
 
LVL 26

Expert Comment

by:pony10us
ID: 39738387
No problem. I figured if you had it working at all then it must be a typo.  :)

If you write Option Compare Text at the top of your module, all the VBA comparisons with in that module will use Text comparison instead of Binary comparison. Thus, “yes” will be equal to “YES”.
0
 
LVL 1

Author Comment

by:fireguy1125
ID: 39738391
I don't have a module, can I do it at the VBAProject level?
0
 
LVL 11

Accepted Solution

by:
Gregory Miller earned 500 total points
ID: 39738393
You just put the statement above all of your Subs
0
 
LVL 1

Author Closing Comment

by:fireguy1125
ID: 39738405
Perfect thanks so much!
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question