Assigning a variable value as an Excel cell's address

[Hema Suresh]

Hi all

I am trying a project in vb6
I have 10 command buttons and 10 text boxes with some data holded
i transferred all the data as a complete record to an Excel file via vb6 code

tell you bit clearly with 4 controls i used in my work
my 1st command button is
Caption : BooksNames
Name : cmdTBooksNames

my 2nd command button is
Caption : No of books
Name : cmdTNoOfBooks

my 1st text box is
Name : txtBooksNames
Text : ""

my 2nd text box is
Name : txtNoOfBooks

when i execute my program, the user will give 2 inputs

i created the code in vb6 to pass those values to excel sheet
Also i have coded the excel sheet via vb6 to locate the received values in specified cells like the code follows

**************************************
oXLSheet.Range("A1") = TBksNames

oXLSheet.Range("B1") = BksNames

oXLSheet.Range("A2") = TNoOfBks

oXLSheet.Range("B2") = NoOfBks
************************************************** **
For example,Now i have the values from the range A1 to B2
Everything going good

But when i execute my program once again, the vaues which i am giving for the second time replacing the existing data

So,i googled and i found the following snippet to find the first unused row in the excel sheet so that i can find the activated row to transfer my second record

************************************************** **
Set oXLRange = oXLSheet.UsedRange

oXLRange.SpecialCells(xlCellTypeLastCell).Activate
newStartingRow = oXLApp.ActiveCell.Row + 1
newStartingCell = "A" & newStartingRow

oXLApp.Range(newStartingCell).Activate
************************************************** ***

But the problem here is , I have to code again by changing the cell's address,like
************************************************** *
oXLSheet.Range("A3") = TBksNames

oXLSheet.Range("B3") = BksNames

oXLSheet.Range("A4") = TNoOfBks

oXLSheet.Range("B4") = NoOfBks

************************************************** *
It becomes more complicated if i want to add 10 records per day

I feel frustrated to code again
is there any possibities to duplicate the activated cell address as "A1:B2",but saving the record next to the existing record automatically

please i neeed some idea

help me out

thank you

[Guido Geurs]

Is this the same problem as in your call:
"adding a new record to an existing record in a single Excel sheet
" ?
If so, no need to use the cell address.
PS:
If you want to set the cell address, use a var like:

dim CELLADDRESS as string
CELLADDRESS = "A1"
Range(CELLADDRESS) = textbox.text

[Stewart Ross]

You can use the Cells method of the Excel range to refer to a specific cell using numeric variables to refer to the row and column concerned. I use your worksheet variable oXLSheet in the examples below, but any worksheet object variable (or the .Activesheet method of the Excel application object) could be used instead.

oXLSheet.Cells(1,1) is cell A1, .Cells(1, 2) is A2, and so on.

If you are processing multiple rows you can use the cells method to make it a lot easier to refer to specific elements without having to use the A, B C column identifiers.

As a simple example, the following loop will set the values of cells A1 to G3 to 0. Column A is the first column, column G is the seventh, so we need to loop through rows 1 to 3 and columns 1 to 7 to set the individual cells to 0:

Const cRows = 7
Const cCols = 3
Dim intRow as Integer
Dim intCol as Integer
For intRow = 1 to cRows
  For intCol = 1 to cCols
    oXLSheet.Cells(intRow, intCol) = 0
  Next
Next

No need to use the Range("A1") notation at all if you use the Cells method instead.

A range spanning more than one cell can also be defined specifically using the Cells method, but it needs the With notation to make it easier to read. The exemplar below sets a range variable to the span of cells between A1 and G3, again just as an example.

Dim oRange as Range
With oXLSheet
  Set oRange = .Range(.Cells(1, 1), .Cells(3, 7))
End With

-Stewart