Excel vba help needed

[Closed]
Report
-
Posts
1
Registration date
Friday July 31, 2009
Status
Member
Last seen
August 1, 2009
-
Hi folks,
Recently started working on vba's and am a new user in this forum.
I need help with 3 issues I have in a vba and if possible ASAP.
1. Delete first few lines(garbage-not needed) until it finds 'itemID' in column A and delete lines after 'GrandTotal' in column A.
2. In below sheet, IFBLANK in column C, then it should automatically put "NJ" in C5 and "NY" in C6 by matching 'itemID AND itemName' (both should match) and it should check from 'C1' to end of data

itemID itemName location
5486 box NJ
5211 cans AZ
4378 bottle NY
5486 box
4378 bottle


3. How to send email automatically when PC is locked but outlook running. I got this code from a website but everything works perfect when I am logged in.
I tried .Send and .Display and by sending keys but not working
Set OutApp = CreateObject("Outlook.Application")
OutApp.Visible = True
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = abc@abc.com
.CC = ""
.BCC = ""
.Subject = "Daily report" & TheDate - 1
.Body = "Please find the attached report. Have a good day. Thanks"
.Attachments.Add Flname
.Display 'or use .Send
Application.Wait (Now + TimeValue("0:00:10"))
SendKeys "%{s}", True
End With
Application.Wait (Now + TimeValue("0:00:10"))
SendKeys "%{s}", True

Set OutMail = Nothing
Set OutApp = Nothing
OutApp.Quit


Thanks in advance for your help. Much appreciated.

Thanks,
GRS

1 reply

Posts
1
Registration date
Friday July 31, 2009
Status
Member
Last seen
August 1, 2009

GRS --

I am also new to the board. Good location! I can help with #1. This code works -- called delete_garbage. Some comments. Pick the top where you want it to start, i used A1. It is just looping until it finds what it needs, deleting the entire rows along the way. You can also use "clearcontents" to erase the values in the rows it deleting the rows becomes a problem.

So it first looks for "itemID", then loops down to 1 row below Grand Total, then starts a delete row routing again. I set a loop counter... Otherwise it would just continue to delete rows forever! Happy coding!!!

RWD

Sub delete_garbage()
Range("A1").Select 'start at top

'LOOP UNITL CELL = "itemID"

Do Until ActiveCell.Value = "itemID" 'clean the trash
If ActiveCell.Value <> "itemID" Then
ActiveCell.EntireRow.Select
Selection.Delete shift:=xlUp
End If
Loop

'SEARCH FOR Grand Total
Dim loop_counter 'set a loop counter or it will run to the bottom!!
loop_couter = 1
Do Until ActiveCell.Value = "Grand Total"
If ActiveCell.Value <> "Grand Total" Then
ActiveCell.Offset(1, 0).Select
End If
Loop
ActiveCell.Offset(1, 0).Select 'go below Grand Total

'DELETE THE NEXT ROW THROUGH THE END OF THE LOOP COUNTER
Do Until loop_counter = 100
ActiveCell.EntireRow.Select
Selection.Delete shift:=xlUp
loop_counter = loop_counter + 1
Loop


End Sub