update same row

[Guest]

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!

 

[Michael Bauer]

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 "*".

 

[Guest]

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

 

[Michael Bauer]

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

 

[Guest]

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]

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

 

[Guest]

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]

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.

 

[Guest]

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!