Over 1 million tech questions and answers.

VBA Deleting row based on cell value

Q: VBA Deleting row based on cell value

I have a fairly simple task. From Row 17 to row 51 and Row 96 to 163 , I want to delete the row if cells P and Q are empty.

From row 57 to row 94 I want to delete the row if cells B, C, P, and Q are empty

I'm not sure what I'm doing wrong here.

Sub CleanUp()

Dim endrow As Long
Dim x As Integer

Set endrow = Sheets("Work Order").Range("A17").End(xlUp).Row

For x = endrow To 17 Step -1
If Sheets("Work Order").Range("P" & x) = "" And Sheets("Work Order").Range("Q" & x) = "" Then Sheets("Work Order").Rows(x).EntireRow.delete
End If
Next x

Rows("167:180").Select
Selection.delete shift:=x1Up
End Sub
Click to expand...

If you see something glaringly wrong or know a quicker way to do this, I would be very thankful.

RELEVANCY SCORE 200
Preferred Solution: VBA Deleting row based on cell value

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.)

A: VBA Deleting row based on cell value

Here's an example work sheet. If the code works right in the example, it would delete rows 4, 5, 9, and 10.

Code:
Dim endrow As Long
Dim x As Long

For x = endrow To 29 Step -1
If Sheets("Sheet1").Range("P" & x).Value = "" And Sheets("Sheet1").Range("Q" & x).Value = "" Then
Sheets("Sheet1").Rows(x).EntireRow.Delete
End If

Next x
Why won't it remove the rows?

Read other 2 answers
RELEVANCY SCORE 82

You must be bored with this question cos I've seen lots of them, however I can't seem to fathom out what to do..........

I've got a block of data within a spreadsheet 4 rows always in the same place.

How do I put into code:-

If column d43:e46 is blank delete rows 43 - 46 if not ignore it.

As I said I've tried a few things and it will delete the four rows regardless of them being empty of not.

Can someone help please.

Many thanks,

Moll
 

A:Deleting rows based on empty cell.

Is there anybody there? - Help!
 

Read other 2 answers
RELEVANCY SCORE 68.4

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.

Read other answers
RELEVANCY SCORE 68.4

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
 

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

Read other 12 answers
RELEVANCY SCORE 68.4

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.

Read other answers
RELEVANCY SCORE 67.6

Hello,

I am attempting to clear 2 cells, based off the value of another. I am pretty sure the code is correct, because it works within another Macro. Thanks.
Code:
Sub REMOVE()


Dim p As Long
p = Cells(Rows.Count, "a").End(xlUp).Row


For i = 1 To p
Range("k2").Select
If InStr(1, Range("k" & p), "None") > 0 Then Range("L" & p) = "" And Range("M" & p) = ""
'If no Issue, Location/Obsevations should be blank
Next i


End Sub


 

A:Solved: Clearing Cell Contents Based off other Cell

hi
try this variation;
Sub REMOVE()

Dim p As Long
p = Cells(Rows.Count, "a").End(xlUp).Row

For i = 1 To p
If InStr(1, Range("k" & i), "None") > 0 Then
Range("L" & i) = ""
Range("M" & i) = ""
End If
'If no Issue, Location/Obsevations should be blank
Next i

End Sub
 

Read other 2 answers
RELEVANCY SCORE 58.4

I have a spread sheet where I would like a message to pop up after I enter a value in cell G2 based on what the value of I2 is. like the following description:
In cell I2 I have "=(E2*F2*G2)/144" after I enter a value in cell G2 I want a message to come up if the value of I2/g2 is less than three and not equal to zero" Is this possible? If so could someone help me out with this?

thanx
 

A:validation based on another cell

This is the same as the challenge file but I added the code for this question
 

Read other 1 answers
RELEVANCY SCORE 58

Hello everyone.

I am trying to tweak a macro that was posted on this forum some time back, but I'm a bit of a N00B. The macro is from this thread that is now locked: https://forums.techguy.org/threads/...ue-date-reminder-based-on-excel-file.1129238/. What I am looking to do is generate two different emails whenever the value of a cell changes. All the data that needs to be part of the email is included in various cells. I've included sample data. If possible, could you provide code for generating the email and another for sending the email without opening Outlook (i.e., as soon as the value changes)? I've included sample data for your consideration. Thanks in advance for any help you can provide.

When Cell Value = In Progress
The email I'm hoping to generate (I've mapped it with the cell contents I'm hoping to pull) will be as follows when Column F changes to In Progress. The email addresses are in Column G.

Subject Line: "Web Request(s) Now In Progress"

Dear D2,

Your C2 B2 request has been received and is now in progress. Thank you!

My Signature

When Cell Value = Completed
The email I'm hoping to generate (I've mapped it with the cell contents I'm hoping to pull) will be as follows when Column F changes to Completed. The email addresses are in Column G.

Subject Line: "Web Request(s) Completed"

Dear D2,

Your C2 B2 request is complete and the changes are now live. Thank you!

My Signature
 

Read other answers
RELEVANCY SCORE 57.2

i have 2 workbooks entitled [price] and [fruit] respectively

[price], sheet1!, cell A1 is a variable which can contains a cell address, eg $D$1

[fruit], sheet1!, contains information in cells as follows:
D1 = apples
D2 = bananas
D3 = cherries

in the [price] workbook, i want to have a formula in say, cell B1, which would use the cell reference in A1 to find the value in the [fruit] workbook.

so, if [price]sheet1!A1 = $D$1

then [price]sheet1!B1 = apples

note that the result "apples" was pulled from another workbook, [fruit]sheet1!$D$1.

if [price]sheet1!A1 = $D$2,
then B2 = bananas

and so on...
in the [price] workbook,

As A1 is a variable, i do not want to physically retype the value within A1, but rather draw reference to it inside of a formula. i hope this is clear as mud.

Is this possible?

Thanks,
markus
 

A:Q: draw value from a 2nd workbook, based on a variable cell ref in 1st wb

In Sheet1!B1 (of Price), use

=INDIRECT("[Fruit.xls]Sheet1!"&A1)

, where A1 contains the cell reference -- $D$1, $D$2, $D$whatever.

As explained yesterday, this (the INDIRECT function) will only work when both files are open ; to get it to work when the source file if closed, you need to download Laurent's add-in. Please do not start threads over.

Rgds,
Andy
 

Read other 1 answers
RELEVANCY SCORE 57.2

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.
 

A:Excel - Auto Email based on cell value

Read other 10 answers
RELEVANCY SCORE 57.2

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.
 

A: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
 

Read other 2 answers
RELEVANCY SCORE 57.2

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)
 

A:Solved: Emailing from Excel Based off Cell Value

Read other 16 answers
RELEVANCY SCORE 57.2

Does anyone know a way to manipulate the countif formula to count how many cells are "green" or "red" (with varying text in each cell), rather than the value of the cells?

Can this be done in VB?
 

A:Solved: countif based on cell format, not value?

Read other 10 answers
RELEVANCY SCORE 57.2

Hi guys,

Can you please help me further expand the macro below? I need it to also look between the ranges of C24 and Z24 and hide the respective column. (i.e need some sort of loop)

i.e

if C24 is 0, then hide column C
if D24 is 0, then hide column D
etc...
Sub Hide_Column ()

If Range("H24").Value = 0 Then
Columns("H").EntireColumn.Hidden = True
Else
Columns("H").EntireColumn.Hidden = False
End If

End Sub
 

A:Solved: Hide Columns based on value of cell

Read other 9 answers
RELEVANCY SCORE 57.2

Hello,

I am using Excel 2010 and trying to resolve an if statement... in the file attached I require the following:

I need 1 of 3 results for cell G2 based on the first 3 characters of cell B2

M15### would equal PAB
M22### would equal NSY
Non# would equal None

Thanks
 

A:Multiple IF Statement based on first 3 characters of cell

This was my attempt but not working:

=IF(LEFT(B2,3)="M15","PAB",if(LEFT(B2,3)="M22","NSY",if(B2,3)="Non","None")))
 

Read other 2 answers
RELEVANCY SCORE 57.2

What is the best way to change cell colors based on a cell color (Not a number)?

I have a condition where a cell may have green background (conditional formatting), and on the next update, a different cell may have the green background (same condition, but not the same cell). I would like several cells in that column to change to the same color when this happens, they will be different rows, but will be on the same row as the change occurs ... it is dynamic.
*********
Condition 1:

G8 background color is = green

Change adjoining cells to the right and down to the same color
(H8:K8 and G9:K12) The inclusive range would be (G8:K12)
********
Condition 2 on update:

G8 is back to normal but H8 is now green

Cells in column G are back to normal but adjoing cells to the right and down should now be green.
(I8:K8 and H9:K12) The inclusive range would be H8:K12)
*******
Here is an example of the conditions above.

Condition 1
Row G H I J K
8 20 22 33 32 35
9
10 24% 15% 22% 21% 25%
11 80 82 90 88 89
12 44% 45% 48% 47% 50%

Condition 2
Row
8 22 33 32 35 38
9
10 15% 22% 21% 25% 27%
11 82 90 88 89 90
12 45% 48% 47% 50% 52%

The cells I want to highlight will not be of the same value and there are formulae in every cell.

Thanks
I haven't got the hang of copying the cells from Excel to the post. sorry about the formatting.
 

A:Change Range Cell Color Based on Another

In (nearly) simplest terms, and assuming from what you say that there'll NEVER be a time when G8 AND H8 are BOTH green, then

If Range("G8").Interior.ColorIndex = 10 Then
Range("G8:K12").Interior.ColorIndex = 10
ElseIf Range("H8").Interior.ColorIndex = 10 Then
Range("H8:K12").Interior.ColorIndex = 10
End If

You'll have to get back to us on what sheet event might fire this. & let us know if the actual cond formatting (a) blocks the code and/or (b) remains intact.

Rgds,
Andy
 

Read other 3 answers
RELEVANCY SCORE 57.2

 The laptop immediately shuts off when I pull out the power connector.  I've tried the troubleshooting steps listed on HPs website with no change. Thanks.

Read other answers
RELEVANCY SCORE 57.2

I have a sheet with several hundred rows. Starting in Row 4 through the end of the sheet are mixed dates in column 1 and ALL rows from Row 4 through the end of the sheet hidden. I want to be able to type in a date in Cell A1 and have all rows that have that date in Column 1, Row 4 through the end of the sheet to become unhidden. Secondly, I would like to type some other code like "999" or something in Cell A1 to have ALL rows become unhidden. Thirdly, I would like to either type another code or just delete anything in Cell A1 and have ALL rows from Row 4 through the end of the sheet to become hidden again.
Tech Support Guy System Info Utility version 1.0.0.4
OS Version: Microsoft Windows 7 Professional, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM)2 Duo CPU T6600 @ 2.20GHz, Intel64 Family 6 Model 23 Stepping 10
Processor Count: 2
RAM: 3963 Mb
Graphics Card: Mobile Intel(R) 4 Series Express Chipset Family, 1853 Mb
Hard Drives: C: 453 GB (298 GB Free); F: 931 GB (710 GB Free);
Motherboard: TOSHIBA, Portable PC
Antivirus: Webroot SecureAnywhere, Enabled and Updated
 

Read other answers
RELEVANCY SCORE 57.2

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
 

Read other answers
RELEVANCY SCORE 57.2

Hi,
I am trying to hide rows in excel 2010 based on cell values in a certain column. I know that I have to use the VBA but I don't know how to do that kind of stuff. I know it should be simple to do but I don't know where to start. Let me know what you need in order to help me accomplish this task.
 

A:How to hide rows in excell based on cell value

Read other 8 answers
RELEVANCY SCORE 57.2

I have a worksheet that contains a budget, some of the line items are identified as allowances in column C, by the "allow" designation. The description of the allowance is in column B.

I want to create an automatic table on a worksheet called "Allowances" which searches column C for "allow", and if found returns the description from column B in cell A and the amount of the allowance from column F, in cell B.

Any suggestions?

Thanks,
 

Read other answers
RELEVANCY SCORE 57.2

Is there a way to insert a new worksheet based on whether or not a cell contains data and name that worksheet with the contents of the cell.
Ex: A1= Data
worksheet would be added and named Data
 

A:Insert Worksheet based on cell content

Read other 9 answers
RELEVANCY SCORE 57.2

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
 

A: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
 

Read other 2 answers
RELEVANCY SCORE 57.2

I have an Excel workbook with 400k rows Each set of rows contains a table name with the fields in the table. The number of fields is not the same for each table. For example:

COLUMN A COLUMNB

AV_ACTN_RSLT_VWACTN_RSLT_CDAV_ACTN_RSLT_VWDESCRAV_ACTN_RSLT_VWDESCRSHORTAV_ACTN_RSP_VWEMPLIDAV_ACTN_RSP_VWEXT_ORG_IDAV_ACTN_RSP_VWINSTITUTIONAV_ACTN_RSP_VWNAMEAV_ACTN_RSP_VWRSPL_ID

AMT_X_SAL_TYPEWCS_PLAN_DESCR20AMT_X_SAL_TYPEWCS_TOT_AMOUNT

I need to convert the field names so that they are in a row like this:

AMT_X_SAL_TYPEWCS_PLAN_DESCR20WCS_TOT_AMOUNT

Is there any way for me to do this programatically? I'm using the transpose feature but it is time consuming and I have 400k rows. So basically, I want something like if the field in column A row 1 matches row 2, move that field to the right of row 1 and so on until the table name changes.

Or maybe i should dump the spreadsheet in access and try there...

Thanks,

Amy
 

A:Move data from column to row based on value of cell?

Read other 6 answers
RELEVANCY SCORE 56.4

Hey all

New here and new to VBA so I apologize if this is the incorrect place to ask this question or if I'm not including enough information.

Basically I have two forms, one form that is a cost est. form and another form is a proposal form (client sees this one). I have the proposal form pulling information over from the cost est. form. What I would like to do is on the proposal form if rows c13:c277 are blank (no data pulled from the cost est. sheet) they will hide. But when I add data (a number) into the cost est. form the proposal form pulls that and unhides that row.

I've tried all kinds of VBA code and none seem to do what I want. I can get the rows to hide but then not unhide. I don't have code to show because I have deleted it when it didn't work. Any help would be greatly appreciated.
 

Read other answers
RELEVANCY SCORE 56.4

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!
 

A:Automatic Email from Excel based on Date in Cell

Read other 16 answers
RELEVANCY SCORE 56.4

Hey all

New here and new to VBA so I apologize if this is the incorrect place to ask this question or if I'm not including enough information.

Basically I have two forms, one form that is a cost est. form and another form is a proposal form (client sees this one). I have the proposal form pulling information over from the cost est. form. What I would like to do is on the proposal form if rows c13:c277 are blank (no data pulled from the cost est. sheet) they will hide. But when I add data (a number) into the cost est. form the proposal form pulls that and unhides that row.

I've tried all kinds of VBA code and none seem to do what I want. I can get the rows to hide but then not unhide. I don't have code to show because I have deleted it when it didn't work. Any help would be greatly appreciated.
 

Read other answers
RELEVANCY SCORE 56.4

Hello to evrebody,
I have look into the answers and the solutions about "hide/unhide rows based on the cell data" but i am not abble to understand how its work and apply to my sheet.
On theattached sheet i explain what im looking for.
Pleaseo play it and hope to help me and give a solution please.
Thanks in advance.
Jose
 

Read other answers
RELEVANCY SCORE 56.4

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
 

A: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.
 

Read other 1 answers
RELEVANCY SCORE 56.4

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
 

A:Excel Generate emails based on conditions of cell.

Read other 10 answers
RELEVANCY SCORE 56.4

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?
 

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

Read other 6 answers
RELEVANCY SCORE 56.4

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 19... Read more

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

anybody ???
 

Read other 2 answers
RELEVANCY SCORE 56.4

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
 

A:Emailing multiple recipients from Excel Based off Cell Value

Read other 9 answers
RELEVANCY SCORE 56.4

I know that this is probably a pretty trivial problem, but I can't seem to come up with the answer. I am trying to set the selection focus to a cell in a column, based on an offset variable. The following is a little macro that I created to experiment with. As written, it positions the selection focus on cell C6. But I can't find the way to set it to C6 + Offset. The only reason for the MsgBox statement is to prove that I passed the argument as expected.

Sub MvTo(ByVal Offset As Long)

Range("C6").Select
MsgBox "Offset " & Offset

End Sub

I thought I could use RnCn cell references and write something like Range("R6[+Offset]C3").Select, but I get an error return, so I'm missing something somewhere. Run-time error '1004', Method 'Range' of object "_Global' failed.

I am using Excel 2000.
 

A:Solved: Set cell selection based on a variable offset?

Read other 6 answers
RELEVANCY SCORE 56.4

Lets say I had in cell $A$1 the number "36", in row2 figures for monthly spend, in row 3 cumulative spend and in row 4 the month number(1,2,3,4..). What I want to do is substract the monthly spend from $A$1 months ago from my cululative figure in row 3 so I have a rolling cummulative hardware spend value of 36 months in this case. So in this case if I was to do it manually in cell AK3 I would have "=AJ3+AK2-A2" and drag it further right. So I add my monthly figure of spend each month to the cummulative figure in row three but also deduct the monthly spend from 3 years prior.

So in order to use the figure in $A$1 I could do something in row 3 like: starting from B3: "=if(B4>$A$1,A3+B2-[$A$1rows_further_back_thanB2],A3+B2). So when I get to month 37 in row 4 I deduct the figure in row 2 (in the formula in row 3 - the cummulative row) from 36 months prior. I hope this all makes sense. Can you help me please (see attached spreadsheet).
 

Read other answers
RELEVANCY SCORE 56.4

Hi Guys,

Apologies for repeating an old question, but I am trying to build my spreadsheet so that it auto-hides any rows were there is zero premium outstanding for a client.

I had gone through previous answers, and I had tried to lift and amend some VBA code to achieve this. However, somewhere within this code or the way I've implemented it, there is an error, because it's not showing of hiding any rows.

Can you please take a look at the attached and fix it (in order to hide all rows with a zero in column C), and maybe point out what I was doing wrong or omitting. I have anonymised my data.

Many Thanks
John
 

A:Show/Hide columns based on cell contents

Apologies... the criteria column is actually F, not C, the "outstanding premium" column. The intention being to display only those rows where client still owe premium
 

Read other 1 answers
RELEVANCY SCORE 56.4

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
 

A: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
 

Read other 1 answers
RELEVANCY SCORE 56.4

In order for my wife to get reimbursed, my wife has to turn in a Mileage Log every 2 weeks. In her Mileage Log form, each cell in the "Starting Location" (Column B) and each cell in the "Destination" (Column C) has a list of addresses from which she can select. The lists are generated from the DATA Sheet mileage chart

As an example and in other words, if we select the address at 417 Victoria Dr., Kerrville in Column B and select the address at 110 Ash, Ingram in Column C. By looking at the mileage chart on the DATA Sheet, we see the distance between those 2 places is 10.6 miles. Presently, we have to enter that mileage manually in Column F.

Is there a way to have excel get the mileage number from the DATA sheet based on the locations selected on the Mileage Log sheet. I could probably use a lot of nested IF statements but that would be real cumbersome and very hard to modify as she has clients come and go from time to time in which case I would have modify each IF statement. I'm sure there is a better and simpler way.

Tech Support Guy System Info Utility version 1.0.0.4
OS Version: Microsoft Windows 8.1, 64 bit
Processor: Intel(R) Core(TM) i7-4790S CPU @ 3.20GHz, Intel64 Family 6 Model 60 Stepping 3
Processor Count: 8
RAM: 12227 Mb
Graphics Card: NVIDIA GeForce GTX 750, 1024 Mb
Hard Drives: C: 1848 GB (1769 GB Free); D: 2794 GB (2536 GB Free);
Motherboard: ASUSTeK COMPUTER INC., K30AD_M31AD_M51AD_M32AD
Antivirus: Webroot SecureAnywhere, Enabled and... Read more

Read other answers
RELEVANCY SCORE 56.4

I need to provide seqential row numbers from 1 to N that relate to cells in another column when the cell value is greater than zero. For instance, if the column with data is column d and I have data greater than zero in cells d3, d4, d5, and d7 I'd like the row count in column c to read number 1 for c3, 2 for c4, 3 for c5, and 4 four c7. Cells c1 through c3 as well as c6 should remain blank. What formula do I use?
 

A:Solved: sequential row numbering based on value in another column/cell

Read other 7 answers
RELEVANCY SCORE 56.4

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
 

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

Read other 9 answers
RELEVANCY SCORE 56

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!
 

A: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.
 

Read other 1 answers
RELEVANCY SCORE 56

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.
 

A: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

Read other 1 answers
RELEVANCY SCORE 56

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,
 

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

Read other 10 answers
RELEVANCY SCORE 56

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.
 

Read other answers
RELEVANCY SCORE 56

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?
 

A: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.
 

Read other 3 answers
RELEVANCY SCORE 56

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!!
 

A: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
 

Read other 2 answers