update same row

Gents,
I'm currently using a script that opens excel and adds a line to the first
empty cell.
How can I expand this to : if the name (strmyinput) is already on that list,
add it to the same row on a new column instead of on a new row?
here's the script I got:
Sub bootstart()
strmyinput = InputBox("recruitname?")
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("C:\Documents and Settings\Overlord\My
Documents\Bart\TEAMSPEAK.xls")
Set ws = wb.Worksheets("Recruits")
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, 1)
rn.FormulaR1C1 = "=TODAY()"
Else
' the last row (65536) isn´t empty
End If
wb.Save
wb.Close
Excel.Application.Quit
End Sub

I tried with Set rn = ws.Columns(1).Find("*" or strmyinput, , xlValues,
xlWhole, xlByRows, xlPrevious) but that didn't work.
any ideas anyone?
Thx!
--
Welcome back, Bart Cuenen. If you're not Bart Cuenen, sign out by clicking
the Sign Out button above, and then sign in again. LMAO!

Hi Bart,

> I tried with Set rn = ws.Columns(1).Find("*" or strmyinput, ,
xlValues,

for that you have to search twice, for strmyinput first and then, if
there is no match, for "*".

--
Viele GrĂĽĂźe
Michael Bauer

somehow it doesn't keep updating the cell...
idea?

"Bart Cuenen" wrote:

> Gents,
> I'm currently using a script that opens excel and adds a line to the first
> empty cell.
> How can I expand this to : if the name (strmyinput) is already on that list,
> add it to the same row on a new column instead of on a new row?
> here's the script I got:
> Sub bootstart()
> strmyinput = InputBox("recruitname?")
> 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("C:\Documents and Settings\Overlord\My
> Documents\Bart\TEAMSPEAK.xls")
> Set ws = wb.Worksheets("Recruits")
> 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, 1)
> rn.FormulaR1C1 = "=TODAY()"
> Else
> ' the last row (65536) isn´t empty
> End If
> wb.Save
> wb.Close
> Excel.Application.Quit
> End Sub
>
> I tried with Set rn = ws.Columns(1).Find("*" or strmyinput, , xlValues,
> xlWhole, xlByRows, xlPrevious) but that didn't work.
> any ideas anyone?
> Thx!
> --
> Welcome back, Bart Cuenen. If you're not Bart Cuenen, sign out by clicking
> the Sign Out button above, and then sign in again. LMAO!

Do you have some more informations? Do you get an error, where is the
line that doesn´t work?

--
Viele GrĂĽĂźe
Michael Bauer


"Bart Cuenen" <BartCuenen@discussions.microsoft.com> wrote in message
news:411B1CF1-3E72-4E2B-9092-8E795E66A3BD@microsoft.com...
> somehow it doesn't keep updating the cell...
> idea?
>
> "Bart Cuenen" wrote:
>
> > Gents,
> > I'm currently using a script that opens excel and adds a line to the
first
> > empty cell.
> > How can I expand this to : if the name (strmyinput) is already on
that list,
> > add it to the same row on a new column instead of on a new row?
> > here's the script I got:
> > Sub bootstart()
> > strmyinput = InputBox("recruitname?")
> > 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("C:\Documents and Settings\Overlord\My
> > Documents\Bart\TEAMSPEAK.xls")
> > Set ws = wb.Worksheets("Recruits")
> > 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, 1)
> > rn.FormulaR1C1 = "=TODAY()"
> > Else
> > ' the last row (65536) isn´t empty
> > End If
> > wb.Save
> > wb.Close
> > Excel.Application.Quit
> > End Sub
> >
> > I tried with Set rn = ws.Columns(1).Find("*" or strmyinput, ,
xlValues,
> > xlWhole, xlByRows, xlPrevious) but that didn't work.
> > any ideas anyone?
> > Thx!
> > --
> > Welcome back, Bart Cuenen. If you're not Bart Cuenen, sign out by
clicking
> > the Sign Out button above, and then sign in again. LMAO!

the problem is somewhere with the offset it seems.
For example:
I have in cell A2: benjamin
I have in cell B2: 12/11/2004

When I run the script and the inputname is benjamin then it should add the
current date to C2 and it's not doing that

The farest I'm getting it is that it will put the date in the first empty
cell 1 row higher then strmyinput.
In my case, I enter benjamin and the date appears in cell C1, not even in B1
if that cell is empty.
Set rn = ws.Columns(1).Find(strmyinput, , xlValues, xlWhole, xlByRows,
xlPrevious)
If Not rn Is Nothing Then
Set rn = rn.Rows.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious)
Set rn = rn.Offset(, xlNext)

rn.FormulaR1C1 = "=TODAY()"
something is really wrong here...
Thank you for your help Michael!

"Michael Bauer" wrote:

> Do you have some more informations? Do you get an error, where is the
> line that doesn´t work?
>
> --
> Viele GrĂĽĂźe
> Michael Bauer
>
>
> "Bart Cuenen" <BartCuenen@discussions.microsoft.com> wrote in message
> news:411B1CF1-3E72-4E2B-9092-8E795E66A3BD@microsoft.com...
> > somehow it doesn't keep updating the cell...
> > idea?
> >
> > "Bart Cuenen" wrote:
> >
> > > Gents,
> > > I'm currently using a script that opens excel and adds a line to the
> first
> > > empty cell.
> > > How can I expand this to : if the name (strmyinput) is already on
> that list,
> > > add it to the same row on a new column instead of on a new row?
> > > here's the script I got:
> > > Sub bootstart()
> > > strmyinput = InputBox("recruitname?")
> > > 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("C:\Documents and Settings\Overlord\My
> > > Documents\Bart\TEAMSPEAK.xls")
> > > Set ws = wb.Worksheets("Recruits")
> > > 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, 1)
> > > rn.FormulaR1C1 = "=TODAY()"
> > > Else
> > > ' the last row (65536) isn´t empty
> > > End If
> > > wb.Save
> > > wb.Close
> > > Excel.Application.Quit
> > > End Sub
> > >
> > > I tried with Set rn = ws.Columns(1).Find("*" or strmyinput, ,
> xlValues,
> > > xlWhole, xlByRows, xlPrevious) but that didn't work.
> > > any ideas anyone?
> > > Thx!
> > > --
> > > Welcome back, Bart Cuenen. If you're not Bart Cuenen, sign out by
> clicking
> > > the Sign Out button above, and then sign in again. LMAO!
>
>

Please execute your code step by step (F8) and check, if rn somewhere is
nothing or, if not, to which address it refers.


--
Viele GrĂĽĂźe
Michael Bauer


"Bart Cuenen" <BartCuenen@discussions.microsoft.com> wrote in message
news:AC7A65C2-A9A0-4016-8B64-814BDF165D85@microsoft.com...
> the problem is somewhere with the offset it seems.
> For example:
> I have in cell A2: benjamin
> I have in cell B2: 12/11/2004
>
> When I run the script and the inputname is benjamin then it should add
the
> current date to C2 and it's not doing that
>
> The farest I'm getting it is that it will put the date in the first
empty
> cell 1 row higher then strmyinput.
> In my case, I enter benjamin and the date appears in cell C1, not even
in B1
> if that cell is empty.
> Set rn = ws.Columns(1).Find(strmyinput, , xlValues, xlWhole, xlByRows,
> xlPrevious)
> If Not rn Is Nothing Then
> Set rn = rn.Rows.Find("*", , xlValues, xlWhole, xlByRows,
xlPrevious)
> Set rn = rn.Offset(, xlNext)
>
> rn.FormulaR1C1 = "=TODAY()"
> something is really wrong here...
> Thank you for your help Michael!
>
> "Michael Bauer" wrote:
>
> > Do you have some more informations? Do you get an error, where is
the
> > line that doesn´t work?
> >
> > --
> > Viele GrĂĽĂźe
> > Michael Bauer
> >
> >
> > "Bart Cuenen" <BartCuenen@discussions.microsoft.com> wrote in
message
> > news:411B1CF1-3E72-4E2B-9092-8E795E66A3BD@microsoft.com...
> > > somehow it doesn't keep updating the cell...
> > > idea?
> > >
> > > "Bart Cuenen" wrote:
> > >
> > > > Gents,
> > > > I'm currently using a script that opens excel and adds a line to
the
> > first
> > > > empty cell.
> > > > How can I expand this to : if the name (strmyinput) is already
on
> > that list,
> > > > add it to the same row on a new column instead of on a new row?
> > > > here's the script I got:
> > > > Sub bootstart()
> > > > strmyinput = InputBox("recruitname?")
> > > > 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("C:\Documents and Settings\Overlord\My
> > > > Documents\Bart\TEAMSPEAK.xls")
> > > > Set ws = wb.Worksheets("Recruits")
> > > > 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, 1)
> > > > rn.FormulaR1C1 = "=TODAY()"
> > > > Else
> > > > ' the last row (65536) isn´t empty
> > > > End If
> > > > wb.Save
> > > > wb.Close
> > > > Excel.Application.Quit
> > > > End Sub
> > > >
> > > > I tried with Set rn = ws.Columns(1).Find("*" or strmyinput, ,
> > xlValues,
> > > > xlWhole, xlByRows, xlPrevious) but that didn't work.
> > > > any ideas anyone?
> > > > Thx!
> > > > --
> > > > Welcome back, Bart Cuenen. If you're not Bart Cuenen, sign out
by
> > clicking
> > > > the Sign Out button above, and then sign in again. LMAO!
> >
> >

Here is the full code.
I don't find any problem in it. Once again, I don't know anything about vb
or vba..
All the scripts I use are created for 90% by somebody else and I adjusted
them to my needs with the help of you and some orthers.
Danke Michael!

"Michael Bauer" wrote:

> Please execute your code step by step (F8) and check, if rn somewhere is
> nothing or, if not, to which address it refers.
>
>
> --
> Viele GrĂĽĂźe
> Michael Bauer
>
>
> "Bart Cuenen" <BartCuenen@discussions.microsoft.com> wrote in message
> news:AC7A65C2-A9A0-4016-8B64-814BDF165D85@microsoft.com...
> > the problem is somewhere with the offset it seems.
> > For example:
> > I have in cell A2: benjamin
> > I have in cell B2: 12/11/2004
> >
> > When I run the script and the inputname is benjamin then it should add
> the
> > current date to C2 and it's not doing that
> >
> > The farest I'm getting it is that it will put the date in the first
> empty
> > cell 1 row higher then strmyinput.
> > In my case, I enter benjamin and the date appears in cell C1, not even
> in B1
> > if that cell is empty.
> > Set rn = ws.Columns(1).Find(strmyinput, , xlValues, xlWhole, xlByRows,
> > xlPrevious)
> > If Not rn Is Nothing Then
> > Set rn = rn.Rows.Find("*", , xlValues, xlWhole, xlByRows,
> xlPrevious)
> > Set rn = rn.Offset(, xlNext)
> >
> > rn.FormulaR1C1 = "=TODAY()"
> > something is really wrong here...
> > Thank you for your help Michael!
> >
> > "Michael Bauer" wrote:
> >
> > > Do you have some more informations? Do you get an error, where is
> the
> > > line that doesn´t work?
> > >
> > > --
> > > Viele GrĂĽĂźe
> > > Michael Bauer
> > >
> > >
> > > "Bart Cuenen" <BartCuenen@discussions.microsoft.com> wrote in
> message
> > > news:411B1CF1-3E72-4E2B-9092-8E795E66A3BD@microsoft.com...
> > > > somehow it doesn't keep updating the cell...
> > > > idea?
> > > >
> > > > "Bart Cuenen" wrote:
> > > >
> > > > > Gents,
> > > > > I'm currently using a script that opens excel and adds a line to
> the
> > > first
> > > > > empty cell.
> > > > > How can I expand this to : if the name (strmyinput) is already
> on
> > > that list,
> > > > > add it to the same row on a new column instead of on a new row?
> > > > > here's the script I got:
> > > > > Sub bootstart()
> > > > > strmyinput = InputBox("recruitname?")
> > > > > 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("C:\Documents and Settings\Overlord\My
> > > > > Documents\Bart\TEAMSPEAK.xls")
> > > > > Set ws = wb.Worksheets("Recruits")
> > > > > 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, 1)
> > > > > rn.FormulaR1C1 = "=TODAY()"
> > > > > Else
> > > > > ' the last row (65536) isn´t empty
> > > > > End If
> > > > > wb.Save
> > > > > wb.Close
> > > > > Excel.Application.Quit
> > > > > End Sub
> > > > >
> > > > > I tried with Set rn = ws.Columns(1).Find("*" or strmyinput, ,
> > > xlValues,
> > > > > xlWhole, xlByRows, xlPrevious) but that didn't work.
> > > > > any ideas anyone?
> > > > > Thx!
> > > > > --
> > > > > Welcome back, Bart Cuenen. If you're not Bart Cuenen, sign out
> by
> > > clicking
> > > > > the Sign Out button above, and then sign in again. LMAO!
> > >
> > >
>
>

Hi Bart,

> Set rn = rn.Rows.Find("*", , xlValues, xlWhole, xlByRows,
xlPrevious)

this second search needs to search in ws again:

Set rn = ws.Rows(rn.Row).Find("*", , xlValues, xlWhole, xlByRows,
xlPrevious)

Some more tipps:

a) > Set rn = rn.Offset(, xlNext)

Just by chance the value of xlNext is 1. The programmer could change
this value and your code wouldn´t work any more. You should use your own
constant or even 1 instead.

b) > rn.FormulaR1C1 = "=TODAY()"

The next day you open this workbook, the value will be the actually date
again, not the former, originally date. That can be convenient but you
should know that.

If the date must not change then use rn.value = date instead.

--
Viele GrĂĽĂźe
Michael Bauer


"Bart Cuenen" <BartCuenen@discussions.microsoft.com> wrote in message
news:841517AD-C0B0-4382-803A-EDFB7CB515CD@microsoft.com...
> Here is the full code.
> I don't find any problem in it. Once again, I don't know anything
about vb
> or vba..
> All the scripts I use are created for 90% by somebody else and I
adjusted
> them to my needs with the help of you and some orthers.
> Danke Michael!
>
> "Michael Bauer" wrote:
>
> > Please execute your code step by step (F8) and check, if rn
somewhere is
> > nothing or, if not, to which address it refers.
> >
> >
> > --

Thank you Michael,

I knew partly where the problem was but couldn't find it.
Also about the date, thank you very much for that, it was exactly what I
needed but couldn't find it with the normal formulas.
Thank YOU Michael!

"Michael Bauer" wrote:

> Hi Bart,
>
> > Set rn = rn.Rows.Find("*", , xlValues, xlWhole, xlByRows,
> xlPrevious)
>
> this second search needs to search in ws again:
>
> Set rn = ws.Rows(rn.Row).Find("*", , xlValues, xlWhole, xlByRows,
> xlPrevious)
>
> Some more tipps:
>
> a) > Set rn = rn.Offset(, xlNext)
>
> Just by chance the value of xlNext is 1. The programmer could change
> this value and your code wouldn´t work any more. You should use your own
> constant or even 1 instead.
>
> b) > rn.FormulaR1C1 = "=TODAY()"
>
> The next day you open this workbook, the value will be the actually date
> again, not the former, originally date. That can be convenient but you
> should know that.
>
> If the date must not change then use rn.value = date instead.
>
> --
> Viele GrĂĽĂźe
> Michael Bauer
>
>
> "Bart Cuenen" <BartCuenen@discussions.microsoft.com> wrote in message
> news:841517AD-C0B0-4382-803A-EDFB7CB515CD@microsoft.com...
> > Here is the full code.
> > I don't find any problem in it. Once again, I don't know anything
> about vb
> > or vba..
> > All the scripts I use are created for 90% by somebody else and I
> adjusted
> > them to my needs with the help of you and some orthers.
> > Danke Michael!
> >
> > "Michael Bauer" wrote:
> >
> > > Please execute your code step by step (F8) and check, if rn
> somewhere is
> > > nothing or, if not, to which address it refers.
> > >
> > >
> > > --
>
>