$$Excel-Macros$$ Create a macro based on a range of cells in excel and send via Lotus Notes with attachment
I have a spreadsheet with columns A through AD and have about 1000 rows on
my report. I need to run the report every month and send an email to anyone
that has a greater than or equal to 6 in Column "W". If column "W" is
greater than or equal to 6, then click a "send emails" button with an
attachment of the worksheet to email recipients in column "P" via Lotus
Notes. (There would be multiple addresses, and each month would be
different depending on the criteria.) Also if the criteria does not match
the 6, then delete the row, and always save a copy of the worksheet to my
"C" drive.
The code for the email with the subject, body text and attachment are
working, but I am having trouble with selecting the range from the
spreadsheet to send to the recipients and adding my signature to the end.
Any help on this would be greatly appreciated! Thanks in advance.
Option Explicit
Const EMBED_ATTACHMENT As Long = 1454
Const stPath As String = "C:"
Sub Send_Active_Sheet()
Dim stFileName As String
Dim Worksheets As Variant
Set Worksheets = Sheets("FabricsProjectList")
Dim myDocument As Range
Dim rng As Range
Dim vaRecipients As String
Dim vaCopyTo As Variant
Dim noSession As Object
Dim noDatabase As Object
Dim noDocument As Object
Dim noEmbedObject As Object
Dim noAttachment As Object
Dim stAttachment As String
Dim stSubject As Variant
Dim vaMsg As Variant
Dim x As Integer
Dim c As Range
Dim i As Long
Dim lRow As Long
Dim lCol As Long
Dim UserName As String
Dim MailDbName As String
Dim Maildb As Object
Dim MailDoc As Object
Dim AttachME As Object
Dim Session As Object
Dim stSignature As String
With Application
.ScreenUpdating = False
.DisplayAlerts = False
'Copy the active sheet to a new temporarily workbook.
With ActiveSheet
.Copy
stFileName = .Range("A1").Value
End With
' Select range of e-mail addresses
Worksheets("FabricsProjectList").Range("P6", "P1000").Value
For i = 7 To Range("A" & Rows.Count).End(3)(2).Row
' Cells.Range ("W6:W1000")
If Cells(i, "W:W") >= 6 Then
With Cells(i, "P:P")
vaRecipients = Worksheets("FabricsProjectList").Range("P" & i).Value
.SendTo = vaRecipients
stSubject = "Hi, Enterprise Project Champion," & vbCrLf & "This is just a
FYI - the last review of your Enterprise Project is older than 6 months...which
one ? Please see audit list attached ..."
vaMsg = "Hi," & vbCrLf & vbCrLf & "What I am looking for.. the reason
for this reminder" & vbCrLf & vbCrLf & "It is my commitment" & vbCrLf & vbCrLf
& "To run an audit every month" & vbCrLf & "To find out which projects are not
in the regular review process (6 months)" & vbCrLf & "To send out this info to
the champions and R&D leaders" & vbCrLf & vbCrLf & "Please be so kind and let
me know if there have been RWW's/ reviews in the meantime. If Yes, please send
me the documentation." & vbCrLf & vbCrLf & "We will enter the document and the
new last review date into the database." & vbCrLf & vbCrLf & "Thank you"
stSignature =
Maildb.GetProfileDocument("CalendarProfile").GetItemValue("Signature")(0)
stAttachment = stPath & "PROJECT TIME TRACKING\2013 Time Tracking
Reports" & stFileName & "Fabrics R&D Time Tracking Reports_Sep2013_rev2.xls"
End With
'Save and close the temporarily workbook.
With ActiveWorkbook
.SaveAs stAttachment
.Close
End With
'Instantiate the Lotus Notes COM's Objects.
Set noSession = CreateObject("Notes.NotesSession")
Set noDatabase = noSession.GETDATABASE("", "")
'If Lotus Notes is not open then open the mail-part of it.
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
'Create the e-mail and the attachment.
Set noDocument = noDatabase.CREATEDOCUMENT
Set noAttachment = noDocument.CreateRichTextItem("stAttachment")
Set noEmbedObject = noAttachment.EmbedObject(EMBED_ATTACHMENT, "",
stAttachment)
'Add values to the created e-mail main properties.
With noDocument
.Form = "Memo"
.SendTo = vaRecipients
.CopyTo = vaCopyTo
.Subject = stSubject
.Body = vaMsg
.SaveMessageOnSend = True
.PostedDate = Now()
.SEND 0, vaRecipients
End With
'Delete the temporarily workbook.
Kill stAttachment
'Release objects from memory.
Set noEmbedObject = Nothing
Set noAttachment = Nothing
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing
Set Maildb = Nothing
Set MailDoc = Nothing
Set Session = Nothing
MsgBox "Congratulations! The e-mail has successfully been created and
distributed", vbInformation
End If
Next i
End With
End Sub
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIM
$$Excel-Macros$$ Know cell.address for a clicked hyperlink
Hi When clicking a hyperlink, I want to know the address for the cell that holds the hyperlink (internal to another sheet in the workbook)? When using the follow_hyperlink event I can get the address for followed link, but not the address from which I jumped. Regards¨ Kim -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
$$Excel-Macros$$ How can I translate text in many cells of an Excel sheet from English to Greek with VBA
Hi all, I have a spreadsheet with thousands of rows and columns of text that I'd like to translate into Greek. Can anyone tell me how I might do that using VBA with some free service or API online? Many thanks in advance. Kim -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ When do my subscribers pay plan
Thanks :) Mvh Kim Pedersen Strand Vinduspuss Tlf. +47 934 60 815 2011/9/24 dguillett1 > See Attached > > *From:* Urghhh > *Sent:* Friday, September 23, 2011 2:33 PM > *To:* excel-macros@googlegroups.com > *Subject:* Re: $$Excel-Macros$$ When do my subscribers pay plan > > Hi > > Sorry :) Here it is. > > The sheet "from csv" is data copied from the csv file. > > The sheet "Output idea" is how I want the data to be sorted. The bold > numbers is the next delivery month. (I did manage to get thatone by a IF > statement) But i need to get the rest of the months to be filled by how many > times a year I will delivery my service (window cleaning). > > Kim > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ How do I find the highest of the most repeating values in a list of numbers ie. the max of the modes
Thanks so much Paul.
I really do need a single formula to do it but again, I'm so impressed
with the assistance you and others have given me on this forum.
I've actually reorganised my base data as a workaround so the need has
passed but I've kept a copy of this code for future reference.
Thanks guys.
Kim
On Jan 7, 1:32 am, Paul Schreiner wrote:
> Do you care if it is done with a macro?
>
> Option Explicit
> Public Dict_Vals
> Sub CheckVals()
> Dim KeyVals, R, MaxVal, MaxCnt, RowCnt
> Set Dict_Vals = CreateObject("Scripting.Dictionary")
> Dict_Vals.RemoveAll
> RowCnt = Application.WorksheetFunction.CountA(Range("A1:A65000"))
> For R = 1 To RowCnt
> If (Dict_Vals.exists(Sheets("Sheet1").Cells(R, "A").Value)) Then
> Dict_Vals.Item(Cells(R, "A").Value) = Dict_Vals.Item(Cells(R,
> "A").Value) + 1
> Else
> Dict_Vals.Add (Cells(R, "A").Value), 1
> End If
> Next R
> MaxVal = 0
> MaxCnt = 0
> KeyVals = Dict_Vals.keys
> For R = 0 To UBound(KeyVals)
> If Dict_Vals.Item(KeyVals(R)) > MaxCnt Then
> MaxVal = KeyVals(R)
> MaxCnt = Dict_Vals.Item(KeyVals(R))
> Debug.Print "VAL: " & MaxVal & " Cnt: " & MaxCnt
> ElseIf Dict_Vals.Item(KeyVals(R)) = MaxCnt Then
> If KeyVals(R) > MaxVal Then
> MaxVal = KeyVals(R)
> MaxCnt = Dict_Vals.Item(KeyVals(R))
> End If
> End If
> Next R
> MsgBox "VAL: " & MaxVal & Chr(13) & "Cnt: " & MaxCnt
> End Sub
>
> Paul
>
>
> From: Kim
> To: MS EXCEL AND VBA MACROS
> Sent: Thu, January 6, 2011 1:16:15 AM
> Subject: Re: $$Excel-Macros$$ How do I find the highest of the most repeating
> values in a list of numbers ie. the max of the modes
>
> Thanks so much Ashish but the problem is in the temporary column. I
> really need to do it in one cell only without introducing any new
> data.
>
> Any idea on how I can achieve that?
>
> Again, thanks for your help.
>
> Kim
>
> On Jan 6, 4:07 pm, ashish koul wrote:
>
>
>
>
>
> > check the attachment see if it helps
>
> > On Thu, Jan 6, 2011 at 6:17 AM, Kim wrote:
> > > Hi,
> > > I have a list of numbers and I need to find the highest of the most
> > > frequent occurrances.
>
> > > For example:
>
> > > List 1
> > > 1
> > > 3
> > > 3
> > > 4
> > > 4
>
> > > Should return the value 4 because its the highest of the most
> > > frequent
> > > occurrances of any number.
>
> > > List 2
> > > 1
> > > 3
> > > 3
> > > 3
> > > 4
> > > 4
>
> > > Should return 3 because it's the most frequent occurrance outright.
>
> > > I've tried using the mode command but it returns the first of the
> > > most
> > > frequent occurances but I want the highest of the most frequent
> > > occurrances.
>
> > > Any help would be greatly appreciated.
>
> > > Thanks
>
> > > Kim
>
> > > --
>
> >------
>
> > > Some important links for excel users:
> > > 1. Follow us on TWITTER for tips tricks and links :
> > >http://twitter.com/exceldailytip
> > > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > > To post to this group, send email to excel-macros@googlegroups.com
>
> > > <><><><><><><><><><><><><><><><><><><><><><>
> > > Like our page on facebook , Just follow below link
> > >http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&;...
>
> > --
> > *Regards*
> > * *
> > *Ashish Koul*
> > *akoul*.*blogspot*.com <http://akoul.blogspot.com/>
> > *akoul*.wordpress.com <http://akoul.wordpress.com/>
> > My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
>
> > P Before printing, think about the environment.
>
> > sample datat.xls
>
Re: $$Excel-Macros$$ How do I find the highest of the most repeating values in a list of numbers ie. the max of the modes
Thanks so much Ashish but the problem is in the temporary column. I really need to do it in one cell only without introducing any new data. Any idea on how I can achieve that? Again, thanks for your help. Kim On Jan 6, 4:07 pm, ashish koul wrote: > check the attachment see if it helps > > > > > > On Thu, Jan 6, 2011 at 6:17 AM, Kim wrote: > > Hi, > > I have a list of numbers and I need to find the highest of the most > > frequent occurrances. > > > For example: > > > List 1 > > 1 > > 3 > > 3 > > 4 > > 4 > > > Should return the value 4 because its the highest of the most > > frequent > > occurrances of any number. > > > List 2 > > 1 > > 3 > > 3 > > 3 > > 4 > > 4 > > > Should return 3 because it's the most frequent occurrance outright. > > > I've tried using the mode command but it returns the first of the > > most > > frequent occurances but I want the highest of the most frequent > > occurrances. > > > Any help would be greatly appreciated. > > > Thanks > > > Kim > > > -- > > > ------ > > Some important links for excel users: > > 1. Follow us on TWITTER for tips tricks and links : > >http://twitter.com/exceldailytip > > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com > > > To post to this group, send email to excel-macros@googlegroups.com > > > <><><><><><><><><><><><><><><><><><><><><><> > > Like our page on facebook , Just follow below link > >http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&;... > > -- > *Regards* > * * > *Ashish Koul* > *akoul*.*blogspot*.com <http://akoul.blogspot.com/> > *akoul*.wordpress.com <http://akoul.wordpress.com/> > My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830> > > P Before printing, think about the environment. > > sample datat.xls > 24KViewDownload- Hide quoted text - > > - Show quoted text - -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
$$Excel-Macros$$ How do I find the highest of the most repeating values in a list of numbers ie. the max of the modes
Hi, I have a list of numbers and I need to find the highest of the most frequent occurrances. For example: List 1 1 3 3 4 4 Should return the value 4 because its the highest of the most frequent occurrances of any number. List 2 1 3 3 3 4 4 Should return 3 because it's the most frequent occurrance outright. I've tried using the mode command but it returns the first of the most frequent occurances but I want the highest of the most frequent occurrances. Any help would be greatly appreciated. Thanks Kim -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
Re: $$Excel-Macros$$ Re: Using sum function for Unknown range
Thanks Paul!!!
Well, I wrote the whole code except for the last line
ActiveCell.Offset(0, 1).Formula = "=sum(" & Format(FBTFirstrow, "H#") &
":" & Format(FBTlastrow, "H#") & ")"
All I want is the following one you explained. Thanks a lot for that.
ActiveCell.Offset(0, 1).Formula = "=sum(H" & FBTFirstrow & ":H" & FBTlastrow
& ")"
For the other parts, it works exactly the way I want. I just didn't know how
to use sum with FBTLastrow and FBTfirstrow. I'm home now.. I will try your
suggestion when I get to work on monday.
You said I could simply define the range extremely large. In fact, I can get
the result with Sumif and selection of the whole column. But I just wanted
to do this way.
And you are right about the following code: it will look strange..
ActiveCell.Value = "Total FBT Transfer to Fund"
It's because the codes I provide is not the whole but part. There are other
codes which come with the code.
All I want was the last code.. how to write code for sum function with a
variable starting row and a variable ending row.
Thanks paul.. I will try on Monday and let you know how it went.
On Fri, Jun 25, 2010 at 2:43 AM, Paul Schreiner wrote:
> Is there other data in that column that you're NOT using?
> If there's not, you can simply define the range extremely large.
>
>
> Or... are you trying to find the last row with ANY data?
>
> that is to say:
> FBTlastrow = ActiveCell.SpecialCells(xlLastCell).Row
> will give you the row number of the last row of the entire spreadsheet
> that contains data.
>
> You said that you don't fully understand your code, so let's step through
> it:
>
>
> > Range("C:C").Find("Description", LookIn:=xlValues).Select
> this selects column "C" and searches for the string "Description",
> then selects this cell.
>
> > FBTFirstrow = ActiveCell.Offset(1, 0).Row
> this returns the row number of the cell immediately below currently
> selected cell
> (that has the word "description")
>
> > FBTlastrow = ActiveCell.End(xlDown).Row
> this has the effect of hitting the "end" key and the "down" arrow.
> as you observed, it will take you to the last entry before the first
> blank
> space.
> Instead, I would use:
> FBTlastrow = ActiveCell.SpecialCells(xlLastCell).Row
>
> >
> > ActiveCell.Value = "Total FBT Transfer to Fund"
>
> This is curious. Unless you've left out something, the currently
> selected cell
> contains the word "Description", and you're changing it to:
> "Total FBT Transfer to Fund"
>
> > ActiveCell.Offset(0, 1).Formula = "=sum(" & Format(FBTFirstrow, "H#")
> > & ":" & Format(FBTlastrow, "H#") & ")"
> This offset statement identifies the cell 0 rows down and one column to
> the right
> and inserts a formula.
> The problem is that this is an improper use of the "format" function.
> In the formatting expression, "H" means to display the Hour without
> leading "0"'s.
> since this isn't a TIME, I suspect you were trying to use this format
> statement
> as if it were similar to C++. It's not.
> How it may have worked properly for F and G is beyond me ..
>
> what I would use is:
> ActiveCell.Offset(0, 1).Formula = "=sum(H" & FBTFirstrow & ":H" &
> FBTlastrow & ")"
>
> but this whole thing looks suspicous.
>
> Because if you ran it for column "F", then the sum would be placed in the
> column "G" header.
>
> I suspect something more is going on.
>
> I think we need to know more about what you're trying to do
> and what you want it to look like when you're done...
>
> Paul
>
> --
> *From:* Mark Kim
>
> *To:* excel-macros@googlegroups.com
> *Sent:* Thu, June 24, 2010 7:15:11 AM
> *Subject:* Re: $$Excel-Macros$$ Re: Using sum function for Unknown range
>
> Can not use like that.
>
> All I know is which column i need to use. I do not know how long is the sum
> range and where to start.
>
> That's why I have FBTFirstrow and FBTlastrow . I need to have sum formular
> in the cell so i can verify the formula
>
> On Thu, Jun 24, 2010 at 3:22 PM, saggi wrote:
>
>> If you are not using Dynamic Range then use simple Sum formula, Just
>> change range n column number
>>
>> Sub SUM()
>> ActiveSheet.Select
>> ActiveCell.FormulaR1C1 = "=sum(R[-1]C:R[-22]C)&q
Re: $$Excel-Macros$$ Re: Using sum function for Unknown range
Can not use like that.
All I know is which column i need to use. I do not know how long is the sum
range and where to start.
That's why I have FBTFirstrow and FBTlastrow . I need to have sum formular
in the cell so i can verify the formula
On Thu, Jun 24, 2010 at 3:22 PM, saggi wrote:
> If you are not using Dynamic Range then use simple Sum formula, Just
> change range n column number
>
> Sub SUM()
> ActiveSheet.Select
> ActiveCell.FormulaR1C1 = "=sum(R[-1]C:R[-22]C)"
> Range("B23").Select
> End Sub
>
>
>
> On Jun 24, 7:58 am, Markkim wrote:
> > Hi
> >
> > I have a set of data which I need to sum up between unknown range..
> >
> > The following is the code I am using
> >
> > Range("C:C").Find("Description", LookIn:=xlValues).Select
> > FBTFirstrow = ActiveCell.Offset(1, 0).Row
> > FBTlastrow = ActiveCell.End(xlDown).Row
> >
> > ActiveCell.Value = "Total FBT Transfer to Fund"
> > ActiveCell.Offset(0, 1).Formula = "=sum(" & Format(FBTFirstrow, "H#")
> > & ":" & Format(FBTlastrow, "H#") & ")"
> >
> > The first paragraph - I assigned beginning of the row to FBTFirstrow
> > and end of the row to FBTlastrow
> >
> > I can not use ActiveCell.End(xlDown) for sum range because there are
> > blank cells in the columns which contains actual data. that's why I am
> > getting FBTlatrow.
> >
> > Now Let's say FBTFirstrow is 50 and FBTlastrow is 80
> >
> > then, I like to insert a formula sum(H50:H80)
> >
> > And the code above I got it from somewhere in the internet, which I
> > don't have full understanding..
> >
> > what's very odd is the code above works perfect for sum(F50:F80) or
> > sum(G50:G80) - I simply change H# to F# or G#. But it doesn't work
> > with H#. I don't understand. Why is it not working.
> >
> > one other question I'd like to ask is
> >
> > is there any better way of doing this?
> >
> > I have first row and last row. with this information, I'd like to sum
> > up different columns.
> >
> > Cheers
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 7000 subscribers worldwide and receive many nice notes about
> the learning and support from the group.Let friends and co-workers know they
> can subscribe to group at
> http://groups.google.com/group/excel-macros/subscribe
>
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Navigation Macro..
Hi Spud I mean for example, Cell A1... I have a formula = Sheet2!A3. I have a worksheet for for example Feb GST incurred ... (I have lots which are to caculate for exmpale.. FBT, Deposits.. different types of costs.. ) And i also have a workbook (separate file) which summarize all of these worksheets.. I can not have a absolute value for any of numbers presented in this summary.. All have to be linked.. simply.. All the value cells have a formula such as =[GST.xls]Feb2010'A3' .. something like this.. hm... I think unticking the option edit directly in cell .. will do the job.. but I really hope to get a macro.. well, I'm using 2007.. where is the option? can't seem to be able to locate it.. ^ ^;; I went throught all the options. On Tue, Mar 23, 2010 at 10:49 AM, Spud wrote: > Can you post an example showing how the format of the cell values (you > mention that the cell value can be a workbook and a cell: does it show > the path correctly for the workbook? Unless you know the correct path > for the workbook, or they're all in the same path/folder, then it > would be impossible to open the correct workbook). > > If by cell value you actually mean formula (ie. in cell A1 you have > the formula =Sheet 2!A3) then you could just go into tools>options and > untick edit directly in cell, and then when you double click in cell > A1 it will take you to > Sheet 2!A3. This will also open up the workbook if the formula is > ='[Book1.xls]Sheet 2'!$A$3. Unticking edit directly in cell will mean > you can't edit formula's in the cell, you have to do it in the formula > bar. > > Cheers, > > Spud > > On Mar 22, 10:34 pm, Mark Kim wrote: > > The problem is then I need a create hyperlink manually.. > > > > Then I need to create hundreds hyperlink.. > > > > Also, everytime I click the cell, it takes me to the reference cell, > which I > > don't want to. I want to go to the reference cell when I want to. > > > > That's why I don't think Hyperlink is a solution. > > > > Due to system problems, I am doing Reconcilation manually with thousands > > data. Creating a hyerlink for every cell I need to link is too much time > > consuming. > > > > I don't think it will be a complicated macro.. > > > > Get the value from a cell... > > > > if the value is workbook.;.. open the workbook and go to the reference > cell > > > > if the value is a cell, go to the cell > > > > well, I think this logic will work.. I just don't know how to write > this.. > > > > Anyone please? ^ ^ > > > > On Mon, Mar 22, 2010 at 5:57 PM, Yahya wrote: > > > Dear Mark > > > > > I think you can do it by just using a Hyperlink. No need for macros. > > > Please see the attached file > > > > > Yahya > > > > > On Mon, Mar 22, 2010 at 2:37 AM, Markkim wrote: > > > > >> Hi > > > > >> I am wondering if anyone can help me creating a macro for the > > >> following.. > > >> Get a reference from the selected cell and take me there . > > >> For example, A1 contains C4. The macro will take me to C4. This is a > > >> very simple one. > > >> A1 contains a reference Sheet2!C4. I’d like the macro to take me > > >> sheet2 C4. > > >> And if A1 contains a location of the other file. Let’s say… A1 > > >> contains Sheet ‘sales’ C4 in the file named ‘Sales’. Then I want the > > >> excel to open the file and take me to C4 in a sheet called ‘sales’. > > >> I want a button to get back to where I was. > > > > >> Can you create a macro like this? > > > > >> -- > > > > >> > -- > > >> Some important links for excel users: > > >> 1. Follow us on TWITTER for tips tricks and links : > > >>http://twitter.com/exceldailytip > > >> 2. Join our Facebook Group @ > > >>http://www.facebook.com/group.php?gid=287779555678 > > >> 3. Excel tutorials athttp://www.excel-macros.blogspot.com > > >> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > > >> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com > > > > >> To post to this group, send email to excel-macros@googlegroups.com > > > > >> <><><><><><><><><><><><><><><><><><><><><><> > > >> HELP US GR
Re: $$Excel-Macros$$ Navigation Macro..
The problem is then I need a create hyperlink manually.. Then I need to create hundreds hyperlink.. Also, everytime I click the cell, it takes me to the reference cell, which I don't want to. I want to go to the reference cell when I want to. That's why I don't think Hyperlink is a solution. Due to system problems, I am doing Reconcilation manually with thousands data. Creating a hyerlink for every cell I need to link is too much time consuming. I don't think it will be a complicated macro.. Get the value from a cell... if the value is workbook.;.. open the workbook and go to the reference cell if the value is a cell, go to the cell well, I think this logic will work.. I just don't know how to write this.. Anyone please? ^ ^ On Mon, Mar 22, 2010 at 5:57 PM, Yahya wrote: > Dear Mark > > I think you can do it by just using a Hyperlink. No need for macros. > Please see the attached file > > Yahya > > > > On Mon, Mar 22, 2010 at 2:37 AM, Markkim wrote: > >> Hi >> >> I am wondering if anyone can help me creating a macro for the >> following.. >> Get a reference from the selected cell and take me there . >> For example, A1 contains C4. The macro will take me to C4. This is a >> very simple one. >> A1 contains a reference Sheet2!C4. I’d like the macro to take me >> sheet2 C4. >> And if A1 contains a location of the other file. Let’s say… A1 >> contains Sheet ‘sales’ C4 in the file named ‘Sales’. Then I want the >> excel to open the file and take me to C4 in a sheet called ‘sales’. >> I want a button to get back to where I was. >> >> Can you create a macro like this? >> >> -- >> >> -- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our Facebook Group @ >> http://www.facebook.com/group.php?gid=287779555678 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> HELP US GROW !! >> >> We reach over 6,800 subscribers worldwide and receive many nice notes >> about the learning and support from the group.Let friends and co-workers >> know they can subscribe to group at >> http://groups.google.com/group/excel-macros/subscribe >> >> To unsubscribe from this group, send email to excel-macros+ >> unsubscribegooglegroups.com or reply to this email with the words "REMOVE >> ME" as the subject. >> > > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words "REMOVE ME" as the subject.
Re: $$Excel-Macros$$ Re: remove empty cells (rows) between two cells
On Fri, Feb 26, 2010 at 4:44 PM, Ayush wrote: > Hi Markkim, > > Would you please send the sample file containing this data? > > Best regards, > Ayush Jain > > On Feb 26, 6:18 am, Markkim wrote: > > Hi > > I have been trying to make these but have failed. ^ ^;; > > I’d like to create a macro > > > > A21 has a word called ‘Date’ > > > > And somewhere down in column A has a cell with a word ‘FBT Details’. > > The location of FBT details depends on the amount of data between > > these two headings. I’d like to remove any blank rows between A21 and > > location of > > FBT details – 7. > > > > What I mean is > > > > For example, location of FBT details is A1207. Then I’d like to > > remove any empty rows (cells) between A21 and A1200 (not A1207). so 7 > > rows above. > > > > I’d like to do this because the data between these cells are one row > > apart. > > > > Could you help me please? > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our Facebook Group @ > http://www.facebook.com/group.php?gid=287779555678 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 6,800 subscribers worldwide and receive many nice notes about > the learning and support from the group.Let friends and co-workers know they > can subscribe to group at > http://groups.google.com/group/excel-macros/subscribe > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe sample.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet sample.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Re: remove empty cells (rows) between two cells
Hi, Thanks for the code. But I tried but it doesn’t seem to work as expected. Can I ask you this please? in the data sheet you uploaded, what I want is to select Dat1 to Data 7 and remove blank rows. In my real data I work with, there are 6 rows between the last transaction record and FBT details. And I’d like to keep that distance. If you can write a code to select this range, I think I can use the following code to remove the blank rows.. Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete Could you revise the code??? I really appreciate your help. On Fri, Feb 26, 2010 at 7:33 PM, Christopher Luka < christopher.l...@gmail.com> wrote: > See attached for example. > > > On Thu, Feb 25, 2010 at 9:44 PM, Ayush wrote: > >> Hi Markkim, >> >> Would you please send the sample file containing this data? >> >> Best regards, >> Ayush Jain >> >> On Feb 26, 6:18 am, Markkim wrote: >> > Hi >> > I have been trying to make these but have failed. ^ ^;; >> > I’d like to create a macro >> > >> > A21 has a word called ‘Date’ >> > >> > And somewhere down in column A has a cell with a word ‘FBT Details’. >> > The location of FBT details depends on the amount of data between >> > these two headings. I’d like to remove any blank rows between A21 and >> > location of >> > FBT details – 7. >> > >> > What I mean is >> > >> > For example, location of FBT details is A1207. Then I’d like to >> > remove any empty rows (cells) between A21 and A1200 (not A1207). so 7 >> > rows above. >> > >> > I’d like to do this because the data between these cells are one row >> > apart. >> > >> > Could you help me please? >> >> -- >> >> -- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our Facebook Group @ >> http://www.facebook.com/group.php?gid=287779555678 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> HELP US GROW !! >> >> We reach over 6,800 subscribers worldwide and receive many nice notes >> about the learning and support from the group.Let friends and co-workers >> know they can subscribe to group at >> http://groups.google.com/group/excel-macros/subscribe >> > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our Facebook Group @ > http://www.facebook.com/group.php?gid=287779555678 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 6,800 subscribers worldwide and receive many nice notes about > the learning and support from the group.Let friends and co-workers know they > can subscribe to group at > http://groups.google.com/group/excel-macros/subscribe > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ How to create a link to other sheets...
Sorry I didn't attach an file. hm.. not sure how to attach a file. Hope this is ok.. On Sun, Jan 17, 2010 at 7:50 AM, Mark Kim wrote: > Hi Praveen > > Thanks Million!!! This is exactly what I was looking for.. > > Thanks a lot !!! > > > On Sun, Jan 17, 2010 at 1:35 AM, kvc praveen kumar wrote: > >> Hi, >> >> Please find the attached file and let me know if this is what you are >> looking for. I used worksheet events to impement the same. >> Regards, >> Praveen >> On Sat, Jan 16, 2010 at 6:39 PM, Markkim wrote: >> >>> Hi all >>> >>> Wow.. I didn't know there is a place like this where you can get some >>> help from experts for Excel macro.. It's brilliant.. !! >>> >>> I have been thinking for some time that It would be very useful if I >>> had this feature.. >>> >>> I hope it's possible. >>> >>> I have an excel file which has three sheets. >>> >>> First sheet contain a list of transaction and each of transaction has >>> a unique ID and one of these transaction are related to the next >>> sheet. >>> >>> Second sheet also contains a list of transactions and each of >>> transaction has a unique ID as well. >>> >>> In the first sheet, I have a column that has a unique ID of >>> transaction from the second sheet. >>> >>> For example >>> >>> Sheet 1 >>> >>> ID Company Product (ID from sheet 2) >>> 1 A 3 >>> 2 C 1 >>> 3 D 2 >>> >>> Sheet 2 >>> ID ProductCompany (ID from Sheet1) >>> 1 X2 >>> 2 Y 3 >>> 3Z 1 >>> >>> As you can see above.. Sheet 1 and sheet 2 are related. What I need >>> is .. when I click the cell which contains ID from sheet 2 in Sheet 1, >>> I want excel to take me there.. so I don't need to look up in the >>> sheet 2.. it will move there. or clicking the next cell..so.. >>> when I click 3 in the product column from Sheet1, I want to move to ID >>> 3 in the Sheet2 and vice versa. >>> >>> I am not good at explaining.. I hope I make sense. >>> >>> I will be really appreciated if you could help me.. >>> >>> Thanks for reading.. >>> >>> >>> -- >>> >>> -- >>> Some important links for excel users: >>> 1. Follow us on TWITTER for tips tricks and links : >>> http://twitter.com/exceldailytip >>> 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at >>> http://www.excelitems.com >>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>> >>> >>> To post to this group, send email to excel-macros@googlegroups.com >>> If you find any spam message in the group, please send an email to: >>> Ayush Jain @ jainayus...@gmail.com >>> <><><><><><><><><><><><><><><><><><><><><><> >>> HELP US GROW !! >>> >>> We reach over 6,700 subscribers worldwide and receive many nice notes >>> about the learning and support from the group.Let friends and co-workers >>> know they can subscribe to group at >>> http://groups.google.com/group/excel-macros/subscribe >>> >>> >> >> -- >> >> -- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at >> http://www.excelitems.com >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> >> To post to this group, send email to excel-macros@googlegroups.com >> If you find any spam message in the group, please send an email to: >> Ayush Jain @ jainayus...@gmail.com >> <><><><><><><><&g
Re: $$Excel-Macros$$ How to create a link to other sheets...
Hi Praveen Thanks Million!!! This is exactly what I was looking for.. Thanks a lot !!! On Sun, Jan 17, 2010 at 1:35 AM, kvc praveen kumar wrote: > Hi, > > Please find the attached file and let me know if this is what you are > looking for. I used worksheet events to impement the same. > Regards, > Praveen > On Sat, Jan 16, 2010 at 6:39 PM, Markkim wrote: > >> Hi all >> >> Wow.. I didn't know there is a place like this where you can get some >> help from experts for Excel macro.. It's brilliant.. !! >> >> I have been thinking for some time that It would be very useful if I >> had this feature.. >> >> I hope it's possible. >> >> I have an excel file which has three sheets. >> >> First sheet contain a list of transaction and each of transaction has >> a unique ID and one of these transaction are related to the next >> sheet. >> >> Second sheet also contains a list of transactions and each of >> transaction has a unique ID as well. >> >> In the first sheet, I have a column that has a unique ID of >> transaction from the second sheet. >> >> For example >> >> Sheet 1 >> >> ID Company Product (ID from sheet 2) >> 1 A 3 >> 2 C 1 >> 3 D 2 >> >> Sheet 2 >> ID ProductCompany (ID from Sheet1) >> 1 X2 >> 2 Y 3 >> 3Z 1 >> >> As you can see above.. Sheet 1 and sheet 2 are related. What I need >> is .. when I click the cell which contains ID from sheet 2 in Sheet 1, >> I want excel to take me there.. so I don't need to look up in the >> sheet 2.. it will move there. or clicking the next cell..so.. >> when I click 3 in the product column from Sheet1, I want to move to ID >> 3 in the Sheet2 and vice versa. >> >> I am not good at explaining.. I hope I make sense. >> >> I will be really appreciated if you could help me.. >> >> Thanks for reading.. >> >> >> -- >> >> -- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at >> http://www.excelitems.com >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> >> To post to this group, send email to excel-macros@googlegroups.com >> If you find any spam message in the group, please send an email to: >> Ayush Jain @ jainayus...@gmail.com >> <><><><><><><><><><><><><><><><><><><><><><> >> HELP US GROW !! >> >> We reach over 6,700 subscribers worldwide and receive many nice notes >> about the learning and support from the group.Let friends and co-workers >> know they can subscribe to group at >> http://groups.google.com/group/excel-macros/subscribe >> >> > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at > http://www.excelitems.com > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > > To post to this group, send email to excel-macros@googlegroups.com > If you find any spam message in the group, please send an email to: > Ayush Jain @ jainayus...@gmail.com > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 6,700 subscribers worldwide and receive many nice notes about > the learning and support from the group.Let friends and co-workers know they > can subscribe to group at > http://groups.google.com/group/excel-macros/subscribe > > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Please help with vba code to download a file from a website
Hi everyone, I need some vba code to download a specific file from a specific internet location to a specific place on my hard drive. I don't really want any prompts or message boxes and definitely don't want the user to have to pick where the file will be saved. What I mean is that I want it to download a file like... www.testsite.com\test.txt to a spot on my hard drive like... c:\temp\ without inputs or prompts or navigating to save location. Can anyone help please? In all my testing, I have to navigate to where I want the file to go which doesn't suit. Thanks all. Kim --~--~-~--~~~---~--~~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit & Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com -~--~~~~--~~--~--~---
