Computer Support Forum

Attachment based on cell value in a excel email macro

Question: Attachment based on cell value in a excel email macro

Hy guys

2nd time i am posting stuff for help, and as i was helped before i will again look forward the response.

I have a file of excel, in which i am sending emails to different candidates of admission, with scan letter placed in the same folder by name.

I want to edit this code, which could select attachment based on Column A list adjacent to the email address

I am attaching the file also pasting the code

Sub Test1()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Working in Office 2000-2013
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim SigString As String
Dim Signature As String
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo cleanup
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "C").Value) = "yes" Then

Set OutMail = OutApp.CreateItem(0)

strbody = "We at Graduate School of Engineering Sciences and Information Technology are extremely pleased to know that you have selected Hamdard University as preferred choice for your graduate/post-graduate Studies. " & vbNewLine & vbNewLine & _
"Hamdard University is a pioneer Higher Education Institute (HEI) of Karachi producing Masters and PhDs in the fields of Engineering, Computer Sciences, Information Technology, Energy and Environment since 1997. " & vbNewLine & vbNewLine & _
"Graduate School of Engineering Sciences and Information Technology (GSESIT) at the Faculty of Engineering Sciences and Technology (FEST), Hamdard University (HU), prepares students to meet the challenges of contemporary world with confidence and success, carry out research in most demanding domains and enables the students to develop a level of knowledge and expertise that allows them to practice independently or collaboratively at advanced level in their field of choice. Our academic programs emphasize student/faculty interaction and timely completion of the degree. We take pride in offering quality education at affordable cost and strong service to our students. " & vbNewLine & vbNewLine & _
"Attached here you will find 'A Brief Guide for Prospective GSESIT Students' that will facilitate you in knowing more about Graduate School of Engineering Sciences & Information Technology and its degree program offerings. " & vbNewLine & vbNewLine & _
"Your positive response to this e-mail would trigger our Student Care Management team to take personal care to see you through a seamless joining at Graduate School of Engineering Sciences & Information Technology for a degree program of your choice. " & vbNewLine & vbNewLine & _
"We would like to be with you all the way…. and wish you the best in your academic pursuits. " & vbNewLine & vbNewLine & _
"Keep connected to GSESIT via www.facebook.com/gsesit and www.twitter.com/gsesit or send a message from your mobile containing text " & vbNewLine & vbNewLine & _
"follow @gsesit " & vbNewLine & vbNewLine & _
"to 40404 " & vbNewLine & vbNewLine & _
"for receiving updates about programs held at GSESIT. " & vbNewLine & vbNewLine & _
"For any further information feel free to contact me. "

SigString = Environ("appdata") & _
"\Microsoft\Signatures\Babar.txt"

If Dir(SigString) <> "" Then
Signature = GetBoiler(SigString)
Else
Signature = ""
End If
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Admission at Hamdard University"
.CC = "[email protected]; [email protected]"
.Body = strbody & vbNewLine & Signature
'You can add files also like this
.Attachments.Add ("C:\Users\Babar\Desktop\admissions\Brief Guide for Prospective GSESIT Students.pdf")
'.Send 'Or use Display
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

Function GetBoiler(ByVal sFile As String) As String
'Dick Kusleika
Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.readall
ts.Close
End Function

Relevance 100%
Preferred Solution: Attachment based on cell value in a excel email macro

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: Attachment based on cell value in a excel email macro

anybody ???

2 more replies
Relevance 103.32%

Need some help sorting a large data output to different sheets based on Column A value, seems like this should be easier than what I am doing but I can't get it to workData coming in example
A C H I
4 Dept Product Code Sch # Prod #
5 DeptA
6 DeptA 101 12 12
7 DeptA 102 34 33
8 DeptB
9 DeptB 201 10 11
10 DeptB 103 23 25
11 DeptB 301 45 40
I want to copy this data to Existing Sheet Named "DeptA"
A C E
3 Prod Code Sch# Prod#
4 101 12 12
5 102 34 33
and then same thing for "DeptB" sheet
A C E
3 Prod Code Sch# Prod#
4 201 10 11
5 103 23 25
6 301 45 40

They can be kept in current order, if Product Code is blank skip to next line, if Sch # is blank skip to the next line, copy Prod# whether it is empty or notThanks

Answer:Excel Macro to copy cells in a col based on another cell

Please click on the following line and then repost your example data after reading the instructions found via that link.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 103.32%

Hello

I need to do the following in VBA:

If cell in column C contains the value GBP, change cell S from that row from 13 to 23
or
If a cell column C contains the value USD change cell S from that row from 13 to 33

exception cell: C1 --> contains the title of the column

How do I do this?
 

Answer:Excel 2003 replace macro based on value in other cell

6 more replies
Relevance 103.32%

Hi All,

I need to make a macro that will add borders to certain cells.
I have a generated report from another product so I don't know how many rows there will be until the report is generated. The number of rows is specified inside the report, so I do have access to it. It is stored in cell C5.

So what I want to do is add borders around C5 number of rows starting at A7..E7

I tried using a rule, to just put borders around cells that contain data, but that won't work because sometimes cells are blank and I still want the border around them.

Help?! Please

Thanks a bunch!!!
Lori
 

Answer:Excel Macro to add borders based on cell contents?

Hi Lori

Here is a macro that will take the value in C5 e.g. 10 and then apply borders to all cells in A7 to E16
Code:
Sub add_borders()
lRow = Range("C5") + 6
Range("A7:E" & lRow).Select
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlC9ontinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End Sub
 

1 more replies
Relevance 102.09%

Hi,
I am having data structure as below. I need some macro or some means to populate formula in Column D based on column C (as and when it changes) . All other column data are populated. This is beyond me. Any help or direction is greatly appreciated. Thanks in advance

Col | A | B | C | D | E
------------------------------------------------------------------------
Row | 1 | Test1 | | | 10
| 2 | Test2 | =A1 | =MAX(E2) }| 15
| 3 | Test3 | =A2 | =MAX(E3) | 20
| 4 | Test3 | =A1&","&A3| =MAX (E1,E3)| 30
 

Answer:Excel macro or formula - change field based on other cell

7 more replies
Relevance 99.22%

Hi,

My VBA/Macro knowledge is very basic, but I am able to record macros and edit them. I have a scatter plot (with 5 series) and I want to automatically change the line style (i.e. solid or dashed etc.) depending on the text in a cell. This is what I tried, but I cannot get the code to reference the cell which contains the text:

--------------------------------------------------------------

Sub Macro14()
'
' Macro14 Macro
'
'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineSolid
End With
End Sub
--------------------------------------------------------------

For the line ".DashStyle = msoLineSolid" I tried ".DashStyle = Range("A7").Select" but this does not work.

Is there a simpler way to set-up a macro to format graphs automatically based on a cell content/format/colour, does anyone know a good guide to do this?

Thanks
 

Answer:Excel Macro to automatically change chart line style based on cell text

Hi,

I'm not familiar with charts. But, see if this helps.

http://social.msdn.microsoft.com/Forums/sk/isvvba/thread/50180062-37e0-435e-bf52-0f6de482d8ef
 

3 more replies
Relevance 96.76%

Hi,

New here. I dug up a thread that Zack Barresse solved many years ago. I am looking to do the exact same thing. The link to the thread is below. My file is infinitely more complicated than what that user was asking for so I need a bit more help tuning the VBA. Link: http://forums.techguy.org/business-applications/710581-automatic-email-alerts-using-excel.html

Some specifics:

- I am using Outlook not Express
- Excel 2007
- All the functionality is complete for monitoring several live streams of securities data with several trade indicators.
- It is consolidated onto one sheet for manual monitoring (Picture below). Basically takes copious amounts of data and reduces it to just IF and AND functionality for the triggers for easy use from all the other sheets.
- The workbook will be open and running/refreshing on its own 24/7 as it is now.

I am a busy guy, I just need the VBA to automatically email me remotely when any of the 7 currency pairs causes a trigger when I am on the go. I can log trades from an app on my phone.

One other hurdle would be that if say (Using percentages to keep it simple) that a trigger would be if something reached as high as 80% to send the notification email. But where the system refreshes every 60 seconds it shouldn't send another notification each time it remains at or above 80%. Just the once. It may remain there for hours and that is a lot of emails.


Thoughts? and many many thanks in advance.
 

Answer:Excel - Auto Email based on cell value

10 more replies
Relevance 95.53%

Hi All,

My name is Diego.

Can anyone send me code to automatically send me an email when the date listed in "column J" is the same date as today. Also, it needs to email only once and even if I am not running excel or at my computer. I want to use Microsoft Outlook and use the ClickYes program as well if this helps that was talked about by Zack Barresse in

http://forums.techguy.org/business-a...s-using-2.html
Essentially I have to be reminded of a reapplication for specific state licensures on healthcare courses I provide. I don't want to forget which courses I have to reapply for so I need to have a program that will look at a date which I have in column J and then email me to remind me of this.

BTW - I am using Outlook 2007 and Excel 2007 on Vista.

Thanks. I appreciate your help! Also, extra points and praise for the person who solves this problem!
 

Answer:Automatic Email from Excel based on Date in Cell

16 more replies
Relevance 94.71%

I am working with the attached spreadsheet in Excel 2010 and am trying to figure out how to code certain parameters that will make Excel send myself, my client or other individual an email (with text in body) if certain dates have not been entered into particular cells, or if a cell has exceeded a certain number of days in a particular cell. I have attached a sample spreadsheet and have listed at the bottom 8 points in which I need an email sent, what the trigger is and what the action (email sent to) is.

I just know enough to be very dangerous with Excel but have found that there is a way to code in Excel to send emails which would greatly help my business but I just don't know that much about codes at all.

Can anyone please help me??

Thanks!!
 

Answer:Excel Coding to Send Email based on Cell Entry

Hi, welcome to the forum.

I suggest you do a find in the forum, there are many posts that gao about this and there are many answers, I'm sure there is one that will help yu and of course one of us can help you if you're still stuck
 

2 more replies
Relevance 94.71%

Hi All,

My name is Diego.

Can anyone send me code to automatically send me an email when the date listed in "column J" is the same date as today. Also, it needs to email only once and even if I am not running excel or at my computer. I want to use Microsoft Outlook and use the ClickYes program as well if this helps that was talked about by Zack Barresse in

http://forums.techguy.org/business-applications/710581-solved-automatic-email-alerts-using-2.html
Essentially I have to be reminded of a reapplication for specific state licensures on healthcare courses I provide. I don't want to forget which courses I have to reapply for so I need to have a program that will look at a date which I have in column J and then email me to remind me of this.

BTW - I am using Outlook 2007 and Excel 2007 on Vista.

Thanks. I appreciate your help! Also, extra points and praise for the person who solves this problem!
 

Answer:Automatic Email Reminder from Excel based on Date in Cell

Please do not post duplicate threads.
One thread per issue.
Continue replies for this issue in this thread: http://forums.techguy.org/business-applications/856705-automatic-email-excel-based-date.html
Thank you.

Closing thread.
 

1 more replies
Relevance 93.48%

Good Afternoon - this is a follow-up to an earlier post that has been closed.

http://forums.techguy.org/business-applications/1090938-emailing-multiple-recipients-excel-based.html

I would like to do something similar.

My Excel sheet has a list of Email addresses in Column A (with duplicate email addresses).
I have several other columns with data that that I would like to have appear in the body of the email in Outlook.

I need to collate each row with the same email address so ONLY 1 email is sent to each recipient.

Is this something easy to do?
I have little to no VBA coding skills

Attached is an Excel mockup of what I am attempting to accomplish.

The 1st tab called "Sample Data" is basically the raw data I want to leverage.
(which I also tried to display below)
Email Address .....Invoice Number .....Date..... .....Dollars
​ [email protected] .............1 ...............7/3/2013 ......$10,000
​ [email protected] ..............2 ...............7/9/2013...... $50,000

[email protected] ..........3 ...............7/9/2013 ......$40,000

[email protected] ............4 ...............7/10/2013 .....$1,000

[email protected] ............5 ...............7/11/2013 .....$3,000
​The 2nd tab called "Body of Email" is an example around how I would like to see the data appear in the email.
Even though [email protected] appears 3 times in the above example, I ONLY want him to receive 1 email that contains 3... Read more

Answer:Emailing multiple recipients from Excel Based off Cell Value Collate to one email

8 more replies
Relevance 93.48%

I need a code that will allow the workbook to be emailed when Column A is populated by certian numbers. The numbers in column A corespond to particular email addreses. This is the code I've been working but it isn't functional.

Sub Email_Out()
If Worksheets("Sheet1").Range("A5:A200") = "190030001" Then
ActiveWorkbook.SendMail Recipients:=("[email protected]")
ElseIf Worksheets("Sheet1").Range("A5:A200") = "190450025" Then
ActiveWorkbook.SendMail Recipients:=("[email protected]")
End If
End Sub

All help is greatly appreciated!
Mikey
 

Answer:Solved: VBA email excel workbook based on cell values using; If Then ElseIf Please he

16 more replies
Relevance 93.48%

Hi This is a follow up to

http://forums.techguy.org/business-...emailing-multiple-recipients-excel-based.html

I would like to be able to do the same

My excel sheet keeps a list of Email addresses on column B (with duplicate email addresses), and their particulars from column C (Item price, purchase date, etc) onwards.

I need the vba to email multiple recipients (those with the "notification" column E field marked as yes) with their purchasing details in it. I need to collate each row with the same email address & marked Yes so that only one email is sent.

eg: email will have in the body

Your order are ready to collect:

row 2 information
row 5 information
row 9 information
It should also prevent multiple emails to the same email address. I would like not to have to change the Notification column to acheve this.

Thank you for your help.
 

Answer:Emailing multiple recipients from Excel Based off Cell Value Collate to one email

6 more replies
Relevance 85.69%

Hello,

I cant seem work out a solution for what I'm trying to do. I have an Excel workbook that has multiple sheets. On sheet 1 i want the data from cell "G3" to be copied onto sheet 2. But i want the location on sheet 2 to be based on whatever was entered into cell "D3" on sheet 1.

For example: Sheet 1, cell D3 I have the name John, in cell G3 i have 68. I want "68" to be pasted in sheet 2 in cell B26.

But if the name in Sheet 1 cell D3 is Suzie, then I want G3 to be pasted in Sheet 2 in cell D26. So I would need to identify the paste location for each person.

I want the data to paste to the next cell so that the next entry can be pasted below the last entry for that person (for John the first entry would go into cell B26, then the next entry would go into cell B27 and so on).

But i want it to be a specific range, i dont want data to be pasted past 20 cells (cell B45). If possible a message box could be created to let the user know that the max is reached.

I would appreciate anyone's help with this as i have been struggling for awhile to try to get this. Thank you
 

Answer:Excel - Copy paste cell into range based on another cell

12 more replies
Relevance 85.69%

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
 
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
 
I am using excel 2013 on windows 10. Any help would be appreciated.

More replies
Relevance 85.69%

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
 
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
 
I am using excel 2013 on windows 10. Any help would be appreciated.

More replies
Relevance 84.87%

I'm attempting to write my first macro for an Excel 2003 workbook. I'm not completely code illiterate (I've got moderate skills with AutoLISP), but I'm new to VBA and am not yet an Excel power user, so please be gentle.

The macro I want to write will:
check that the selected cell's content is underlined before proceeding
copy the content of the currently selected cell into an external plain text .log file
.log file lines should be: year/month/day - time - username - cell contents
.log file names will probably need to be generated
clear the cell's content and formatting (particularly underline and text/background color)
Here's what I have so far:
Code:
Sub Unpost()
If Selection.Font.Underline = True
Then Selection.ClearFormats And Selection.Clearcontents
Else
If MsgBox("The selected cell is not underlined...are you sure?", vbOkCancel) = vbOk
Then Selection.ClearFormats And Selection.Clearcontents
Else Exit Sub
End If
End If
End Sub
If I've written it correctly, it should currently do everything except log the cell contents. This, from what I've seen, is going to be the trickier part. I intend to use this macro 50+ times per weekday, so at some point the .log files will get too long to be useful, so I assume it will need to automatically create new logs (perhaps "year-month.log"). I've seen some useful info about appending to an external log here and here, ... Read more

Answer:Excel 2003 macro: log contents of selected cell, clear cell

You need to use the "File Scripting Object" to create and/or append text to a file. I've included a link below to get you started. If you are unable to figure it out on your own let me know and I'll write the code for you.

http://www.virtualsplat.com/tips/visual-basic-fso.asp

Rollin
 

1 more replies
Relevance 84.46%

Example of what has to be done by the macroI need a macro which should add below each row the quantity of rows shown in the column named "TO-FROM".Then, the same information of the original row should be copy and paste in the new rows that were added. However, anther column should be added named "Valid Date". The first row should contain the date included in the column named "Expense From/On Date" and the last row should contain the date included in the column named "Expense To Date". The rows between these two rows should contain the dates that form a continuous from the first to the last date in question. Then, the same should apply for te following rows.The attachement shows further information of what the excel contains.message edited by Lukiano16

Answer:Macro to Add rows based on the infrmation in another cell

Thanks for letting us know what you "need" and what the macro "should" do.When you are ready to ask for help with that, just let us know.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

5 more replies
Relevance 84.46%

I use a macro to print shipping forms & labels based on data entered on a "Data Entry" worksheet. When someone pushes the button the macro transfer this data from the data entry page to another worksheet (used as a database/history), prints the forms/labels to specific printers, clears the fields on the "Data Entry" page, and saves the file.A couple of the labels are only required if data is entered into specific fields, so we are currently wasting labels using the current macro.The workbook has the following worksheets: Data Entry, Std Cert Form, Label, ILT, Line #The ILT worksheet only needs to print if there is data in B16 on the Data Entry worksheetThe Line # worksheet only needs to print if there is data in B18 on the Data Entry worksheetHere is the portion of the macro that currently deals with the printing: Sheets("Label").Select Application.ActivePrinter = "DYMO LabelWriter 450 Turbo (Copy 1) on Ne00:" ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "DYMO LabelWriter 450 Turbo (Copy 1) on Ne00", Collate:=True Sheets("ILT").Select Application.ActivePrinter = "DYMO LabelWriter 450 Turbo (Copy 1) on Ne00:" ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "DYMO LabelWriter 450 Turbo (Copy 1) on Ne00", Collate:=True Sheets("LINE #").Select Application.ActivePrinter = "DYMO LabelWriter 450 Turbo (Copy 1) on Ne00:" ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "DYMO LabelW... Read more

Answer:How use macro to print based on cell content.

Sorry the macro code did not stay properly indented when I posted the message.Each line that starts with " should be indented.

6 more replies
Relevance 84.46%

I am making a timesheet in Excel. I want to insert a row beneath my active cell if the value is less than 8. Range is N13:N28. If the macro could copy the the formating and fourmulas from the row above but not the values that would be good otherwise just a blank row will work.

Answer:Macro for inserting blank row based on a 8 value in cell

Try this...Select a cell in your N13:N28 range.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count = 1 Then
If Not Intersect(Range("N13:N28"), Target) Is Nothing Then
If Target < 8 Then
Target.EntireRow.Copy
With Rows(Target.Row + 1)
.Insert shift:=xlDown
.ClearContents
End With
End If
End If
End If
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 84.46%

Dear Fellows

I am looking for help for a macro which can copy data to other sheet based on adjacent cell value which i define.

For example i have data attached in a sheet.

i want to copy data based on value present in E column to different sheets. if it is ES then all ES rows should be copied to sheet ES with roll number name father per and dept.

Same is for other departments, on different sheets. There will be 9 departments overall.

Looking for response
 

More replies
Relevance 83.23%

I need a macro that will Insert 3 rows under every change in value in column E. I've inserted a column after Round E and used the formula =if(E2<>E3, "Last","") to enter the value of Last next to the cell in column E just before it changes to the next value in column E. I did this thinking I could come up with the macro myself using this column F data but I'm now stumped! Looking forward to hearing back!

Answer:Macro needed to Insert Rows Based on Cell Value Changes

Hi,Here is very quick solution, it works, but there is one issue, when you run it the second time it again inserts 3 rows, this shouldn't be an issue to fix, I will try to fix it when I get sometime, as I said in my pm, I am now on Christmas holidays and the wife has a list (a big list) of chors for me to do :(At least this is a startDim uRange
Dim lRange
Dim Bcell As Range
Dim FirstVal

Sub SortColumn()

Columns("A:E").Sort key1:=Range("E1"), _
order1:=xlAscending

InsertRows

End Sub

Sub InsertRows()

Set uRange = Range("E1")
Set lRange = Range("E" & Rows.Count).End(xlUp)

FirstVal = Range("E1").Value

For Each Bcell In Range(uRange, lRange)

If Bcell.Value <> FirstVal And Bcell.Value <> Empty Then

For i = 1 To 3

Bcell.EntireRow.Insert

Next i

FirstVal = Bcell

End If

Next Bcell

End Sub

9 more replies
Relevance 83.23%

I am trying to write a macro for excel that will allow me to save as (Range("A1").Value) into a file that changes according to the value in cell "A2". The path will be constant. The document I am making is a monthly log. Within the workbook I have sheets numbered 1 - 31 and another sheet called "Data Sheet". Each sheet from 1 - 31 has a date and the month and year can be easily changed in "Data Sheet" by changing the month in cell "A1" and the year in "A2". Right now the workbook is saved as "Log - Template". I want to be able to just click a button and have it save the whole workbook as <C:\Users\Tom\Desktop\FA\ (Here it would have the year that is listed in "A2") \Log - "A1" "A2".xlsm>. This way with in the 2010 folder there would be "Log - Jan 2010.xlsm", "Log - Feb 2010.xlsm", "Log - Mar 2010.xlsm", and so on. Then When I am ready to make "Log - Jan 2011" it would automatically make a 2011 folder and save the workbook in that folder.

Answer:Macro to Save File Based on Cell Contents

Your stats say you are using Excel 2003, but you used xlsm in your post.I recorded then modified this in 2003, so some of the options may differ for 2007.Just to be safe, you might want to record the Save As operation and then modify the code so it looks like my example.You may need to include Sheet names, etc. The basic idea is to build the filename using the references to the Ranges. Don't forget the space.
Sub SaveMonthYear()
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Tom\Desktop\FA\" & Range("A2") & " " & Range("A1") & ".xlsm", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

4 more replies
Relevance 83.23%

I would like to have a macro create a new worksheet naming it the value of the cell in the second row when I click on the 5th cell in the same row.Here is an example of my data.A B C D E(Blank) Asset ID Service Class Unit # Details L01L22A26 Class 1 01 Details L03L01A36 Class 1 03 Details L03L06A08 Class 1 03 Details L03L07A44 Class 1 03 Details L03L07A45 Class 1 03 Details L05L01A01 Class 1 05 DetailsSo, when I would click on Details in the last column, a new worksheet would populate named L01L22A26. Then I would like to have the data in the column B and C of that row, copied over to the new worksheet at Range D2:E2. I have thousands of rows of data and only want them to create a new sheet when clicked.Any help is greatly appreciated!! Thank you in advance for your time.

Answer:Macro to create new worksheet based on cell clicked

re: ...create a new worksheet naming it the value of the cell in the second row...The second row? If you are always going to be copying data from the second row, why does it matter that you have "thousands of rows of data"?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 83.23%

I am looking for a macro that when I click on a button, the workbook is saved as a PDF file and automatically named based on cell content. Excel 2007Sub pdfsave()pdfname = Range("A1").ValueActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfname, Quality:=xlQualityStandard, _IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _TrueEnd SubThis works, but I want to save it straight to a specific folder on my computer. for example c:\my documents/PDF.Also, do you you if it is possible to save the document as a PDF and at the same time as an excel file in the same macro?

Answer:Macro to Save File as PDF based on Cell Conte

"...save it straight to a specific folder..."Try changing Filename:=pdfnameto Filename:="c:\my documents\PDF\" & pdfnameAs for saving it as an Excel file...Hopefully someone can help you out with that.I will only be able to that tonight.In what Excel format do you want to save the file?Excel 2003 (.xls)Excel 2007 Macro-Free (.xlsx)Excel 2007 Macro-Enabled (.xlsm)

8 more replies
Relevance 83.23%

Hi! I'm positively stumped on how to fix this issue: we use TFS for story management, and have recently gotten an updated version we're being asked to use 'out of the box'. As PM, I have a lot of analysis I do on our story backlog and a lot of reporting that needs to come from the backlog. Most often, I need to 'roll up' analysis from story level to feature level. Feature is not a field you can populate on a story, but you can create a feature and link the story to it as parent and child. In Excel of course this shows up in a tree view, with nothing on the rows containing user stories that tie it to a feature other than the initial structure of the download. Parent (feature) shows up in column 'title 1' for feature rows and 'title 2' is blank, for user stories 'title 1' is blank and story title is in 'title 2'. I desperately need to populate the title 1 field for all user stories with the proper feature. I will build out a little chart as example to post. Just needed to post this part immediately (though I'm on my phone) before I either cry or quit my job.

Answer:Macro/command to fill down based on last populated cell

Looks like this will come in two flavors - for most teams their stories will break out as above, some will have an Epic, then a Feature, then a Story (title 1, 2, and 3 respectively) and for reporting I would need epic and feature to populate on each line. Trying to add an example below, not working as I'd hoped. I'll keep trying!

4 more replies
Relevance 83.23%

In VBA, I'm having trouble with the following: I would like to move to a cell that is "x" number of cells to the right of the current cell selected. And "x" equals a value in cell 1E After that cell is selected, I would like for the column that this newly found cell is in and the 200 columns to the right of it, to be deleted.Thank you!

Answer:Excel.VBA move to cell based on other cell

re: "In VBA, I'm having trouble with the following..."When you say you are having trouble, does that mean you have tried some VBA code that is not working?If so, why not post what you have and we'll see if we can point of the "troublesome" areas.

4 more replies
Relevance 83.23%

I have an excel spreadsheet that contains a series of 5 number and a date. I am looking for every occurrence of a second number in a row if I find the first number anywhere along that row.for example - I want to find every occurrence of 27 if I find 6 first.9/26/12 2 18 19 35 369/25/12 12 20 35 36 379/24/12 11 18 22 32 399/23/12 6 10 27 35 399/22/12 6 23 27 32 379/21/12 2 33 34 35 399/20/12 13 17 18 19 379/19/12 10 12 15 27 349/18/12 4 16 17 35 389/17/12 2 3 5 9 219/16/12 6 15 22 33 36

Answer:Looking for cell contents based on another cell in excel

First, a posting tip...Please click on the blue line at the end of this post and read the instructions on how to post example data in this forum. As for your question, what do you mean by "find" every occurrence of e.g. 27? Do you want the address returned? Do you want to highlight it?Please clarify.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

10 more replies
Relevance 82.41%

Excel Macro Change Font ColorI was looking for some help in a workbook. This workbook consists of multiple sheets. Each sheet contains data that I entered, formulas that add items on an individual sheet, links to other sheets and links to other workbooks. I would like to write a macro that will change the color of the font in the entire workbook that depends on the formula or lack thereof in a cell. The font color is based on four scenarios:1) If the cell contains text (such as the word "Average") or a hard-coded number (such as "150,000"), the font of the cell should be in blue.2) If the cell contains a formula that calculates based on items on cells located in on the sheet where the formula is located, the font color should be black. For example on Sheet1, the font of the cell containing the formula =SUM(E4:E12) should be black.3) If the cell contains a formula that calculates based on (a) cells located on the other sheets or (b) located on the other sheet plus the sheet where the formula is located, the font color should be green. For example on Sheet1, the font of the cell containing the formula =Sheet2!A1 or =Sheet2!A1+Sheet1!A1 should be green.4) If the cell contains a formula that calculates based on items on cells located in an entirely different workbook, the font color should be red. Any help that you could offer is greatly appreciated.

Answer:Need a macro to change font color based on cell formula

I am assuming that the default font color in your workbook is Black, so this code doesn't bother checking for formulas that reference the sheet that the formula is on. If the font is already black, why write code to set it to black?Obviously you'll have to test the code in your workbook to see if it does what you want since here is no way for me to replicate your formulas, text or numbers.It seemed to do what you asked for when I set up a test workbook.I suggest that you try it in a backup copy of your workbook since macros can not be easily undone.Sub ColorMyFonts()
'Loop through Sheets
For shtNum = 1 To Sheets.Count
'Loop through cells
For Each cell In UsedRange
'If Text or Number, Font is Blue
If WorksheetFunction.IsText(cell) Or WorksheetFunction.IsNumber(cell) Then
cell.Font.ColorIndex = 5
End If
'If Formula, check for "[" to see if a workbook is referenced
'If no "[", check for "Sheet" to see if Sheet is refernced
If cell.HasFormula Then
myForm = cell.Formula
If InStr(1, myForm, "[") > 0 Then
cell.Font.ColorIndex = 3
ElseIf InStr(1, myForm, "Sheet") > 0 Then
cell.Font.ColorIndex = 50
End If
End If
Next
Next
End Sub
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 81.59%

Hi, I hope someone can help me. I have an Excel sheet, where there is a variety of data. In order to better analyze some raw data, I need Excel to detect "Arrival Date" and "Length of Stay", to determine how many times it needs to replicate the original line, and add a cell specifying the date which corresponds to each new line.

Answer:Macro or Formula to Insert rows and copy data based on cell

What do you mean by "detect"?Are you looking for a macro to run against an existing set of data and insert the rows or is this something that you want to happen automatically when a user enters data?Please be more specific about your requirements and how your data is laid out, such as what columns need to be searched, etc. Keep in mind that we can't see your data from where we are sitting, so you need to tell us everything we need to know so that we can help you.Thanks!Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

11 more replies
Relevance 77.9%

Hello.

I have been trying to work out how i can select a file from mY c drive to add as aan attachment

I know how to build the E-mail up i am looking for how to select and install an attachment.
 

Answer:Add PDF file to e-mail, excel, Macro, attachment

Are you looking for a macro to include the .pdf file in a excel macro ? Could you please give more information about your need ?
 

2 more replies
Relevance 77.49%

Hello,I have a similar problem and tried adapting this with no luck. If there is a 1 in column F, I need the full row to repeat, including the functions within in. Here's an example: AM33SAS-F.bmp M SAS 33 0 0 AF05SAS-F.bmp F SAS 05 1 AF05SAS-F.bmp <-- This row must repeat.I need info on how to input this as well. I have been trying to create a Macro, but I'm not sure I did it properly. The videos online don't help. With the above script, I have received errors for the name I gave the Macro, End Sub Expected, and even when I don't get an error nothing happens. I need the macro to apply to specific sheets. Please help!Here is my adapted script: Private Sub Worksheet_Change(ByVal Target As Range)On Error GoTo errHnd'Don't do anything if more than one cell has been changed If Target.Cells.Count > 1 Then Exit Sub'Determine if the changed cell is in Column F and is a 1 If Target.Column = 6 Then If Target = "1" Then'Disable events so code doesn't fire again when row is inserted Application.EnableEvents = False'Copy & Insert changed Row, Clear dotted lines Target.EntireRow.Copy Range("A" & Target.Row + 1).Insert Shift:=xlDown Application.CutCopyMode = False End If End IferrHnd:'Re-enable event Application.EnableEvents = TrueEnd Sub

Answer:Macro to insert a row based on a value in Excel? Please help

Just so you know that you aren't being ignored, I've read your post and will work on something a bid later. I won't have access to Excel until later today.In the the meantime...1 - The code you posted is known as an "event macro". It is designed to fire when an event occurs, in this case, a change to the spreadsheet. Specifically, it does its job as soon as a 1 is manually entered in Column F by the user.I am assuming that you want to loop through an existing data set and copy each line where a 1 is already present in Column F. Please confirm this.2 - Please click on the following line and read the instructions on how to post VBA code in this forum. Thanks.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

6 more replies
Relevance 77.08%

Sounds like a simple thing but I’m having troubles finding an easy answer. Still searching ….

I want to create a catalogue for a customer of mine (basic – but might be more extensive later)

– showing a thumbnail picture and then a short product description. Then he can go into the doc and correct the descriptions etc.

Not sure if excel or word or other is the best place.

He has over 200 product pictures stored on disks and folders on hard drive. I have corresponding description in excel based on a code that is the photo name (+.jpg).

So, I have the product files name as HB1000.jpg and all I want to do is show the picture (say at size 100x100)

Ideally I’d like to build a reference based on the code and not have to insert each file.

Thoughts??

So far – tried creating a formula/reference in excel with “Code” HB1000 (Cell A2) and image cell contains “=c:\productimage\”$A2$”.jpg” - can not get the pic to display
 

Answer:How to display images in Excel based on cell ref

The answer is already on here, I posted it fairly recently, it requires VBA to do it well.
see this thread
http://forums.techguy.org/business-applications/723855-solved-insert-image-based-cell.html
 

2 more replies
Relevance 77.08%

I have several large spreadsheets containing information about building contractors and numbers of items fitted into houses.I would like to colour code many of these items so that they are specifically excluded (or included) in the sum totals at the ends of the rows and columns.Is it possible to sum rows and columns based on the colour of the text or cells.If so please explain in terms as simple as possible as I am not yet into manual coding etc. within Excel.

Answer:Sum in Excel based on text (or cell) colour

You can use conditional formatting rules to set the colour of a cell from the Format menu. I'm still trying to work out the next bit but I thinks it's an array formula but I don't know much about them.

9 more replies
Relevance 77.08%

I would to insert a row in excel only if by comparing two valves in seperate sheet and if the value matches copy the entire row paste it by inserting a new row below it. can this be done easily with formula or should we have to go macro. please help me with this

Answer:How to insert a row in excel based on values in the cell

A formula can not insert rows, so you will need a macro.We will need more details before any help can be offered. What values are be compared, which sheets, etc.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 77.08%

Hi all,

I have a workbook containing 30 worksheets. The first sheet is named Master, while the other 29 worksheets are named with cities where we held branch offices (Los Angeles, New York, Chicago, Washington D.C., etc.)

I am trying to write a macro that will copy the contents of a row in the Master sheet to a particular worksheet based on the contents of the cell in that row that contain the location.

To illustrate:
Master sheet:
Columns are: Staff Name, Location, Project status

We have over 5000 staff.
I am trying to write a macro that will copy each staff's record to the worksheet corresponding to his/her location, depending on their project status.

So I am planning to use macro buttons labelled A, B or C (these are the values in the Project status column). So when I click on the button labelled A, the macro will go through all 5000 employees, and for those employees whole project status is "A", will copy that employee's record into a worksheet depending on his/her location.

For example:
Staff name, Location, Project Status
Joe Doe, Los Angeles, A
Jane Davis, New York, A
Mike Smith, Los Angeles, C

When I click on the macro for "A", it should copy Joe Doe's record into the worksheet labelled Los Angeles and copy Jane Davis' record into the worksheet labelled New York.

Thank you so much for your help. I've been searching all over the place for the command to use to reference the worksheet.
 

Answer:Excel - how to reference a worksheet based on value of a cell

For the love of PacMan, make sure you save your file BEFORE you try this. Try it on a copy of the file, even.

HTH,
Andy

Sub A_Status()
'select the list of staff names on Master sheet, then run
For Each Cell In Selection
If Cell.Offset(0, 2).Value = "A" Then
DestinationSheet = Cell.Offset(0, 1)
Cell.Resize(1, 3).Copy
Sheets(DestinationSheet).Select
'assumes each location sheet has a header row
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Master").Select
End If
Next Cell
Application.CutCopyMode = xlCopy
End Sub
 

2 more replies
Relevance 77.08%

Hey all:

I'm have really no experience with coding, so I'm out of my depth trying to do this myself. Hopefully someone can help, so here goes!

Essentially, my excel sheet keeps track of dates of when files need to be renewed. They need to be renewed every 60 days. So I have an "IF" formula that tells me if it's time for renewal.

What do I need to do to get an email notification whenever a file comes up for renewal? I've attached a sample sheet.

Best,
Nick

PS.
Using Excel/Outlook 2010
ClickYes Pro is installed (don't know if it will be necessary or not)
 

Answer:Solved: Emailing from Excel Based off Cell Value

16 more replies
Relevance 76.67%

Hello,

I have experience with macros, but it is very limited. I have imported data into Excel that covers several periods (of time). In column A, the way it imported, I have both dates and the text "Period". What I am needing is a Macro, or other utensil, that I can run that will insert a cell to the left (similar to if I were to manually insert a cell and "shift cells right") of every cell that contains text "Period".

If possible, it would also be nice if I could get the Macro to bold this entire row also.

Thank you so much for your help!!!

Thanks,
Mitch
 

More replies
Relevance 76.67%

I need to export the contents of one and the same cell to 100 different text files.

For example... I need the contents of Sheet1 Cell A1 to be exported to new text files. The names for each text file is on Sheet2 Cell A1 - A100.

Does anyone have a code example for this? I have search and found some similar but i couldnt figure out the code to modify it.

Thank you in advanced.

asm
 

Answer:Export Excel Cell with Macro

Hi asm,
and will the exported file be a new one or can it be an existing text file?
If the file is already presnt then what? Delete the file or append the new data to it?
Which version of EXCEL are you using?
Macro's a problem ?
 

1 more replies
Relevance 76.67%

Hi all,
i am trying to create a summary page for a test document, each module of testing has been given its own tab in the workbook and i want to use a macro to:
1, look up the correct tab
2, find the latest test run that has been finished
3, return the test round number, the number of tests run, and the number of tests that passed.
I am using Excel 2003 edition.

I can make the macro the problem is i have to make 3 SEPERATE macros to return the 3 values. I would like to only have one that can return all three values.

the following ths the macro that i have so far:

Option Explicit
Private m_iColumn As Integer 'm_iColumn currently on
Private Const m_iColumnJump = 7 'number of m_iColumns in between test runs
-----------------------------------------------------------------------------------------------------
Function Last_Test(Round_lookup As String)
m_iColumn = 3
Do Until Worksheets(Round_lookup).Cells(3, m_iColumn) = "0" Or Worksheets(Round_lookup).Cells(3, m_iColumn) = vbNullString
'runs until finds nothing for a test run
If Worksheets(Round_lookup).Cells(3, m_iColumn) = "0" Then
'looks to see if cell contains a testrun if it doesnt then
Else
m_iColumn = m_iColumn + m_iColumnJump
'adds 7 to the m_iColumn number
End If
Loop

If m_iColumn = 3 Then
Last_Test = "Test run not started."
Else
Last_Test = Worksheets(Round_lookup).Cells(1, m_iColumn - 7)
'the last m_iColumn is the name we want
End If
End Function
---------... Read more

More replies
Relevance 76.67%

Hi there,
I'm attempting to make a macro in an excel workbook which will find the first unused cell in a given row and then select it, so that I can have the user input whatever information it is that they wish to input. I've got that second part of the problem figured out, but I cannot seem to figure out good code which will select the last cell in the row. I found on another webpage this code:
LastRow = Range("A6000").End(xlUp).Row
which apparently uses the End function to find the last cell in a given column and then names this cell "Last Row". This is basically the opposite of what I am trying to do, so I attempted to reverse most of what was going on in this line of the code:
LastColumn = Range("BJ1").End(xlLeft).Column
However, this code, and any variation of it which combines End and Column always ends up encountering a bug in the code and failing when used. Could anyone lend me some support here? I'm really hurting for it.
 

Answer:Excel Macro which Selects Last cell in a Row?

8 more replies
Relevance 76.67%

Hello!

I am new to this site and to the world of macros. I am creating a form in Excel for my company and cannot find any info for creating a macro for hyperlinked info.

Background of my form:
It's a time off request form. My dad (who knows a lot of Excel and macros (but not this kind)) has helped me get to where I am at this point.

Currently I have a dropdown for employee names. Once a name is selected, it automatically fills in their manager and department number. I have a Reset Form button to clear info out and would like to create a Submit Form button that would send the form to the manager. Is something like this possible?

I'm guessing it would need to pull from my Tables sheet.

Help! I hope I made some sense.

P.S. I will attach a sample of what I have so far.
 

Answer:How to have a macro add hyperlink to a cell in Excel

9 more replies
Relevance 76.67%

I'm trying to get the cell (row & column) where a checkbox is at a excel sheet, using VB code in an excel macro.
the idea is to modify the cell properties (color, fonto, other) when the checkbox is true, and that will be only for that particular cell (where the checkbox is placed).

Any ideas?

Thx.
 

Answer:CheckBox cell in Excel Macro

7 more replies
Relevance 76.67%

So, I have a Excel spreadsheet of file paths (All in column A) and their respective end file (in column B) and I need the macro to go down column A and compare the file paths to one another, and when they are different it needs to insert a row underneath both column A and B where the path differs. Hopefully someone can help with this. Thanks in advance!

Answer:Excel Macro To Insert Row based on Comparison

Sub InsertRows()
'Determine last Row with Data in Column A
lastRw = Cells(Rows.Count, "A").End(xlUp).Row
'Loop through rows in reverse order, looking for a change in Column A
For nxtRw = lastRw To 3 Step -1
If Cells(nxtRw - 1, "A") <> Cells(nxtRw, "A") Then
'If a change occurs, Insert Row
Cells(nxtRw, "A").Insert
End If
Next
End Sub
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

8 more replies
Relevance 76.67%

Hi,If I have a sheet (Sheet1) with 3 columns (A, B and C) with data from row 2 to 100, where: columns A and B have text and column C has TRUE/FALSE.I would like to have a macro to copy the text of column A (if True) or B (if False) to column D.Note that I cannot reference cells as same text is strikethrough. Basically an if function would solve if the text was not strikethrough!Would really appreciate an help on this.Many thanks

Answer:Excel Macro to copy based on if function

I do not know what this means:"Note that I cannot reference cells as same text is strikethrough"Please post an example of what you are trying to do. Click on the following line and read the instructions found at that link before posting any data.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

5 more replies
Relevance 76.67%

Alright this one might be a bit of a challenge. I am releasing a complex spreadsheet with my product pricing on it. Is there a way to make the program have a "shelf life" of 6 months or so? I would like to set it up to make it inaccessible to anyone without the password after a certain time period. So basically I would like to create a macro that will disable the entire spreadsheet (without the password) after a specific date so I can release new versions (with the new pricing) without the old versions (old prices) getting thrown into the mix. Anyone have any ideas?? Thanks.
 

Answer:Excel Macro - Date Based Protection

Hello CWDENVER, this should be possible to achieve, you can have a date held in a cell in a hidden Protected sheet in the workbook. When the System date = the date in the Cell the Target Worksheet is hidden and can only be made un-hidden by entering a password.
To be effective the Menus would need to Removed from the Workbook as well.
The VBA Editor would need to be password protected as well.
 

2 more replies
Relevance 76.26%

I have searched the net but cannot find anything specifically what I'm trying to accomplish.

I have a timesheet, of sorts, that I added buttons to move around and I would like to add a button for the user to click and it will send the Excel spreadsheet to me when they're done entering their data. I can get the email to open a new email message with the file attached but I'm stuck on getting my name in the To field, as well as a subject and body. Then I would like for it to send and display a message that the email was sent.

I am using Office 2003

Any help would be appreciated.
 

Answer:Solved: Macro to Send Email with Spreadsheet as attachment

7 more replies
Relevance 76.26%

Hello, I am working on a spreadsheet to help employees quickly calculate tank volumes. In short, we keep track of tank volume by height in 16ths of an inch. I have 10 different worksheets containing the height and volume data of the 10 tanks. The sheets are named by tank number. So for example, tank 2301 is in worksheet "2301". In another reference sheet I plan to hide, In B column have the heights in 16ths of an inch. In C column I have the volume associated with that height. I do have a Vlookup formula that works right now but I would like to use one master sheet and need to be able to enter different tank numbers in a selected cell and have the formula reference the data sheet based on the tank number we enter. Each tanks volumes are different which is why this is needed. This is the formula I currently use and it works! Just not sure how to alter it so I can have a cells value determine the reference worksheet in the vlookup formula.

=VLOOKUP($B$3,'2301'!$B$2:$C$519,2)

Id like the 2301 value to change based on what tank number we type in which will also be the data sheets name to be referenced. Maybe adding a drop down selection would even be a better idea than typing the number in.

Can this be done?

Thanks in advance for any info!

Joe
 

Answer:Excel Vlookup formula based on cell data

Welcome to the forum

Hmmm that's a tricky one. So you want the table array to be dependent on the data you type in? I think you can use the INDIRECT function for that (with the function pointing at the data input cell), but you would need to give defined names to all the relevant ranges. For example, you could name the range '2301'!$B$2:$C$519 to something like Tank_2301.

You could use Data Validation to create a drop down list to select the right tank.

How does that sound?
 

8 more replies
Relevance 76.26%

A user has a spreadsheet that tracks the current miles on the vehicle, has another column that shows when preventative maintenance needs to be done.What they want is to turn the current miles cell red when it is 90% to needing the preventative maintenance so it's easy to pick out of the list.I know I need to use Conditional Formatting, Highlight Cells Rule and then More Rules in Excel 2010 and also that I should choose "Use a formula to determine which cells to format" but am not sure what formula to use. Please provide examples for the formula.Here is an example of the mileage information:Current mileage 50,959 Preventative maintenance due at 51,115Thank you in advance for your assistance

Answer:excel 2010 change color of a cell based on th

Try this:
A B
1) Current Milage Maintenance Due
2) 46003 51115
3) 50959 51115

Conditional Format Column A using the formula: =IF((A1/C1)*100>=90,TRUE,FALSE)Cell A2 should remain your default color (White)Cell A3 should turn RedMIKEhttp://www.skeptic.com/

7 more replies
Relevance 76.26%

I have some excel knowledge but am not too proficient in VBA. I am looking to generate emails based on the any of the conditions I have specified below for column G "Current Approved to Date"

Conditions:
1. =AND(($G9<Today()),($I9=""))
2. =AND(((J9-G9>3)),($I9="Applied for Ext."))
3. =AND((J9>G9),($I9="Active"))

If any of these conditions are met I would like an email to be generated.
(SIMILAR VBA) https://forums.techguy.org/threads/solved-automatic-email-alerts-using-excel.710581/page-2
 

Answer:Excel Generate emails based on conditions of cell.

10 more replies
Relevance 76.26%

I want to create a new column of data pulling data from one cell in a row. The problem is the particular column in that row is variable. I've created a MATCH and a new column with a number that corresponds to a cell number that ranges from 12 to 61 (or N to BJ). So, for example, I have a something like this.Column A: values 12, 12, 18, 19, 19, 25, 61Columns B through BJ have various data (numerical and text, although if its important its numerical data I am collecting)Result:BK1 = N1BK2 = N2BK3 = T3BK4 = U4BK5 = U5BK6 = AA6BK7 = BJ7THANKS!

Answer:Excel - pull data from a cell based on MATCH

Hi,You said I want to create a new column of data pulling data from one cell in a row. The problem is the particular column in that row is variableThis is confusing - you want to pull one cell in a row -i.e the cell is somewhere on a specific row.but you say that the 'particular' column in that row is variable.Does this mean that you want to create a row based on a value in a cell in a row - but the row is variable - If so what is the data or condition that allows you to select that column. You say that the column is variable but there must be a specific way to identify the column.Then you say:I've created a MATCH and a new column with a number that corresponds to a cell number that ranges from 12 to 61 (or N to BJ)Do you mean 12 to 61 are row numbers or are they columns N to BJ (which are columns 14 to 62)You need to post the MATCH() function you are using, as your description of 'creating' a MATCH function and a new column is, to say the least, not intelligible - there is no way I could recreate your formula from this description.How have you / your formulas 'created' a new column that corresponds to a cell number (does number mean cell row - or cell column and if it's a single number - where does the column [or row] value come from).You then refer to Columns B through BJ - how do these relate to N to BJ or 12 to 61Finally you show a series of values in column BK:Is column BK your 'new' column and if so what is the formula / calculation that creates the relationship to ce... Read more

2 more replies
Relevance 76.26%

Here's an example of similar data:Name Count Column3 Column4A 2 50 51B 1 40 41C 0 1 2D 3 52 55What macro could I create that would produce this data?Name Count Column3 Column4A 2 50 51A 2 50 51B 1 40 41D 3 52 55D 3 52 55D 3 52 55Put into words, what macro could I create that would repeat each row the number of times contained in Column B?

Answer:How can I copy rows based on cell values in Excel?

Sub CopyByNumber()
'Determine last Row with data in Column A
lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Loop throught rows in reverse order
For srcRw = lastRw To 2 Step -1
'Determine how many rows to copy and insert
copyNum = Cells(srcRw, 2) - 1
'If Column B was 0, delete the Row
If copyNum = -1 Then
Rows(srcRw).EntireRow.Delete
End If
'If Column B was not 0, Copy and insert
For copyRw = 1 To copyNum
Rows(srcRw).Copy
Rows(srcRw).Insert
Next
Next
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 76.26%

Any Excel gurus who can help on this?Working on a spreadsheet with lots of names in, and need to get cells to change colour/font characteristics depending on contents (which name is picked from drop down list)Conditional formatting would be OK, except there are too many names....Is there any way to do this using an "IF" formula for example?All help appreciated!

Answer:Excel text colour based on cell contents

I think that you would need a macro like the following to do this:Sub ColorThem() Dim Ndx As Long For Ndx = 1 To ActiveSheet.UsedRange.Rows.Count If LCase(Cells(Ndx, 1).Value) = "account" Then Rows(Ndx).Interior.ColorIndex = 3 End If Next NdxEnd Sub

4 more replies
Relevance 76.26%

Hello,I have a workbook in excel with a number of different worksheets. I would like a way to set it so that when a user prints the workbook it only prints specific pages based on whether a specific cell in each worksheet has content or not. Here are the names of each of the worksheets along with the cell that is of importance:Main - I never want this to printNew AX43Cont_Sheet B3Orange AX43O2 AX43Vodafone AX43T-Mobile AX43Three AX43So, for example, if the cell AX43 on the worksheet named "Orange" has something in it, then when the user presses print, I want worksheet "Orange" to print. Same for all the other worksheets where the specified cell above is full. Any worksheets where the specific cell is empty, then I want that worksheet omitted when printing the workbook.Is this possible?Many thanks in advanceAmy.

Answer:Excel:Print specific pg based on cell content

Try this:Press Alt-F11 to open the VBA editor.Double Click the ThisWorkbook module in the left hand column.Paste this code in the pane that opens. It will run automatically each time the user prints the workbookRead the comments to see what it does.Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Disable events while code runs
Application.EnableEvents = False
'Loop through sheets
For shts = 1 To Sheets.Count
'Read Sheet Name
Select Case Sheets(shts).Name
'If Name = Main, Don't Print it
Case "Main"
Cancel = True
'If Name = Cont_Sheet, Check B3, print if not empty
Case "Cont_Sheet"
If Sheets(shts).Range("B3") <> "" Then _
Sheets(shts).PrintOut
'All other sheets, check AX43, print if not empty
Case Else
If Sheets(shts).Range("AX43") <> "" Then _
Sheets(shts).PrintOut
End Select
Next
Application.EnableEvents = True
End Sub

6 more replies
Relevance 76.26%

Hi all, long time reader, first time poster. I searched around and have found similar queries to what I require however I haven't been able to adapt these solutions with any success so here we go....

I have a list of businesses in a spreadsheet with information such as name, phone number, e-mail, website, postcode etc. I would like a search box on the side, say in cell O2 where a user can input part or all of a postcode e.g. (LS19 or LS) and those that do not begin with LS would be hidden. The postcodes are in column G.

I'm trying to make this as simple and user-friendly as possible so employees can find businesses in a certain area with ease. Thanks in advance for any help and please feel free to ask questions.

Chris Needham
 

Answer:Excel VBA : Hide rows based on cell input.

Seems nobody had the answer so I found a work around myself. Just in case anybody else wants to do a similar thing. Using auto filter is the easiest way.
Code:

[SIZE="3"]Sub Search()
Columns("G:G").Select
Selection.AutoFilter
ActiveSheet.Range("$G$1:$G$999").AutoFilter Field:=1, Criteria1:="*" & Range("O2").text & "*", _
Operator:=xlAnd
End Sub[/SIZE]

Where G is the column to search for and O2 is the users input data.

Guess this can be closed now, thankyou all for your help.
 

1 more replies
Relevance 76.26%

Hey all,

I'm new and have no experience with Excel vba coding. Hopefully someone here can help me out.

My excel sheet keeps a list of Email addresses on column B (with duplicate email addresses), and their particulars from column C (Item price, purchase date, etc) onwards.

I need the vba to email multiple recipients (those with the "notification" field marked as yes) with their purchasing details in it. It should also prevent multiple emails to the same email address.

Thanks in advance,
Leon

PS.
Using Excel/Outlook 2010
 

Answer:Emailing multiple recipients from Excel Based off Cell Value

9 more replies
Relevance 76.26%

I want a cell to turn red if i input S or Sam or Spm in it, can anyone help please
 

Answer:Excel - Change cell colour based on text

Welcome to the forums . You can use a formula conditional on the Cell. Right click the cell > Format > Conditional Formatting and then use the following formula (i.e. Cell A1) and select Red:

=if(or(A1="S",A1="Sam",A1="Spm"),1,0)
 

5 more replies
Relevance 76.26%

Hi guys,
I need to write a macro that is connected to many buttons, one for each row in spreadsheet, ask for a value to subtract, error check and (if ok) subtract from a corresponding value in column P.

When the button on a left hand side is clicked:
a dialog button should pop-up prompting a user to input the number of pieces to subtract from the corresponding cell (row based on the row of the button, column P)
error check the input number (show error if the value is smaller than 0 or larger than the corresponding value) ... I can write the error check by myself, so no need to do this step
subtract the number from the corresponding cell (i.e. user clicked a button located in cell A9, input 9,24 pieces in a dialog box and there are 15,97 pieces in P9 => the pieces in P9 should change to 6,73)
Thank you for helping me out!
Hope, the description is not very confusing.

Michal
 

Answer:Excel VBA: Subtract from a corresponding cell based on button placement

9 more replies
Relevance 75.85%

Hi,

I have a large excel worksheet, divided into 4 rows (different data fields). Hard luck, in one of the rows, some cells contain two products or more instead of one (ex: "ZB3054066¦¦ZB3601207¦¦ZB3601206"). They are separated either by a blank or by a ¦¦ (double bar).
I need that each time I detect a cell in that particular row that contains multiple products, I create new rows so that each row contains only one product in the column "component parts" and that the original data in the other columns remains unchanged.
I don't know anything about excel macro, but here is the approximate code I created to do this task :
If current row cell "component parts" contains a separator (either or blank),
{
create "number of separators" new rows
copy every field except the "component parts" one
while( cell "component parts" contains a separator)
{
cut the part of the "component parts" cell before the first separator (including the separator)
paste it into the next row "component part" cell
delete the separator in the new "component parts" cell
}
}

If anyone knows how I could do this in an excel macro...
Thanks.
 

Answer:Excel macro : cell with multiple content

16 more replies
Relevance 75.85%

Hi again

I'm doing a complicated macro that seems to run for about 5 minutes. I believe I can shorten it if only I knew how to do the following.

My macro starts at Cell A2 and scrolls down column A until a certain condition is met. The condition is that the active cell <> cell D1. The macro then stops at the active cell in column A and then copies the active cell in cell D1. More code goes here. The program then goes back to the top of column A to Cell A2 and scrolls down again until the active cell <> cell D1.

Is there a way that I can continue where I have left off in Column A instead of going back to Cell A2 and scrolling down again? Here is a concrete example. Copy A3 to D1. Code goes here. Loop until activeCell <> D1. The cursor stops at A10. A10 is now copied to D1 and the process starts all over again. The macro starts back at A2 and scrolls down. Is there a way that the macro can put the cursor back at A10, where the copy started from??

The next spot the cursor stops at is at cell A16 and then copies A16 to D1. I now want the cursor to go back to A16 instead of A2.

I hope that you understand what I'm talking about.

Mario
 

Answer:Do a macro in excel that return to a prevoius cell.

10 more replies
Relevance 75.85%

Hi,I have a list of prices. I want a macro that when a button is pushed, the data will go from one cell to another. However, I have 10 rows at the top of the page. I want to price to go into the last blank cell of those ten rows....that is if a price was already in a cell, it would fill the new data into the one below it.I know it has to be some sort of "if" "then" code, but I am in way over my head!Please help!~Deb

Answer:Excel Macro If a cell is blank, how to paste?

re: ...when a button is pushed, the data will go from one cell to another.errr...what data?You need to be a lot more specific before we can help.Please post an example of your layout, after reading the How To referenced in the next line.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 75.85%

In the "Checkout" tab, I am wanting to copy rows if/when a cell (column "B") in that row is greater than zero. I need this macro to go to the next row and the next row and the next row until the cell in column "B" is blank. Once the selection has been copied, I need it to pastespecial only the values and not fomulas in the "Database" tab.

I have attached an example spreadsheet.

Any and all help is greatly appreciated.
 

Answer:Excel Macro to copy row if cell is greater than zero

This is a duplicate of your other post
 

1 more replies
Relevance 75.85%

Microsoft Excel MS Excel 2003

I would like to pull information from worksheet cells (specifically file path and name) to use in an auto open macro in order to provide a workaround on the limitation of the INDIRECT function's inability to access data in closed worksheets.

How do I create a reference in the Visual Basic macro to pul the data from the worksheet?
 

Answer:MS Excel Worksheet Cell info to be used in Macro

10 more replies
Relevance 75.85%

Hello, I need help with a print macro. I have a file with multiple tabs (each tab having unique information) including a data entry tab. On the data entry tab, I want to put a button that will allow me to print a specific number of pages from each tab based on a specific cell value on that tab. For example: Cell M1 on each tab will contain a number. That number represents the number of pages to print. Tab 1: M1 = 2 Tab 2: M1 = 5 Tab 3: M1 = 3 The print button should then print 2 pages of the data on tab 1, 5 pages of the data on tab 2, and 3 pages of the data on tab 3. Can anyone help me with the macro needed to do this? Thank you,

Answer:Print Qty of Excel Sheets = cell value macro

How are you defining what a "page of data" is?Are you letting Excel figure out where to put the page breaks or are you printing specific number of rows or something else?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 75.85%

Hi,I have an excel spreadsheet in Office 2003. Column A is an alert column. Columns K, U, X, AE, AO, AR and AU have conditional formatting that, dependant on dates entered, change the cell color to either green, amber or red.What I'm after is a way to change column A color for each row, dependant on the other row colors, in a priority sense. Red is highest, then amber then green. So if any of the cells listed above is red in a row, then column A for that row would be red, regardless of the color of any other cells. If none are red in the row, it would then look for amber, then green. You get the drift?My excel and VBA skills are not such that I can accomplish this. Any help would be greatly appreciated.

Answer:Excel cell color formatting macro

Why can't you use the same Conditional Formatting rules that turn the other cells in the Row the various colors and apply them to Column A? Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

7 more replies
Relevance 75.85%

(I am unsure if the first time i posted this worked or not, but here we go again.)

I need to remove some data from cells, preserve numerical values, and add them together.

The record format is this EntityA(#records). In most cells, There is not only an Entity A, but B-H as well. I am trying to strip out the entity name information, retain the numerical value in the parentheses, and then add them together cell by cell, row by row.

I have attached a sample of the sheet.

Im not looking for anything too fancy, I've just got about 1000 rows of these and need a total count of (#records) that have been received. So in the sample, it doesn't matter that A3 = 32 total, I'm just in need of a folmula, filter, or macro that will tell me that A3-A9= 41 total. (If the "n/a" or "~" values are problematic they can just be blank.)

Can anyone help with this?
 

Answer:Solved: Excel Cell Macro/Filter

10 more replies
Relevance 75.85%

Hi,
I'm relatively new to VBA, so excuse me if this is a dumb question. I have a workbook with a tab that users enter different variables into. One cell is meant to input a range that later on will be used to perform selections on other tabs. I can't seem to set my range to the text in the input cell. An abbreviated form of the macro I'm trying to use is this:

Sub Macro1()
Dim RetrieveRange As range

Set RetrieveRange = range(Worksheets("Admin").range("B1").Text)

MsgBox RetrieveRange.Address

End Sub

I keep getting this error: "Run-time error '1004': Method 'Range' of object '_Global' failed"

Can anyone help?

Thanks!
 

Answer:Excel Macro - Set Range to Text in Cell

I pasted your code as a macro, created an Admin tab, and entered a valid range (A1:A2) and it worked for me. I entered an invalid range ('before') and got the error.
 

3 more replies
Relevance 75.85%

I want a macro to solve the below problems please:Sample:ABCT*DEFG*AB*Results:A D AB E BC F T G So copy row 1 and when it reach to *, then change another column. The height is uneven so the only splitter is *Many thanks for your help.

Answer:Excel Macro Change Column when Cell = *

re: So copy row 1 and when it reach to *...I assume you mean "So copy column 1 and when it reach to *..."With your data starting in A1, this code will split it, starting in B1:
Sub SplitData()
'Determine last Row with data in Column A
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Initialize Column number
colNum = 2
'Loop through data
For myList = 1 To lastRow
'When we hit an "*"...
If Range("A" & myList) = "*" Then
'Increment loop counter so we don't use the "*"
myList = myList + 1
'Increment the Column number
colNum = colNum + 1
'Reset the Row number
rowNum = 0
End If
'Increment the Row number
rowNum = rowNum + 1
'Place the data in the proper cell
Cells(rowNum, colNum) = Cells(myList, 1)
Next
End Sub

2 more replies
Relevance 75.85%

I'm creating a macro to paste data from one worksheet into another and I need to find the last unmodified cell in a column to copy the date. The last cell will be modified by having the fill color changed. All cells with data have the fill color modified although not all cells with fill color have data.

I would use something like
While Cells("i,j") <> "",
but not all cells have data.

I need some VB method which can look at a cell and determine if it has No Fill or is filled with a color. Then I will just search for the first cell in a column which has "No Fill" and that will be the end of the data.

Hope this makes sense and thanks in advance for any help you can provide.
 

Answer:(ttt) Excel Macro finding last unmodified cell

7 more replies
Relevance 75.85%

hi

my macro tells cell A2 on sheet1 to equate to cell D10 on sheet2. D10 on sheet2 is a total from the same sheet (sheet2) and sometimes can move to D8, D9 or D11. how do i incorporate the moving target cell into my macro?

tim
 

Answer:Excel macro, moving target cell

6 more replies
Relevance 75.85%

I am trying to use part of a macro to change the active cell to a cell address determined from data entered by the user. For example, a user enters a date, the date is compared to a list of dates and when a match is found the cell address is returned. I want to then make the returned address the active cell. Can this be done and if so how?
 

Answer:MS Excel - Changing the Active Cell Macro

You would need to use VB code. I don't think you can record a macro to do that...do you already have a way to find the matching date or do you need help on that part too?
 

3 more replies
Relevance 75.85%

I am trying to write a macro that compares cells in column A from sheet1 to cells from column A in sheet2 and if the values from sheet2 are not in sheet1 then I want to delete the entire row from Column A in sheet2.

for example

In sheet1, column A has a list of id no's (no duplicates) and in sheet2 Column A has list of id no's (with duplicates). I want to delete every row in Sheet2 Column A that is not in Sheet1 Column A.

Keep in mind that both sheets have several columns.
Also there is about 5000 rows in sheet 2

Any help would be greatly appreciated.

Thanks
 

Answer:Excel HELP macro to Delete Row Based 2 columns from 2 sheets

Welcome to the board.

While I understand each part of your post (I think), some parts seem to contradict others. So here's what I did.

In Sheet1!A1:A5 I entered 1 -- 2 -- 3 -- 4 -- 5.

In Sheet1!A1:A6 I entered 1 -- 2 -- 6 -- 3 -- 4 -- 5.

Then I wrote some code that removes the third row from Sheet2, since it contains 6 in column A which is not in col A of Sheet1.

How the code works is:

(i) inserts a new column A on Sheet2

(ii) enters a formula in new column A Sheet2, e.g.:

=MATCH(B1,Sheet1!A:A,0)

which returns a number if there's a match in column A Sheet1, #N/A if not

(iii) deletes any rows on Sheet2 with #N/A in column A

(iv) deletes column A on Sheet2 (redundant).

Obviously if this isn't what you need don't use it, post more info instead.

HTH

Sub test()
Sheets("Sheet2").Range("A1").EntireColumn.Insert
x = Rows.Count
y = Sheets("Sheet2").Range("B" & x).End(xlUp).Row
Sheets("Sheet2").Range("A1").Resize.FormulaR1C1 = "=MATCH(RC[1],Sheet1!C,0)"
Sheets("Sheet2").Range("A1").Resize.SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
Sheets("Sheet2").Columns(1).Delete
End Sub
 

1 more replies
Relevance 75.85%

I need to create a macro that will search column A for a text string, and then insert the data in cell A1 in column C of the same row.Basically, search column A for text "String10", if found, insert contents of cell A1 in column C of the same row.Any help would be greatly appreciated!

Answer:MS Excel Macro - insert text based on search

Hi,Following is the subroutine code for your query:'==============================Sub findAndWrite()Range("a1").SelectDo Until ActiveCell.Value = ""If ActiveCell.Value = "String10" ThenCells(ActiveCell.Row, 3).Value = Range("a1").Value 'comment:you write in the cell with C column of the same rowEnd IfActiveCell.Offset(1, 0).Select 'comment:you go to the next cellLoopEnd Sub'=======================Be Happy and make others Happyviswam

10 more replies
Relevance 75.44%

Dear All,

I am creating a tracker file for Purchase Orders in which end date in column 'J' is very important.
Column 'J' has following conditional formatting
1. If cell date is in between todays date + 14 days --- YELLOW
2. cell date <= today's date ------------------------- RED
3. cell valus > today + 14 days ---------------------- GREEN

So now when the cell turn Yellow I want the excel should send the mail to the concerned person whose mail id will be mentioned in same row of column 'G'

One more requirement there should be two mails. Reminder 1 & Reminder 2.

I use officer 2010 & mail is outlook 2010 & OS is windows 7.
This file will be on share point. This file may not be opened everyday.

Please reply with the procedure as I am not a programming/ IT person... I may not understand all terms.. please try to simplify the response.
Thanks in advance for all the help.
 

Answer:Auto mail from Excel to Outlook based on date in cell

Hi there, welcome to the board!

You'd want a location to mark when an email was sent. I'm assuming you want a worksheet change event for this, which will basically always run when a cell on this specific worksheet is changed. There are other events you could use to fire it off, like the calculate event. You could, if you wanted to, assign this functionality to a button, but then it wouldn't be automatic.

The below code does what you ask. It goes in the worksheet module of the worksheet your data is on. To get to it, right click the sheet tab name and select 'View Code', then paste this there.

EDIT: The locations to mark when an email was sent (btw) were columns K and L, as you'll see them set as constants at the top of the code. You can change those letters to any column you want to house it in. It just puts the current system date in those cells, and that will be checked when the values in column J are checked. If nothing is there it assumes an email hasn't been generated yet and will do so. But if it has a value - any value, it will ignore that row.

Also, I assumed a 'yellow' highlighted value was your first reminder, and a 'red' highlighted value was your second reminder. It uses this as text in the subject and body of the email.
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Const ReminderOne As String = "K" 'column letter
Const ReminderTwo As S... Read more

1 more replies
Relevance 75.44%

I have an excel sheet with two tables for customers to know which products the customer is using based on a Contract table.

The enclose sheet has two tables:
1. Customer
2. Contracts

All what I need is to fill the columns of "Product A", "Product B" and "Product C" of every customer looking at the table "Contracts" where the data is available based on the "Customer ID"

How can I do it?
 

Answer:Solved: MS Excel / Filling a cell with YES based on data from another sheet

I would just use a simple countifs formula to check if the customer ID and Product type match (ie the count of matches is greater than zero)
See attached.
 

3 more replies
Relevance 75.44%

I am using Excel 2010

I want to limit a validation list based on the contents of Cell D4
There are 4 lists possible based on the contents of D4:

If cell is 1 = list (on datasheet tab) should be limited to range A42:A71 only

If cell is 2 = list (on datasheet tab) should be limited to range A72:A87 only

If cell is 3 = list (on datasheet tab) should be limited to range A88:A90 only

If cell is 4 = list (on datasheet tab) should be limited to range A92:A93 only
Thanks
 

Answer:Excel 2010: Limit validation list based on another cell

Soooo... is this possible or no?
 

3 more replies
Relevance 75.44%

i have a spreadsheet that updates with data from a number of different sources. i need to filter only the relevant data that i need. i'm almost there but need to find out how to pull 2 more pieces.

in the attached spreadsheet the cells i need are in column R and T. I need to know the corresponding "name" and "size" of the data that is in column S.

unfortnuately the actual values don't show up in the spreadsheet b/c they are live data feeds but the formulas written up until now are still there.

in a nutshell what i'm doing is getting 5 price feeds and i need to filter out which is the highest updated within the last 2 minutes. i actually have the only the price figured out in column S, but i also now which name and how big the size is related to that price.

your help is very appreciated.

regards,
 

Answer:Solved: excel - data returned based on the value of a specific cell

10 more replies
Relevance 75.44%

Hi folks, not sure if this is possible or not but I'll try to explain what I'd like to do.

On the first sheet, I have a simple sheet to be filled in daily. The date is changed, and then daily pick figures and hours picked are updated by the relevant department.

I then have to manually put all of the data into the second sheet, which acts as a log of previous pick figures.

Is there a way the second sheet can automatically be filled in when the date is changed and new figures are put into sheet 1?

I hope I've explained this well enough, I'm trying to teach myself excel as I'm going along! Thanks.

Edit - I've had to delete peoples names' for obvious reasons, initials in sheet 2 will correspond to people in sheet 1.
 

More replies
Relevance 75.44%

Hi all,

Complete newbie here, have been reading various posts and trying to figure things out on my own but to no avail. Maybe someone can shed some light on my problem.

I'm using Excel 2004 (Mac) and want to create a VBA/macro that would change the background colour of specific cells (say A4, A5, A6 and C8) based on the number OR letter in the cell. There is a drop-down from which the user must select either 0,1,2,3,4,5 or H for these cells. I want the colour to change based on the user's selection as follows:

0 or 1 = Red (i.e. colour value 3)
2 or 3 = Yellow (i.e. colour value 6)
4 or 5 = Green (i.e. colour value 4)
H = Lavender (i.e. colour value 7)

I've found sample event code showing how to change the cell colour based on a numerical value/range, as well as different sample code showing how to change the colour based on the letter/text, but not *both in one*. (I've found out how to to insert the code via View Code when clicking the worksheet tab but am unfortunately not savvy enough to know how to set up the code correctly).

I can't imagine it would be too complex for someone with a decent level of VBA/macro knowledge. BTW, I would be using conditional formatting to do this except it only allows for 3 conditions/colours, whereas I need 4! (thanks MicroSoft...)

Any suggestions on how to do this would be very much appreciated!
 

Answer:How to change cell colour in Excel based on number *or* letter?!?

Technically, you can still use conditional formatting in this case - you have 4 cases and 3 max options. So just make the default background Lavender, then then use the 3 conditional cases to change it to Red/Yellow/Green. As it's a dropdown, this shouldn't break anything and selecting H will leave the default lavender colour. Not ideal, but perhaps a simple macro-free option.

If you want to use VBA then you can use code like this:

http://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm

This page explains how the Case statement works for number and letter selections (near the bottom):

http://www.ozgrid.com/VBA/select-case.htm

Hope that helps
 

6 more replies
Relevance 75.44%

Hi - I have a cell that is calculated based on 2 other cells=IF(F2="","",TEXT(F2-C2,"h:mm"))What I am trying to do is get the cell G2 to change colour if the time is => 4 hours. I have read for hours but everything suggested does not appear to workDoes anyone have any ideas of how I can do this?

Answer:Excel 2007 conditional format cell based on time value

I am assuming that the formula you posted is in G2.I am also assuming that you have entered a time value in F2 and C2.If that's the case, then this CF Rule on G2 should work:=HOUR(G2)>=4Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 75.44%

I would like to know a method to solve an otherwise repetative task in Excel for me.

In my Excel spreadsheet, I have many columns relating to customer orders etc. Each customer (finite number (~40)) has its name (customer1, and customer2 etc) input to a cell, I then must fill out the customer account number (ac001, ac002 etc). The customer name and number already exist on another sheet.

How can I automate the process of when I input the customer name, excel then fills in the customer number in the next cell?

Many thanks
Dave
 

Answer:Excel: Conditional Cell Input Based on Entered Data

Depending on how your Customer data is arranged, look at either Dlookup or Vlookup.
But Access is better for Customer databases.
 

3 more replies
Relevance 75.03%

Is it possible for an email to automatically be sent when the word "Alert" shows up in column "L". The word alert would be generated by a formula. I need the email to be sent to 3 different people. I would like a notification email that simple says "please check log for alerts". i would appreciate any help.
Thanks Sue

XP-excel 2003
 

Answer:Running a email macro when a condition in a cell changes

1. Enter =IF(A1=1,"Alert",0) in L1 in a fresh workbook, then copy to L2 & L3.

2. Rightclick the sheet tab, select "View Code" from the pop-up menu.

3. Paste this into the worksheet code module:

Private Sub Worksheet_Calculate()
If WorksheetFunction.CountIf(Columns(12), "Alert") = 0 Then Exit Sub

MsgBox "!"

End Sub

4. Press ALT+Q to exit the VBE & return to Excel.

Now anytime you enter 1 in A1, A2 or A3, the message box will display. So that's a way to set it up for the "email prep & send" process.

So: what's your email programme? (welcome to the board)
 

1 more replies
Relevance 75.03%

Hi there guys

Looking for help in excel 2007 for the macro part.

Basically in sheet1(BOM), we have configurations which our customer order to us (in the form of CP1,CP2..CPn). In Sheet2 (CP) details about CP are mentioned, basically each CP has list of items under it. Typical order from customer includes combination i.e. CP1&CP2 or could be CP2&CP3. What I want to do is based on the order in BOM sheet, need to search the details of CP's in sheet2 (CP) & copy the rows belong to that particular CP in the BOM sheet. So that I can make the BOM list for that particular order. Each CP has variable number of rows. Appreciate your help in making macro for this.

Rgds, MintC
 

Answer:Excel Macro to copy multiple rows based on selection

11 more replies
Relevance 75.03%

Dear All,

Can somebody help me out in generating a macro for forming a "n x n" matrix based on user inputs. - Excel sheet enclosed.
Inputs:

i j Value

1 2 1
1 3 1
1 4 -1
1 8 -1
2 1 1
2 4 1
2 5 -1
2 6 -1
3 5 1
3 6 1
3 7 -1
4 1 130.1459226
4 4 -160.8272092
4 2 -144.5355421
5 2 144.5355421
5 3 -183.6928125
6 2 144.5355421
6 4 160.8272092
6 5 137.3575476
6 7 152.8315505
7 2 144.5355421
7 4 160.8272092
7 6 137.3575476
7 7 152.8315505
8 3 183.6928125
8 8 172.7183057

In remaining locations, values are zero.

i j Value
1 1 0
1 5 0 etc.,

Here, we have 8x8 matrix. But, this should be generalized for forming "n x n" matrix
Please get me an Excel Macro for this.
Thanks in advance,
Prakash
 

Answer:VBA Excel Macro for 'n x n' Matrix formation based on user inputs??

10 more replies
Relevance 75.03%

Hi All,

Can someone help me out in generating Excel macro for Excel sheet enclosed.

Algorithm and conditions ve been explained in the sheet.

User inputs:

Element Left Node Con #1 Right Node Con #2
1 1 5
2 2 4
3 3 4
4 4 5
5 5 6
6 5 6
7 6 7
8 4 8
9 7 8
10 1 2
11 2 3

Outputs:
Loop1 1 5 4 2 1
Loop2 2 4 3 2
Loop3 5 6 5
Loop4 4 8 7 6 5 4

Conditions:
1. Right node of element 1 should be compared with right nodes of all other elements - if matches, get the left node of that element
2. Now, this left node is compared with right nodes of all other elements - if matches, get the left node of that element and this is continued until left node of the current element matches with left node of element 1.

thanks in advance..
 

More replies
Relevance 75.03%

Hi,New to VB and would appreciate some help with the following.I am trying to create a sheet where by i can input my capacity/demand on any new day. However, i want to be able to store the historic data and be able to bring it up again by inputting a date for a specific days data that i wish to view.Lets say that A1 is the cell where a date can be inputted.B1 is where i input todays capacityC1 is where i input todays demandD1 is where i input expected WIPWhen i open up the document tomorrow, i would love these fields to be blank ready to be populated. However, i would like to be able to view yesterdays data by inputting yesterdays date in A1. If the date is not a valid date i.e no data was recorded for that day, a message box could pop up saying 'not a valid date'.Can anyone help?

Answer:Excel: Macro that will display data based on a inputted date

I believe that these 2 sets of code will do what you asked for. However, there is one big piece missing. There is nothing here that will add the current date's new data to the historical data.Since there are so many variables involved with inputting the data, I didn't want to waste time writing code that won't do what you want.For example, when do you want the data copied to the historical data - as soon as the last piece of data is entered into D1? Just before the workbook is closed? Just before the workbook is saved? Do you want to click a button to save today's data? etc.Let's say you enter today's data and then decide to search for another day's data. Would you want to be able to pull today's data back again during that same session?Would you want to be able to change today's data assuming it's still "today"?Until it's clear how you want to deal with the new (current day's) data, it's tough to offer any suggestions.As far as your other 2 requirements, try this in a backup copy of your workbook. This code writes data and deletes data, so you don't want to test this your only copy of your workbook since it cannot be undone.Note: This code makes 3 assumptions:1 - The data entry cells you mentioned in your post are correct, i.e. A1:D1.2 - The data entry cells are on Sheet 1.3 - The historical data is stored in Sheet 2 Columns A:D.You will have to modify the code if it doesn't fit your workbook layout.Right click on the sheet tab for Sheet 1.Paste this code into the pane that... Read more

5 more replies
Relevance 75.03%

I hope someone can help me with some VBA code. Sheet1 has 500+ rows and 30+ columns of data, sorted by text in column G. I want to create a data input sheet to manually key in data. I need help to create a macro to cut and insert the row into Sheet1 - the first row after it finds a match in column G. Thanks
 

More replies
Relevance 75.03%

Hi,
I have an employee list excel file (vacation) including the details about empno, emp name, last vacation, next vacation, etc in sheet 1.
Now, I would like to write a macro where I will enter the name of the month in a dailog box and the macro should display the list as in sheet 2 of the attached file.

Windows 7 with MS Excel 7.

Kindly help me, its urgent.
 

Answer:Solved: Excel Macro to display data based on month

I've made a quick macro that does as you requested. It is not "smart" at all so if you change where the data is located (by adding a column for example) it will no longer work. It will handle more rows of data no problem. See the attached.
 

2 more replies
Relevance 75.03%

I have been reading up on creating log files with Excel Macros.

I am trying to figure out if it is possible to have a Macro output the contents of the currently selected Cell to a text file?
 

Answer:Solved: Excel Macro Copy Cell to log file.

16 more replies
Relevance 75.03%

update on the below question:I think something is wrong when I opened the .csv file.If I leave the file open then run the macro, it works ok. If I open the file within the macro, I get the format problem.Any advice?Thanks!Hi,

Please can someone give me some advice on this?

I am writing a vba code in Excel 2003 to copy from a source .csv file and paste into xls file.

First, I tried this using macro recording, it worked fine. so I recorded the macro and tried to apply it. Something strange happened.

One of the columns is date, and originally is format Date (*01/01/01). If I manually copy and paste, the format is fine. If I use the macro, some (and only some) of the cells in this column will change format to General and align to the right, while the others keep the original Date format and align to the left.

How did this happen?

I viewed the .csv file using Notepad, and cannot see any difference between these dates that end up in different format.
And if I manually copy/paste, there will be no format issue at all.

Please can someone help?

Thanks!
 

Answer:copy/paste in excel macro changes cell format

upon a closer look, i think I found some clue, but still far from a solution, here is what I observed.

for date 09/07/2006, excel interpret it as Sept 07 06
for date 20/06/2012, excel interpret it as June 20 2012

please note the year format is different!

any advice pls?
 

1 more replies
Relevance 75.03%

I'm trying to write a macro for Excel 2010 to count cells only if the background fill color is a certain color. I'm having trouble finding how to access the background color information in Excel 2010.
 

Answer:Excel 2010 - Macro to count if cell is a certain color

6 more replies