how to add a time stamp or current date in a column 2

[arlinda]

Hi,

I have an excel spreadsheet with a few columns. I have added a column called modification date and I would like to compare it with a column card num and if there is data in card num then I want the modification date column to add the current date.

How do I do this. Please advice?

Hi,

Thank you for your comments. I did make the changes to the script but it doesn't do anything to my spreadsheet. Please advice.


Sub mymacro1()
'
' mymacro1 Macro
' Macro recorded
'

'
const xlDown=&HFFFFEFE7

Set objXL = CreateObject("Excel.Application")
objXL.visible=true
Set objWB = objXL.WorkBooks.Open("C:\data\abc.csv")
Set objWS = objXL.ActiveWorkBook.WorkSheets("abc")

With objWS

.Rows("1:1").Insert
.Application.Selection.End(xlDown).Select()
.Range("A1").Value = "First Name"
.Range("B1").Value = "Middel int"
.Range("C1").value = "Last name"
.Range("D1").value = "Start Date"
.Range("E1").value = "Card Num"
.Range("F1").value = "Modification Date"

end with
int iRow = 2

objXL.DisplayAlerts=false
objWB.save
objWB.Close
objXL.Quit

End Sub

 

[Skie]

If objWS.Cell(iRow, "E") <> "" Then objWS.Cell(iRow, "F") = Now()

 

[arlinda]

Hi,

Sorry to bother you again but when I put the If statement into my script, and compiled the code I got a "Unknown runtime error on the line that has the If statement any ideas? I initialy used the objWS.cell statement but I got an error saying that Object doesn't support this property or method. I tried using .Activecells and I got an error saying that missing an If statement. Below is the script

Sub mymacro1()
'
' mymacro1 Macro
' Macro
'

'
const xlDown=&HFFFFEFE7

Set objXL = CreateObject("Excel.Application")
objXL.visible=true
Set objWB = objXL.WorkBooks.Open("C:\data\abc.csv")
Set objWS = objXL.ActiveWorkBook.WorkSheets("abc")

With objWS

.Rows("1:1").Insert
.Application.Selection.End(xlDown).Select()
.Range("A1").Value = "First Name"
.Range("B1").Value = "Middel int"
.Range("C1").value = "Last name"
.Range("D1").value = "Start Date"
.Range("E1").value = "Card Num"
.Range("F1").value = "ModificationDate"

end with
int iRow = 2
If objWS.Cells(iRow, "F") <> "" Then objWS.Cells(iRow, "M") = Now()


objXL.DisplayAlerts=false
objWB.save
objWB.Close
objXL.Quit

End Sub

 

[arlinda]

Please look at this one on the one above

Hi,

Sorry to bother you again but when I put the If statement into my script, and compiled the code I got a "Unknown runtime error on the line that has the If statement any ideas? I initialy used the objWS.cell statement but I got an error saying that Object doesn't support this property or method. I tried using .Activecells and I got an error saying that missing an If statement. Below is the script
The spreadsheet has Numbers on the Card Num column. Modification Date column is blank


Sub mymacro1()
'
' mymacro1 Macro
' Macro
'

'
const xlDown=&HFFFFEFE7

Set objXL = CreateObject("Excel.Application")
objXL.visible=true
Set objWB = objXL.WorkBooks.Open("C:\data\abc.csv")
Set objWS = objXL.ActiveWorkBook.WorkSheets("abc")

With objWS

.Rows("1:1").Insert
.Application.Selection.End(xlDown).Select()
.Range("A1").Value = "First Name"
.Range("B1").Value = "Middel int"
.Range("C1").value = "Last name"
.Range("D1").value = "Start Date"
.Range("E1").value = "Card Num"
.Range("F1").value = "ModificationDate"

end with
int iRow = 2
If objWS.Cells(iRow, "E") <> "" Then objWS.Cells(iRow, "F") = Now()


objXL.DisplayAlerts=false
objWB.save
objWB.Close
objXL.Quit

End Sub

 

[tsuji]

>int iRow = 2
[tt]iRow = 2[/tt]

Variables in vbs would be declared as variant. Integer subtype would be eventually taken care internally by itself. If you want to declare, it is this.

[tt]dim iRow
iRow=2
wscript.echo typename(iRow) 'for inspection only, comment it out later
[/tt]

 

[arlinda]

Hi,

How do I put this in a loop?


dim iRow
iRow=2
Do Until objWS.Cells(intRow).Value = ""

If objWS.Cells(iRow, "F") <> "" Then objWS.Cells(iRow, "M") = Now()
objWS.MoveNext
intRow = intRow + 1

Loop

 

[tsuji]

>Do Until objWS.Cells(intRow).Value = ""

What do you mean by objWS.Cells(intRow)? It is iRow, that's obvious. But cells() takes two parameter? So I guess it is this.
[tt]
dim iRow
iRow=2
Do Until objWS.Cells(iRow,"F").Value = ""
objWS.Cells(iRow, "M") = Now()
[red]'[/red]objWS.MoveNext
intRow = intRow + 1
Loop
[/tt]

 

[PHV]

intRow=2
Do Until objWS.Cells(intRow, "F").Value = ""
objWS.Cells(intRow, "M") = Now()
intRow = intRow + 1
Loop

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tsuji]

Amendment
Incomplete correction of intRow!
[tt]
dim iRow
iRow=2
Do Until objWS.Cells(iRow,"F").Value = ""
objWS.Cells(iRow, "M") = Now()
'objWS.MoveNext
[red]iRow = iRow + 1[/red]
Loop
[/tt]

 

[arlinda]

Thank youuuuuuuuu very much.

 

[arlinda]

Thank youuuuuuuuu very much. It works flawlessly