?
Solved

Excel 2007 If Then Formula Cell Entry Automated Script

Posted on 2013-12-18
9
Medium Priority
?
395 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
Industry Leaders: 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: 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 1600 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 400 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

Need protection from advanced malware attacks?

Look no further than WatchGuard's Total Security Suite, providing defense in depth against today's most headlining attacks like Petya 2.0 and WannaCry. Keep your organization out of the news with protection from known and unknown threats.

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
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

801 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