Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Insert value on double click Results

I did a search on the forums and quickly found 2 ways for inserting a new line.

I tried the following code:

This produces two squares between the first and the second word. When I double click the cell it does create a new line and
then leaves one square after the first word.

Then I tried:
This produces one square between the words. When I double click the cell it creates a new line and the square disappears.

What should I use to produces 0 squares and have a new line without clicking in the cell. Funny behavior though

I am using Excel 2003 for the PC.

I want to be able to perform arithmetic on an existing cell value,
e.g., A1 contains the value 45 and I wish to add other values to it.

Typing the equal sign obliterates the original entry. Double clicking
the cell might place the insertion point between numbers--this is
awkward. Pressing F2 places the insertion point at the end of the
value. This is also awkward because I must move the insertion point to
the beginning of the value to place the equal sign.

My Excel for 2004 for Mac has a calculator icon which when clicked
automatically enters the initial value into an on-screen calcultor.

Any suggestions as to how this might be efficiently done?

Thanks,
Stu B

Good day. I am using Excel 2002 with Windows ME. I have
a worksheet that contains product costs supplied to me by
my vendor in the following format;

$1.00 (US)
$12.00 (US)
$123.00 (US)
$1234.00 (US)

I have inserted two columns, one to contain a fomula and
the other to copy the formula results into.

The first column contains the formula shown below to
extract the " (US)" from each of the costs.

=LEFT(C2,SEARCH(" ",C2,1))

This appears to work although it does leave a single space
character behind the actual product cost. The worksheet
contains almost 300 different products and the cost for
each one.

After executing the formula shown above, I then copy the
entire column into the second column using Paste Special
and selecting Values. This appears to work also and
leaves me with the product cost with a left-justified
value with a dollar sign and a single space behind the
cost. Eventually I would like to get rid of the space.
Both of the new columns are formatted as Currency with 2
decimal places.

All appears to be OK. However, if I double-click any
single value in the second column and then click any other
cell, the value in the double-clicked cell loses its
currency formatting and changes to number format. This
loses the dollar sign and the value changes from left-
justified to right-justified. Also, if the value contains
a zero or a double zero as part of the cents value, that
zero is no longer shown even though the number format
calls for 2 decimal places.

I am at a loss on this one. If anyone has any
suggestions, please forward them. I could also some
advice on how to get rid of the space between the cost and
the "(US)".

Thanks, Danno...

Hi,

Let me explain what I am trying to do.

I have a 2 sheet workbook Sheet1 = (Data) is a pivot table ,Sheet2(Saved)

I am trying to create a macro that works with my pivot table. The problem is when I record my macro I start on sheet1 to double click the value in cell B3 it automatically opens up sheet3( sheet3 is all of the person work for that day) So I want to insert 3 lines and count or sum all the cells in the ranges A3:A500 for coulmns A,B,C,D and E .I then want to copy all the calculations just made (sheet3 cells A1:A5) then paste special the values to sheet2(saved) then Delete sheet3 and return to sheet1 to start all over again at B4 and so on till it hits an empty cell on sheet1 (data).

Can someone help me with this code:

Thank You So Much...

Sub Macro6()
        Dim cell    As Range
        Dim wks     As Worksheet

        For Each cell In Worksheets("data").Range("B3", Range("B3").End(xlDown))
        cell.ShowDetail = True
        Set wks = Worksheets.Add
        Rows("1:3").Select
        Selection.Insert Shift:=xlDown
        Range("A1").FormulaR1C1 = "=R[4]C"
        Range("C1").FormulaR1C1 = "=COUNT(R[4]C:R[499]C)"
        Range("D1:F1").FormulaR1C1 = "=SUM(R[4]C:R[499]C)"
        Range("A1:F1").Copy
        Sheets("New Totals").Range("A3").PasteSpecial Paste:=xlPasteValues
        Application.DisplayAlerts = False
        wks.Delete
        Application.DisplayAlerts = True
        Next cell
End Sub


(code below mostly borrowed from "Excel 2002 VBA" pub. by WROX)

Using the code below, if the user is on Sheet1 and double-clicks on a cell in column Q (e.g. Q5), a combobox is inserted at Q5 and populated (see code). When the user chooses from the combobox, his choice is put into Q5 (using

.TopLeftCell.Value = .List(.ListIndex)

after which the combobox is deleted.

When I first tested (e.g in cell Q5), I went crazy because the value was somehow inserted in cell P4. (One row up and one column left.) Note that Excel's zoom setting was at 75%. When I changed the zoom to 100%, the code ran fine and the selected value was inserted (correctly) in Q5.

I used a message box to check the address of TopLeftCell and, in fact the value of that property (for the same combobox) changed when I changed the zoom setting.

Any suggestions on how I can fix this? I thought my code for the size of the combobox fit the control to the size of the cell, but must be more to this.

Thanks in advance.
Bill

<code below in module for Sheet1 >

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Columns("Q")) Is Nothing Then
Call AddDropDown(Target)
Cancel = True
End If
End Sub

< code below in Module 1 >

Sub AddDropDown(Target As Range)
Dim ddBox As DropDown
Dim vaProducts As Variant
Dim i As Integer

vaProducts = Array( _
"No Schedule At All", _
"Different results with different criteria", _
"Schedules wrong (times, train#, etc.)", _
"Schedules missing", _
"Schedules from useless stations", _
"Have to choose stations", _
"Ticket not matching", _
"Ticket missing", _
"Other")
With Target
Set ddBox = Sheet1.DropDowns.Add(.Left, .Top, .Width, .Height)
End With
With ddBox
.OnAction = "EnterProdInfo"
For i = LBound(vaProducts) To UBound(vaProducts)
.AddItem vaProducts(i)
Next i
End With
End Sub

Private Sub EnterProdInfo()
With Sheet1.DropDowns(Application.Caller)
.TopLeftCell.Value = .List(.ListIndex)
.Delete
End With
End Sub

Using Excel 2003. I'm bringing in data from Sharepoint via IQY as
shown:

WEB1
SharePointApplication=http://www.helloworld.com:5060/_vti_bin
SharePointApplication=http://www.helloworld.com:5060/_vti_bin
SharePointListName={CD33EA8B-A0B3-4EAF-8BA3-C8BEAD289D5C}
SharePointListView={1B35BCF5-6842-4841-AC41-5D3D18B1676A}

This works great, if I 'double-click' the .iqy file, except I'd like to
take things a step further, but I'm having issues.

One, I'd like the data to come in as static data, not as a data range.
As a range I can't insert a row, sum a column, etc, without first
copying and pasting the data (values) to a second worksheet. Is this
possible, perhaps by way of add'l IQY properties? Excel settings?

Second, I'd like to save the query with the workbook, configured to
auto refresh when the workbook is opened. So, I set the Data Range
properties 'Refresh Data on File Open' and 'Remove External Data ...
Before Saving'. Then I save and close. Then I open, all looks good, but
then 'A connection to sharepoint site cannot be established'. So the
refresh fails. The sharepoint site requires authentication, BTW.
Appreciate feedback immensely. Thanks,

DAN

I have 2 columns of values that I would like to convert into a query. The two columns are called Customer Name and Cust #. The end result query is shown at the bottom of the table. Note the single quotes need to be added around customer name, double quotes around Cust# and insert an OR between Customer name and Cust#. Any ideas?

******** ******************** src="*********>*********>Microsoft Excel - FOC CustomerAccount IPVPN Test.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB16=
BCDEFGHIJKLM3Customer Name (+*)Cust #          4ABC IncABC123          5Telco IncABC456          6ACME IncABC789          7Smith IncABC246          8            9end resulting query           10            11'Customer Name (+*)' = "ABC123" or 'Customer Name (+*)' = "ABC456" or 'Customer Name (+*)' = "ABC789" or 'Customer Name (+*)' = "ABC246"           Sheet1 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I currently have a userform, and on commandbutton_click, it performs
the following code that adds a column in a access table to a combobox(cbList)

Code:
Private Sub CmdName_Click()
Dim rstName As ADODB.Recordset
Dim strClientDatabase As String, strConnectionString As String
strClientDatabase = ActiveWorkbook.Path & "9001.mdb"
strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strClientDatabase & ";Persist Security Info=False"

Set rstName = New ADODB.Recordset
rstName.Open "clients", strConnectionString, adOpenStatic

Do While Not rstName.EOF
cbList.AddItem rstName("Client_name")
rstName.MoveNext
Loop
formImportClients.cmdID.Enabled = False
formImportClients.CmdName.Enabled = False
End Sub
----------------------------------------------------------------------------

After its listed on the combo box, upon commandbutton_click on another button, i wish to extract a particular record, based on the selection made on the combobox(cbList). This is where im having problems caused i have no idea how to do so. I just want it to extract the particular record row, based on cbList, and insert it into range A100:D100 in a particular worksheet. It is then updated and added to a listbox which ive already done the coding for, and with another button click it would add the details in the listbox into the appropriate location i wish to.. The only place im stuck is with extracting the data from access into a A100:D100 range in any worksheet. what ive come up with, but is incomplete is :

Code:
Private Sub CmdImport_Click()
Dim adoRS As ADODB.Recordset
Dim strSQL As String
Dim strClientDatabase As String, strConnectionString As String
strClientDatabase = ActiveWorkbook.Path & "9001mdb"
strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strClientDatabase & ";Persist Security Info=False"

Set adoRS = New ADODB.Recordset
adoRS.Open "clients", strConnectionString, adOpenStatic
strSQL = "SELECT * FROM clients"
.
.
.
.
.
.
Dim rngOptionType As Range
Dim rngCell As Range
Set rngOptionType = ActiveWorkbook.Worksheets("Clients").Range("A100:D100")
For Each rngCell In rngOptionType
listboxImport.AddItem rngCell.Value
Next rngCell
End Sub
------------------------------------------------------------------------
Would sincerly appreciate anyone who could help fill in those blanks for me.. and apologies if this is considered a double post, cause my other post was not as specific, will try deleting the old one.

I must apologize in advance for the length. The question is specific and I have tried to be concise while including all necessary information.

I am attempting to do exponential regression and am finding that using Linest gives values close to what I need, but when I double-check them by charting the same data and applying a trendline the numbers are slightly off, enough that I cannot accept the difference. I believe this may be because my x-values are nonlinear.

Question: Why are my calculations always apparently close, but not correct?

For illustration, assume that my x-values, represented by $A$4:$A$11 are {0, 24, 48, 96, 144, 192, 288, 384} and my y-values, represented by $B$4:$B$11, are {0.5, 0.51, 0.51, 0.65, 0.65, 0.51, 0.65, 0.65}.

A stripped version of the program showing exactly this is attached as "Testbook1".

The 'coefficients' formula that is inputted within the cell will look like this:
Ex. "=EXP(INDEX(LINEST(LN($B$4:$B$11), LN($A$4:$A$11),TRUE,FALSE), 1, 2 ) )"

The 'power' formula may look like this:
Ex. "=LINEST(LN($B$4:$B$11), LN($A$4:$A$11),, TRUE)"

The "R^2" formula may look like this:
Ex. "=INDEX(LINEST(LN($B$4:$B$11), LN($A$4:$A$11),, TRUE), 3, 1)"
The example for 'coefficient' above gives a value of 0.51172862. The trendline option in excel gives a coefficient of 0.5227.

The example for 'power' above gives a value of 0.029640993. The trendline option in excel gives a power of 0.0006.

The example for 'R^2' above gives a value of 0.355785557. The trendline option in excel gives an R^2 of 0.4251.

My next two posts will have progressively more detail for anyone who is interested. If this is enough, I recommend not wasting the time to read them! Auto Merged Post Until 24 Hrs Passes;

Note: I have read this thread: http://www.ozgrid.com/forum/showthread.php?t=85286, which seemed to have a very similar problem. However, reading the posted link (http://support.microsoft.com/kb/211967) that helped the author has not solved the problem.

Here is the long version of the above post:

I must apologize in advance for the length. The question is specific and I have tried to be concise while including all necessary information.

I am attempting to do exponential regression and am finding that using Linest gives values close to what I need, but when I double-check them by charting the same data and applying a trendline the numbers are slightly off, enough that I cannot accept the difference. I believe this may be because my x-values are nonlinear (e.g. 0, 24, 48, 96, 144, 192, 288, 384 as opposed to 1, 2, 3, 4, 5...)..

Short program description
I am analyzing color density in digital photos and calculating the time until the photos 'fail'. Color samples are divided by ink, color, shade, and point in time the reading is taken. I need to find the shades whose first 'points' are closest to 3 or so designated target densities (those we wish we had started with in a perfect world). Once I've found those, I interpolate to theorize what those plots would look like

(This is a general - and generally poor - description but these parts are all working and are not necessary to understand the problem.)

In essence, supplied with raw data in text files (Sheet = 'Original') the program copies and reorganizes them into a 4-D array.

The array is sorted and a new 'interpolation' array is created using the old data.

Finally, the interpolation array is printed (on 'Sheet2') and some formulas are inserted below each plot to determine a, b, and R^2 if the plot were fitted (using the least-squares method) to a curve of type:

y = a * exp(b * x)I am using the method used by ozgrid-member SHG in this thread: http://www.ozgrid.com/forum/showthread.php?t=57470
The only difference, as far as I can tell, is that my x-values (representing 'time') are not linear (e.g. 0, 24, 48, 96, 144, 192, 288, 384 as opposed to 1, 2, 3, 4, 5...).

Here is my code snippet for placing the formulas into cells where 'coefficient' refers to 'a' above, 'power' refers to 'b', and R^2 shows the average distance squared (least-squares method). The lines are very long, read below for disambiguation:


	VB:
	
For j = 1 To numColors 
     
    Cells(currentRow, currentColumn).Formula = "=Exp(Index(LinEst(Ln(" & Range(Cells(currentRow - (numPoints + 2),
currentColumn), Cells(currentRow - 3, currentColumn)).Address & "), Ln(" & Range(Cells(currentRow - (numPoints + 2), 1),
Cells(currentRow - 3, 1)).Address & "),,True), 1, 2 ) )" 
    currentColumn = currentColumn + 1 
     
Next 
 
currentColumn = 2 
 
 'Power
For j = 1 To numColors 
     
    Cells(currentRow + 1, currentColumn).Formula = "=LinEst(Ln(" & Range(Cells(currentRow - (numPoints + 2), currentColumn),
Cells(currentRow - 3, currentColumn)).Address & "), Ln(" & Range(Cells(currentRow - (numPoints + 2), 1), Cells(currentRow -
3, 1)).Address & "),, True)" 
    currentColumn = currentColumn + 1 
     
Next 
 
currentColumn = 2 
 
 'R^2
For j = 1 To numColors 
     
    Cells(currentRow + 2, currentColumn).Formula = "=Index(LinEst(Ln(" & Range(Cells(currentRow - (numPoints + 2),
currentColumn), Cells(currentRow - 3, currentColumn)).Address & "), Ln(" & Range(Cells(currentRow - (numPoints + 2), 1),
Cells(currentRow - 3, 1)).Address & "),, TRUE), 3, 1)" 
    currentColumn = currentColumn + 1 
     
Next 
 
currentColumn = 2 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Disambiguation:

The 'coefficients' formula that is inputted within the cell will look like this:
Ex. "=EXP(INDEX(LINEST(LN($B$4:$B$11), LN($A$4:$A$11),TRUE,FALSE), 1, 2 ) )"

The 'power' formula may look like this:
Ex. "=LINEST(LN($B$4:$B$11), LN($A$4:$A$11),, TRUE)"

The "R^2" formula may look like this:
Ex. "=INDEX(LINEST(LN($B$4:$B$11), LN($A$4:$A$11),, TRUE), 3, 1)"
Question: Why are my calculations always apparently close, but not correct?

For illustration, assume that my x-values, represented by $A$4:$A$11 are {0, 24, 48, 96, 144, 192, 288, 384} and my y-values, represented by $B$4:$B$11, are {0.5, 0.51, 0.51, 0.65, 0.65, 0.51, 0.65, 0.65}.

The example for 'coefficient' above gives a value of 0.51172862. The trendline option in excel gives a coefficient of 0.5227.

The example for 'power' above gives a value of 0.029640993. The trendline option in excel gives a power of 0.0006.

The example for 'R^2' above gives a value of 0.355785557. The trendline option in excel gives an R^2 of 0.4251.

My last post will have the full program with directions. Auto Merged Post Until 24 Hrs Passes;

The program is attached as 'Testbook'.

Description:

The sheet 'Original' has an example of the data I am using, exactly the way I get it in the text files.

'Sheet1' has the data from 'Original' cleaned up a bit.

'Sheet2' is where the output is placed. Don't delete it!

The macro is named 'Redline'. (If you can find a modified use for it, you have my full permission to make millions of dollars off it, as long as you don't turn around and sue me for infringing on "your" copyright.) :-)

Redline will open a series of message boxes.

In order, click "OK", "Yes", and "No". In general, don't mess around with the default settings unless you want to spend some serious time figuring out the program.This should spit out some poorly organized results. The code itself should be somewhat straightforward but it is long. I've done some commenting, but I wouldn't consider it properly documented yet.

Hello,

I am building an VBA macro that will allow me to send Bulk Email, using MS
Outlook, and using an word.doc as the message body. When i create a new
object of words i have no problem, but if i try to create an object of MS
Outlook i get the error 429 if you are familiar with that, "You can pull a
search on google using, "VBA error 429".

Basically it works like this, i need to be able to send out around 1,500 per
day what it does is extract the information from excel spreadsheet cell. I
click on a button in excel then it will dump every value in every cell into
an array. Then it will manipulate a word.doc document by inserting into
bookmarks values from the array. then after one row is processed it will then
send it to outlook to be processes for outgoing mail.

As i stated before the word application has no problem working, but if i try
to initiate outlook then i get the error. I never thought VBA would be so
complicated. If anyone has any suggestions or better way i could go about
this i would surely appreciate it.

~~~~~~~~~~~Below is the code~~~~~~~~~~~~~

Option Explicit

Sub BtnSendEmail_Click()
Dim name, phone, email, time, _
dated As String
Dim confirm, sent As Boolean
Dim status As Boolean
' array = {name, phone, email, date, time, confirm, sent}
Dim rowColArray() As String
Dim row As Double, col As Double

' Debug.Print DBEngine.Version

' Step 1
status = GetApptRec(rowColArray, row, col)

' ' TODO: at end of coding delete this section was used for
' ' TODO: testing purposes
' ' test values to see if it was inputted
' Dim nr, nc As Integer
' For nr = 1 To row
' For nc = 1 To col
' ' MsgBox rowColArray(nr, nc)
' Next nc
' Next nr
'
' MsgBox "There are " & row & " rows " & _
' "and " & col & " Columns", vbOKOnly, _
' "Number of Row and Columns"

' TODO: Call CreateEmailMsg (Create Email Message Module)
Call CreateEmailMsg(rowColArray)

' TODO: Call SendMsg (Send Email Message)

End Sub

Public Function GetApptRec(ByRef rowColArray() As String, _
ByRef row As Double, ByRef col As Double) As Boolean

Dim r, c As Integer
' Dim rowColArray() As String
' Dim row, col As Double
Dim strValue As String

' Determine the total number of rows and columns
col = fLastColWithData()
row = fLastRowWithData()

ReDim rowColArray(row, col)

For r = 1 To row
For c = 1 To col
' fill varaible with the values from the cells
' starting at row 2
strValue = Cells(r, c)
rowColArray(r, c) = strValue
Next c
Next r

GetApptRec = True
End Function ' GetApptRec

Public Function CreateEmailMsg _
(ByRef rowColArray() As String) As String
Dim r As Double, c As Integer, row As Double, col As Integer
Dim name As String, dated As String, timed As String, _
email As String

Dim oGlobalWordApp As Object
Dim oOutlook As Object
' Dim oOutlook As Outlook.Application
Set oGlobalWordApp = CreateObject("Word.Application")
oOutlook = CreateObject("Outlook.Application")
' oOutlook = New Outlook.Application
oGlobalWordApp.Visible = True

row = UBound(rowColArray, 1)
col = UBound(rowColArray, 2)

On Error GoTo errorHandler
' TODO: Call GetWrdDoc (Get Word Document)
Documents.Open ("C:docscopy of crm.doc")

' TODO: FrmDtTm (Format Date And Time)

' TODO: Call ManipMsg (Manipulate Message)

' array = {name, phone, email, date, time, confirm, sent}
' bookmark. exists (does it exist?):
For r = 1 To row

' make sure it is ok to send it before sending it
Dim sent, confirmed
sent = rowColArray(r, 7)
confirmed = rowColArray(r, 6)

If confirmed = 1 And sent = 0 Then
For c = 1 To col
name = rowColArray(r, 1)
dated = rowColArray(r, 4)
timed = rowColArray(r, 5)
email = rowColArray(r, 3)
If Word.ActiveDocument.Bookmarks.Exists("Name") = True Then
Word.ActiveDocument.Bookmarks("Name").Select
Word.Selection.TypeText Text:=name
End If
If Word.ActiveDocument.Bookmarks.Exists("Date1") = True Then
Word.ActiveDocument.Bookmarks("Date1").Select
Word.Selection.TypeText Text:=dated
End If
If Word.ActiveDocument.Bookmarks.Exists("Date2") = True Then
Word.ActiveDocument.Bookmarks("Date2").Select
Word.Selection.TypeText Text:=dated
End If
If Word.ActiveDocument.Bookmarks.Exists("Time1") = True Then
Word.ActiveDocument.Bookmarks("Time1").Select
Word.Selection.TypeText Text:=time
End If
If Word.ActiveDocument.Bookmarks.Exists("Time2") = True Then
Word.ActiveDocument.Bookmarks("Time2").Select
Word.Selection.TypeText Text:=time
End If

' TODO: Call SendMsg (Send Email Message)
Call SendMsg(, email)
Next c
End If
Next r

errorHandler:
MsgBox Err.Number & " " & Err.Description
oGlobalWordApp.Quit
oGlobalWordApp = Nothing

End Function ' CreateEmailMsg

Public Sub SendMsg(Optional ByVal msgBody As Object, _
Optional ByVal email As String)

' Dim bStarted As Boolean
' Dim oOutlookApp As Object
Dim oItem As Outlook.MailItem

' On Error Resume Next
On Error GoTo errorHandler

'Get Outlook if it's running
' Set oOutlookApp = GetObject(, "Outlook.Application")
' If Err <> 0 Then
'Outlook wasn't running, start it from code
' Set oOutlookApp = CreateObject("Outlook.Application")
' bStarted = True
' End If

'Create a new mailitem
Set oItem = oOutlookApp.CreateItem(olMailItem)

With oItem
'Set the recipient for the new email
.To = email
'Set the recipient for a copy
'.CC = "recipient2@mail.com"
'Set the subject
.subject = "Concerning Appointment with Dustin Swiger"
'The content of the document is used as the body for the email
.Body = ActiveDocument.Content
.Send
End With

' If bStarted Then
' 'If we started Outlook from code, then close it
' oOutlookApp.Quit
' End If

errorHandler:
MsgBox Err.Number & " " & Err.Description
'Clean up
Set oItem = Nothing
Set oOutlookApp = Nothing

End Sub ' SendMsg

Public Sub GetWrdDoc()

End Sub

Public Function FrmDtTm(ByVal time As String, _
ByVal dated As String)

End Function

Public Function ManipMsg(ByVal name As String, _
ByVal msgBody As Object)

' TODO: FindReplaceName (Find & Replace Default String for Name Field)

' TODO: FindReplaceDtTm (Find & Replace Default String for data & time)

End Function

Private Function FindReplaceName(ByVal name As String, _
ByVal msgBody As Object)

End Function

Private Function FindReplaceDtTm(ByVal dated As String, _
ByVal time As String, ByVal msbBody As Object)

End Function

Public Function fLastRowWithData()
Dim excelLastCell
Dim LastRowWithData
Dim row

Set excelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)

' Determine the last row with data in it(must also copy above para for
' this to work)
LastRowWithData = excelLastCell.row
row = excelLastCell.row

Do While Application.CountA(ActiveSheet.Rows(row)) = 0 And row <> 1
row = row - 1
Loop

LastRowWithData = row ' row number

fLastRowWithData = LastRowWithData
End Function

Public Function fLastColWithData()
Dim excelLastCell
Dim lastColWithData
Dim col

Set excelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)

' determine the last column with data in it(must also copy the top
' para for this to work)
lastColWithData = excelLastCell.Columns
col = excelLastCell.Column

Do While Application.CountA(ActiveSheet.Columns(col)) = 0 And col <> 1
col = col - 1
Loop

lastColWithData = col ' column number

fLastColWithData = lastColWithData
End Function

'TODO: I need to redo this to make this work with the sendMsg module
Public Sub chkSent(ByRef rowColArray() As String, row, col)
Dim r
Dim c As Integer

' initiate c to total amount of columns in the array
c = UBound(rowColArray, 2)

' array = {name, phone, email, date, time, confirm, sent}
For r = 1 To UBound(rowColArray, 1)
Value = rowcountarray(r, c)
If Value = 1 Then
Dim cs
For cs = 1 To c
rowcountarray(r, cs) = ""
Next cs
End If
Next r
End Sub

'TODO: I need to redo this to make this work with the sendMsg module
Public Sub chkConfirmed(ByRef rowColArray() As String, row, col)
Dim r
Dim c As Integer

' initiate c to total amount of columns in the array
c = UBound(rowColArray, 2)
c = c - 1

' array = {name, phone, email, date, time, confirm, sent}
For r = 1 To UBound(rowColArray, 1)
Value = rowcountarray(r, c)
If Value = 1 Then
Dim cs
For cs = 1 To c
rowcountarray(r, cs) = ""
Next cs
End If
Next r
End Sub

Public Sub SendOutlookMail(ByVal subject As String, ByVal Recipient As _
String, ByVal Message As String)

On Error GoTo errorHandler
Dim oLapp As Object
Dim oItem As Object

oLapp = CreateObject("Outlook.application")
oItem = oLapp.CreateItem(0)
'
With oItem
.subject = subject
.To = Recipient
.Body = Message
' .Send()
End With
'
oLapp = Nothing
oItem = Nothing
'

' reset the resend boolean
resend = False
Exit Sub

errorHandler:
oLapp = Nothing
oItem = Nothing
' reset the resend boolean
resend = False
Exit Sub
End Sub ' SendOutlookMail()

Basically
--
Digit Solver‚ĄĘ


No luck finding an answer? You could always try Google.