Help finding Row ID of Row where value in cell matches a value

Help finding Row ID of Row where value in cell matches a value

Post by jason_scha » Sun, 04 Jul 2004 05:04:12


Every month this my script will run and add a value to the next blank
row.

I have a worksheet like this

A B
---------- ---------
1 May 2004 1,234,543
2 June 2004 1,244,932
3 July 2004 1,251,355
4 Aug 2004
5 Sept 2004

If I run the script in August I expect my code to know (one way or
another) that it needs to put that month's value in B4. The next month
it should know to put the value in B5, etc

I have seen some people use some function on the B column to find the
nect available cell instead of searching A and finding a date match
and getting the row ID.

Here is my script by the way in case anyone is interested. I am sure
you could point out more elegant ways I could be doing all this.

thanks
Jason Schaitel
------------


'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()

DIM objxls
DIM objSheet
DIM Col1, Col2, Col3, Col4
DIM RowNum

'retrieve instance of excel
Set objXls = CreateObject("Excel.Application")

'open excel workbook file
objXls.Workbooks.Open DTSGlobalVariables("gv_ExcelSpreadsheet").Value

'Get reference
SET objSheet = objXls.Worksheets("Sheet1")

'Hard Code until we figure out how to calculate.
RowNum = 4

Col1 = "B" & RowNum

'Set the data for this month
objSheet.Range(Col1).Value = DTSGlobalVariables("Col1").Value

'release sheet
Set objSheet = Nothing

'save workbook
objXls.ActiveWorkbook.Save

'quit excel - important
objXls.Quit

'release
Set objXls = Nothing

Main = DTSTaskExecResult_Success
End Function
 
 
 

Help finding Row ID of Row where value in cell matches a value

Post by Trevor Shu » Sun, 04 Jul 2004 06:08:26

Jason

try something like:

RowNum = Range("B65536").End(xlUp).Offset(1,0).Row

Regards

Trevor

 
 
 

Help finding Row ID of Row where value in cell matches a value

Post by jason_scha » Fri, 09 Jul 2004 00:43:13

You recommended I try this:

RowNum = Range("B65536").End(xlUp).Offset(1,0).Row

I am got an error and I have changed the code to this:

RowNum = oSheet.Range("B65536").End(xlUp).Offset(1,0).Row

I still get an error and am wondering if perhaps xlUp is a constant
that I need to declare.

I am working in VBScript

Thanks for your help