Solved

Excel 2007 If Then Formula Cell Entry Automated Script

Posted on 2013-12-18
9
394 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
[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
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 14

Expert Comment

by:Faustulus
ID: 39728747
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)
ID: 39728814
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
ID: 39730080
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 1

Author Comment

by:fireguy1125
ID: 39731908
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
 
LVL 1

Author Comment

by:fireguy1125
ID: 39731952
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
ID: 39732530
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
ID: 39732888
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
ID: 39735523
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
ID: 39736369
Thanks - I'll open another question for details involving the automation aspect of this.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

690 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