• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

Excel 2007 If Then Formula Cell Entry Automated Script

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
fireguy1125
Asked:
fireguy1125
  • 4
  • 2
  • 2
  • +1
2 Solutions
 
FaustulusCommented:
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
 
Robberbaron (robr)Commented:
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
 
Harry LeeCommented:
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
What Security Threats Are We Predicting for 2018?

Cryptocurrency, IoT botnets, MFA, and more! Hackers are already planning their next big attacks for 2018. Learn what you might face, and how to defend against it with our 2018 security predictions.

 
fireguy1125Author Commented:
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
 
fireguy1125Author Commented:
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
 
Harry LeeCommented:
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
 
fireguy1125Author Commented:
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
 
Robberbaron (robr)Commented:
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
 
fireguy1125Author Commented:
Thanks - I'll open another question for details involving the automation aspect of this.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now