update first empty cell in excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hello,
I'm using a outlook macro to autosend a mail to somebody and meanwhile open
a excel file.
the sending of the file goes perfect as goes opening the excel file.
now I have a inputbox that replaces a word in that mail but taht data should
also be entered in excel.

How can I tell it to add strMyInput to the first empty cell in column A?
 
Hi Bart,

the sample finds the first cell in column 1 from buttom up that is *not*
empty. The next cell than is empty of course.

dim rn as excel.range

set rn=Worksheets.Columns(1).Find("*", , xlValues, xlWhole, xlByRows,
xlPrevious)
if not rn is nothing then
set rn=rn.offset(1,0)
else
' the last row (65536) isn´t empty
endif
 
Michael,

Thank you for the fast reply.

I'm getting a error in line "Dim rn As excel.Range"
It says it's not defined.

This is what I have so far:
strMyInput = InputBox("recruitname?")

Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
Set oWbks = oExcel.Workbooks
oWbks.Open "C:\Documents and Settings\Evil_elf.SS212DGA6\My
Documents\TAW\TEAMSPEAK.xls"
dim rn as excel.range

set rn=Worksheets.Columns(1).Find("*", , xlValues, xlWhole, xlByRows,
xlPrevious)
if not rn is nothing then
set rn=rn.offset(1,0)
else
' the last row (65536) isn´t empty
endif

you are going to have to help me here...

how can I get the words that I fill in the inputbox into that first empty
cell in column1?
Thx a lot!

Michael Bauer said:
Hi Bart,

the sample finds the first cell in column 1 from buttom up that is *not*
empty. The next cell than is empty of course.

dim rn as excel.range

set rn=Worksheets.Columns(1).Find("*", , xlValues, xlWhole, xlByRows,
xlPrevious)
if not rn is nothing then
set rn=rn.offset(1,0)
else
' the last row (65536) isn´t empty
endif
Click to expand...
 
Hi Bart,
I'm getting a error in line "Dim rn As excel.Range"
It says it's not defined.
Click to expand...

please set a reference on Excel (extras/references) or declare rn as
object.
how can I get the words that I fill in the inputbox into that first empty
cell in column1?
Click to expand...

if not rn is nothing then
set rn=rn.offset(1,0)
rn.value=strMyInput
endif
 
and how do u set rn as a object?

Told ya... I'm worse then a noob...

thx for the help Michael!
 
Hi Bart,
and how do u set rn as a object?
Click to expand...

just in the way I wrote it: dim rn as object.

Anyway, you should use the early binding, that is set a reference on
Excel (extras/references) to your project. This way is more performant
and you will get the benefits of Intellisense while programming.
 
erm....

it still gives me a error.... 424 needs object

I have set the refernces to microsoft excel 11.0 object library

I tried to replace dim rn as excel.range with dim rn as object but then it
gives me also the same error

error happens in "Set rn = Worksheet.Columns(1).Find("*", , xlValues,
xlWhole, xlByRows, xlPrevious)"
 
error happens in "Set rn = Worksheet.Columns(1).Find("*", , xlValues,
xlWhole, xlByRows, xlPrevious)"
Click to expand...

Ah, yes, of course. Sorry, my fault, I should have explained more
exactly.

First you need a reference to the worksheet you want to search in. For
that...
oWbks.Open "C:\Documents and Settings\Evil_elf.SS212DGA6\My
Documents\TAW\TEAMSPEAK.xls"
Click to expand...

you have to store the reference to the opened Workbook to get than one
to the Worksheet. E.g.:

dim ws as excel.worksheet
dim wb as excel.workbook

set wb=oWbks.Open (...)
set ws=wb.worksheets("YourSheetName")
set rn=ws.columns.find(...)
 
Michael,

THANK YOU SO MUCH!

I got it to work thankx to your great help!

full code is below if anyone ever wants to use it;
Sub testing()
strMyInput = InputBox("Membername?")
strMyinput2 = InputBox("password?")
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
Set oWbks = oExcel.Workbooks
Dim ws As Excel.Worksheet
Dim wb As Excel.Workbook
Dim rn As Excel.Range
Set wb = oWbks.Open("path where you saved the file including the name")
Set ws = wb.Worksheets("name of the worksheet u want to adjust")
Set rn = ws.Columns(1).Find("*", , xlValues, xlWhole, xlByRows, xlPrevious)
If Not rn Is Nothing Then
Set rn = rn.Offset(1, 0)
rn.Value = strMyInput
Set rn = rn.Offset(0, 2)
rn.Value = strMyinput2
Else
' the last row (65536) isn´t empty
End If

Michael, thank you for your outstanding help!
 
Back
Top