How do I change the direction of my script searching an excel doc? 1

[RonQA]

Here is the script I am using to add an entry into an excel sheet automactically. I need it to find the last cell with data and add the entry after it. It works, but it searches top to bottom. I need it to go Bottom to Top.

How can I change the direction of my search for the last empty cell from down to up?

'Update Excel Spredsheat
Function UpdateExcel(ExcelFile)
Const xlCellTypeLastCell = 11

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(ExcelFile)
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Activate

Set objRange = objWorksheet.UsedRange
intNewRow = objExcel.ActiveCell.Row + 1

'Update Cells

strNewCell = "B" & intNewRow
objExcel.Range(strNewCell).Activate
objExcel.Range(strNewCell).Value = Date()

strNewCell = "E" & intNewRow
objExcel.Range(strNewCell).Activate
objExcel.Range(strNewCell).Value = "QA"

strNewCell = "F" & intNewRow
objExcel.Range(strNewCell).Activate
objExcel.Range(strNewCell).Value = "QA Accepted"

strNewCell = "J" & intNewRow
objExcel.Range(strNewCell).Activate
objExcel.Range(strNewCell).Value = Date()

strNewCell = "K" & intNewRow
objExcel.Range(strNewCell).Activate
objExcel.Range(strNewCell).Value = "Admin"

strNewCell = "L" & intNewRow
objExcel.Range(strNewCell).Activate
objExcel.Range(strNewCell).Value = "Shipping"

objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
End Function

Thanks,

 

[tsuji]

If you copy the script from the link I posted in your previous unanswered thread, you have to copy it properly: not missing the vital line (which makes your script incorrect). With that, why do you need to loop upward, you don't need any loop at all. Besides, your script doesn't even show any functionality of "search", not to say "downward" as if it worked in that sense.

 

[RonQA]

This line fails when executed. This script is part of a larger script.
Error - "Activate method of Range class failed."
objRange.SpecialCells(xlCellTypeLastCell).Activate

It is now a function. The total script searches for a specific excel file, opens and updates with an entry. The excel file has the first few rows which should always contain data, but if someone failed to put the data in, then the script places my entry in those rows. Otherwise it works fine. If the script searched from the bottom up then this could never occur. Just trying to idiot proof the script.

Thanks,

 

[tsuji]

There are some subtle problems using specialcells(xlcelltypelastcell). If you have some signature column (say column B) that you want to go to the (absolute) last row, you can do this.
[tt]
'replacing these two lines
'Set objRange = objWorksheet.UsedRange
'intNewRow = objExcel.ActiveCell.Row + 1
'by these (for signature column b)
const xlUp=&hffffefbe
r=objWorksheet.range("b65536").end(xlup).row
if r=1 and objWorksheet.range("b"&r).value="" then
intNewRow=r
else
intNewRow=r+1
end if
[/tt]
This is effectively search upward on specific signature column (B here).