Solved

Excel 2007 If Then Formula Cell Entry Automated Script

Posted on 2013-12-18
9
350 Views
Last Modified: 2013-12-23
I have a spreadsheet that contains columns A through J of pre-existing data.  I need to populate additional columns K, L, M which are current blank,with a new heading, and new data based on whether data exists in other columns. As an example:

For Column K - If a cell in Column A contains the words "New Work", then enter "Assigned" in the corresponding row in Column K. The first cell in Column K should be labeled State

I would repeat the same process for the other columns as needed, with other criteria.

I need this to be automated. I would require a script that I can make a scheduled task, and it will automatically enter this information.
0
Comment
Question by:fireguy1125
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 14

Expert Comment

by:Faustulus
Comment Utility
Your project is too big even while its scope is still vague. In order to succeed you will need to cut it into palatable bits which you can dress up as questions and requests, one at a time.
I suggest that you keep the bit about "script that can make a scheduled task" for some distant future and focus on your plans for columns K,L and M. To start off on that please post your workbook.
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
Comment Utility
if you post a workbook with say the top 10 rows of sample data, we can create a VBA macro that runs when the workbook is openned to add the columns if not already there.
0
 
LVL 12

Expert Comment

by:Harry Lee
Comment Utility
In fact, it's extremely easy either using formula or VBA.

With VBA
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) = "State"
For I = 2 To RwCnt
    On Error Resume Next
    If InStr(1, LCase(WS.Cells(I, 1)), "new work") Then
        WS.Cells(I, 11) = "Assigned"
        Else
    End If
Next
End Sub

Open in new window

With Formula
In Column K, enter formula as the following.
=IF(ISNUMBER(SEARCH("new work",A2)),"Assigned", "")

Open in new window

Then copy it all the way down your list.

In order to make it fully automatic, you can have the vba code entered into a workbook open event, to trigger the vba code as the workbook open.

Some modification to the code will be needed. I need to know the worksheet name.
0
 
LVL 1

Author Comment

by:fireguy1125
Comment Utility
Please note I changed my original request of Column names and criteria to the 3 below:

If Location column (Column A) contains "Apple Street" then in Column K (create new heading called Custom Attribute 3) and enter the word "Fruit"
If Location column (Column A) contains "Clear Water" then in Column K (create new heading called Custom Attribute 3) and enter the word "Ocean"
If Location column (Column A) contains "Blue Sand" then in Column K (create new heading called Custom Attribute 3) and enter the word "Sun"

If possible I would prefer the Column to be mapped by the name "Location", rather than the column letter, in case the letter of the column changes, but the "Location" heading will not.

I hope this helps, I appreciate all the input and assistance.
active.xls
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:fireguy1125
Comment Utility
HarryHYLee, I ran you VBscript, however it does not appear to complete.  It only seems to create the column name in Column K, but does not actually populate the cells.
0
 
LVL 12

Accepted Solution

by:
Harry Lee earned 400 total points
Comment Utility
Fireguy1125,

You are using Excel 2007, right? I'm using Excel 2010 and Excel 2013, and the code runs perfectly. I checked all the function I'm using is compatible with Excel 2007. Maybe I have overlooked, and some of the code is not compatible and needed to be changed.

I have included the following code in the attached sample file. Give it a try.

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)), "apple street") Then
        WS.Cells(I, 11) = "Fruit"
        Else
    End If
    If InStr(1, LCase(WS.Cells(I, 1)), "clear water") Then
        WS.Cells(I, 11) = "Ocean"
        Else
    End If
    If InStr(1, LCase(WS.Cells(I, 1)), "blue sand") Then
        WS.Cells(I, 11) = "Sun"
        Else
    End If
Next
Columns.AutoFit
End Sub

Open in new window

active.xls
0
 
LVL 1

Author Comment

by:fireguy1125
Comment Utility
Works perfectly, thank you!

Now to the 2nd part of my question - how can I automate the macro to run when excel file is opened via scheduled task and then save it as active-transform.xls
0
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 100 total points
Comment Utility
try Workbook_Open event but not sure it works consistently when run from scheduled task

in the vba thisworkbook section...
Private Sub Workbook_Open()
  If ActiveSheet.Cells(1, 11) = "Custom Attribute 3" Then
        'nothing to do as alrady done
         ActiveWorkbook.Close SaveChanges:=False
    Else
         'fill extra columns
         FillColumnKLM
         'now save and close
         ActiveWorkbook.Close SaveChanges:=True, Filename:="active-transform.xls"

  End If
        
End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:fireguy1125
Comment Utility
Thanks - I'll open another question for details involving the automation aspect of this.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
"Migrate" an SMTP relay receive connector to a new server using info from an old server.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now