[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Excel 2007 If Then Formula Cell Entry Automated Script

Posted on 2013-12-18
9
Medium Priority
?
397 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Question has a verified solution.

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

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…

649 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